Invalid query: You have an error in your SQL syntax;

Error .. Error .. Error .. who can help me find the error???

Page 1 of 1

11 Replies - 5630 Views - Last Post: 05 June 2008 - 01:32 PM Rate Topic: -----

#1 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 11:30 AM

I'm getting this error:

Quote

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users' ('user_name','password','email','faction','confirmed','admin','beta_test' at line 1



I am not sure why this is erroring out after staring at it and trying to make adjustments for awhile to it.

I'm using:

PHP 5
MySQL

Here is the page code [long]:


<?php

include("smarty_include.php");
include ('db_var.php');

if ($_POST) {
$user=$_POST['user_name'];
$password=$_POST['password'];
$email=$_POST['email'];
$faction=$_POST['faction'];
$confirmed=0;
$admin=0;
$beta_tester=0;
$wins=0;
$losses=0;
$points=0;
$rank = "Ensign";
$confirm=$_POST['confirmpw'];
$ship_name=$_POST['vessel_name'];
$class="Cadet Cruiser";
$country="Not Stated";
$points=0;
$wins=0;
$losses=0;
$point_value=50;
$pos_x=rand(0,1000);
$pos_y=rand(0,1000);
$shield=2;
$max_shield=2;
$heavy_weapon=2;
$max_heavy_weapon=2;
$main_phaser=2;
$max_main_phaser=2;
$secondary_phaser=2;
$max_secondary_phaser=2;
$defensive_phaser=2;
$max_defensive_phaser=2;
$hull=2;
$max_hull=2;
$lab=2;
$max_lab=2;
$trans=2;
$max_trans=2;
$bridge=2;
$max_bridge=2;
$sl_engine=2;
$max_sli_engine=2;
$aux_power=2;
$max_aux_power=2;
$battery=2;
$max_battery=2;
$small_craft=2;
$max_small_craft=2;
$extra_damage=2;
$max_extra_damage=2;
$w_engine=2;
$max_w_engine=2;




 if ($password != $confirm) 
  {  
	 print("<span style='color:red'>ERROR: Passwords do not match! </span>");
  }

   $mysqli = new mysqli($host, $dbuser, $pw, $db);
   
	if ( $mysqli->errno)
   {
	  printf("Unable to connect to the database: <br />  %s", $mysqli->error);
	  exit();
	}
 
$query="SELECT *  FROM `users` WHERE `user_name` = '$user' ";

$result=$mysqli->query($query);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}

if ($result->num_rows > 0)
{
 	printf("Sorry but character name %s is already taken.", $user);
 	exit();
 	}
 
;

$query=" SELECT * FROM ships WHERE `ship_name` = '$vessel_name'";

$result=$mysqli->query($query, MYSQLI_STORE_RESULT);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}

if ($result->num_rows > 0)
{
 	printf("Sorry but vessel name %s is already taken.", $vessel_name);
 	exit();
 	}
 

$query=" SELECT * FROM users WHERE `email` = '$email'";

$result=$mysqli->query($query, MYSQLI_STORE_RESULT);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}

if ($result->num_rows > 0)
{
 	printf("Sorry but a player is already using %s as an email address.", $email);
 	exit();
 	}
	

$query="INSERT INTO 'users' ('user_name','password','email','faction','confirmed','admin','beta_tester','wins','losses','points','rank') VALUES ('$user',(md5('$password')),'$email','$faction','$confirmed','$admin','$beta_tester','$wins','$losses','$points','$rank',);";


$result=$mysqli->query($query);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}

$query = "UPDATE users SET date_joined = NOW() WHERE 'user_name' = '$user'";

$result=$mysqli->query($query);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}

$query = "UPDATE users SET date_lastlogin = NOW() WHERE 'user_name' = '$user'";

$result=$mysqli->query($query);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}


