[rjolitz]

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?

[akozlik]

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.

[rjolitz]

akozlik, 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.

[akozlik]

rjolitz, on 4 Jun, 2008 - 02:07 PM, said:

akozlik, 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.

[rjolitz]

akozlik, 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';


?>

[JBrace1990]

on line 120, instead of:

(md5('$password'))

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

[akozlik]

JBrace1990, 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.

[JBrace1990]

akozlik, on 4 Jun, 2008 - 12:51 PM, said:

JBrace1990, 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!"

[akozlik]

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.

[rjolitz]

JBrace1990, 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.

[akozlik]

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

[rjolitz]

akozlik, 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!