$query="INSERT INTO ships ( 'user_name', 'ship_type','ship_name','faction','pos_x','pos_y','class','points','shield','heavy_weapon','main_phaser','secondary_phaser','defensive_phaser','hull','lab','trans','bridge','sl_engine','aux_power','battery','small_craft','extra_damage','w_engine','crew','marines','sensors','max_shield','max_heavy_weapon','max_main_phaser','max_secondary_phaser','max_defensive_phaser','max_hull','max_lab','max_trans','max_bridge','max_sl_engine','max_aux_power','max_battery','max_small_craft','max_extra_damage','max_w_engine','max_crew','max_marines','max_sensors') VALUES ('$user_name','$class','$ship_name','$faction','$pos_x','$pos_y','$class','$points','$shield','$heavy_weapon','$main_phaser','$secondary_phaser','$defensive_phaser','$hull','$lab','$trans','$bridge','$sl_engine','$aux_power','$battery','$small_craft','$extra_damage','$w_engine','$crew','$marines','$sensors','$max_shield','$max_heavy_weapon','$max_main_phaser','$max_secondary_phaser','$max_defensive_phaser','$max_hull','$max_lab','$max_trans','$max_bridge','$max_sl_engine','$max_aux_power','$max_battery','$max_small_craft','$max_extra_damage','$max_w_engine','$max_crew','$max_marines','$max_sensors',);";

$result=$mysqli->query($query);

if (!$result) {
  printf("Invalid query: %s", $mysqli->error);
exit();
}






$to = $email;
$subject = 'Sector Battles e-mail confirmation';
				$message = "
<p>Hey! Thanks for signing up for Sector Battles. Click below to confirm your e-mail address.</p>
<p><a href='http://sectorbattles.com/confirm.php?email=$email'>By Clicking Here</a></p>";
				$headers = 'From: fleetregistration@sectorbattles.com' . "\r\n" .
							'Content-type: text/html; charset=iso-8859-1' . "\r\n";
				mail($to,$subject,$message,$headers);






}

$smarty->display('register.tpl');
?>





The includes at the top are just the smarty template paths and my db log in information


Anyone see something here?

Is This A Good Question/Topic? 0
  • +

Replies To: Invalid query: You have an error in your SQL syntax;

#2 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 11:43 AM

You have a syntax error in your SQL query. Specifically, you left a trailing comma after '$rank'

$query="INSERT INTO 'users' ('user_name','password','email','faction','confirmed','admin','beta_tester','wins','losses','points','rank') VALUES ('$user',(md5('$password')),'$email','$faction','$confirmed','$admin','$beta_tester','$wins','$losses','$points','$rank',);";



should be

$query="INSERT INTO 'users' ('user_name','password','email','faction','confirmed','admin','beta_tester','wins','losses','points','rank') VALUES ('$user',(md5('$password')),'$email','$faction','$confirmed','$admin','$beta_tester','$wins','$losses','$points','$rank')";



you also don't need both those semicolons. Just the one outside the double quotes will be fine. If you were running multiple queries at the same time you'd need the other one.

Hope that fixes your problem.
Was This Post Helpful? 0
  • +
  • -

#3 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:07 PM

View Postakozlik, on 4 Jun, 2008 - 11:43 AM, said:

You have a syntax error in your SQL query. Specifically, you left a trailing comma after '$rank'


you also don't need both those semicolons. Just the one outside the double quotes will be fine. If you were running multiple queries at the same time you'd need the other one.

Hope that fixes your problem.



I fixed that [over looked that and THANKS!] but I'm getting the same error.


I've done an insert via PHPMyAdmin and the syntax follows what they produce so I'm at a loss.
Was This Post Helpful? 0
  • +
  • -

#4 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:11 PM

View Postrjolitz, on 4 Jun, 2008 - 02:07 PM, said:

View Postakozlik, on 4 Jun, 2008 - 11:43 AM, said:

You have a syntax error in your SQL query. Specifically, you left a trailing comma after '$rank'


you also don't need both those semicolons. Just the one outside the double quotes will be fine. If you were running multiple queries at the same time you'd need the other one.

Hope that fixes your problem.



I fixed that [over looked that and THANKS!] but I'm getting the same error.


I've done an insert via PHPMyAdmin and the syntax follows what they produce so I'm at a loss.


Well it says that the error is on line 1, so it's probably not what we though it was. I have a feeling the issue actually lies in your 'smart_include.php' or 'db_var.php' files. Can you post those without the login information? Just substitute anything sensitive with xxxxxxxx.
Was This Post Helpful? 0
  • +
  • -

#5 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:21 PM

View Postakozlik, on 4 Jun, 2008 - 12:11 PM, said:

Well it says that the error is on line 1, so it's probably not what we though it was. I have a feeling the issue actually lies in your 'smart_include.php' or 'db_var.php' files. Can you post those without the login information? Just substitute anything sensitive with xxxxxxxx.



Here they are:

db_var.php

<?php
$dbuser="XXXXXXXX";
$pw="XXXXXXXX";
$db="XXXXXXXX";

$host="XXXXXXXX";

?>



and smarty_include.php


<?php

require('/home/emicarn/sectorbattles.com/Smarty/libs/Smarty.class.php');
$smarty = new Smarty();

$smarty->template_dir = '/home/emicarn/sectorbattles.com/Smarty/templates';
$smarty->compile_dir = '/home/emicarn/sectorbattles.com/Smarty/templates_c';
$smarty->cache_dir = '/home/emicarn/sectorbattles.com/Smarty/cache';
$smarty->config_dir = '/home/emicarn/sectorbattles.com/Smarty/configs';


?>


Was This Post Helpful? 0
  • +
  • -

#6 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:50 PM

on line 120, instead of:
(md5('$password'))


try putting that into a variable, and then putting it into the Query...
Was This Post Helpful? 0
  • +
  • -

#7 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:51 PM

View PostJBrace1990, on 4 Jun, 2008 - 02:50 PM, said:

on line 120, instead of:
(md5('$password'))


try putting that into a variable, and then putting it into the Query...


You know, I think you're right on that one. Momentary lapse in debugging on my part. ha ha.
Was This Post Helpful? 0
  • +
  • -

#8 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:53 PM

View Postakozlik, on 4 Jun, 2008 - 12:51 PM, said:

View PostJBrace1990, on 4 Jun, 2008 - 02:50 PM, said:

on line 120, instead of:
(md5('$password'))


try putting that into a variable, and then putting it into the Query...


You know, I think you're right on that one. Momentary lapse in debugging on my part. ha ha.


lol... when I threw it into my PHP editor, it flew right out at me =P

i'm like "wait a minute, that doesn't look right!"
Was This Post Helpful? 0
  • +
  • -

#9 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 12:55 PM

Ha ha, yeah I know that feeling. It kinda flagged when I looked at it but I didn't think anything of it. I'm working on work stuff, freelance stuff and trying to write part 2 of my regex tutorial, so I think my brain just collapsed. Ha ha.
Was This Post Helpful? 0
  • +
  • -

#10 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 11:24 PM

View PostJBrace1990, on 4 Jun, 2008 - 12:53 PM, said:

lol... when I threw it into my PHP editor, it flew right out at me =P

i'm like "wait a minute, that doesn't look right!"



Thanks for the help guys. If fixed everything you pointed out.

I finally tracked it down when the error persisted.

For some reason, putting ' ' around the fields in user made it choke. When I took the ' ' from around the field names and left them around the '$somevariable' names it worked flawlessly.
Was This Post Helpful? 0
  • +
  • -

#11 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 04 June 2008 - 11:25 PM

Wow, definitely should've caught that. My bad. Glad you got it fixed!
Was This Post Helpful? 0
  • +
  • -

#12 rjolitz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 110
  • Joined: 17-May 08

Re: Invalid query: You have an error in your SQL syntax;

Posted 05 June 2008 - 01:32 PM

View Postakozlik, on 4 Jun, 2008 - 11:25 PM, said:

Wow, definitely should've caught that. My bad. Glad you got it fixed!


So should I!! Staring at it didn't help and the SQL code generaged in MyPHPAdmin generated the same style I was using. So I'm not sure where the mismatch was THERE.

I can tell you this has been a major learning experience and I think my brain is melting!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1