You have an error in your SQL syntax;

[jack bro]

I have this code for a place where admins can create promotion codes.

 

$username=$_SESSION['username'];

if ($_POST['Submit']){
	// Define post fields into simple variables
	$code=strip_tags($_POST['PromoCode']);
	$info=strip_tags($_POST['Info']);

if((!$code) || (!$info)){
		$message="Fill in all fields";
}
		elseif (strlen($code) > 0 || strlen($info) >0){
$sql_promo_check = mysql_query("SELECT * FROM promocodes WHERE code='$code'");
$code_check = mysql_num_rows($sql_promo_check);

if($code_check > 0){
	$message= "Promo code $code already exsists in database.";
						}

	elseif ($code_check < 1){
		mysql_query("INSERT INTO `promocodes` (`id`, `code`, 'info', 'createdby')
VALUES ('', '$code', '$info', '$username')") or die (mysql_error());
						$message= "Promotion code $code has been inserted into the database and is now active.";
	}
	}
}
?>

 

When I submit the form if the code exists in database I get the right message but when I try to insert one that don't exist I get this message.

 

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 ''info', 'createdby') VALUES ('', 'test2', 'test2', 'Jack')' at line 1

 

Any ideas?

 

Thanks,

 

Jack.

[ohdang888]

this

         mysql_query("INSERT INTO `promocodes` (`id`, `code`, 'info', 'createdby')
VALUES ('', '$code', '$info', '$username')") or die (mysql_error());

 

 

should be this:

 

         mysql_query(" INSERT INTO `promocodes` (`id`, `code`, `info`, `createdby`)
VALUES (' ', '$code', '$info', '$username') ") or die (mysql_error());

 

in other words, to declare something is a column, you put these type of quotes around it: `

to delcare something a value that you are inserting or updating, etc. you use this: '

[nankoweap]

unless one's using keywords as table/column names, why put ticks around them at all? why not simply:

 

mysql_query("INSERT INTO promocodes (id, code, info,createdby)
VALUES ('', '$code', '$info', '$username')") or die (mysql_error());

 

jason

[premiso]

unless one's using keywords as table/column names, why put ticks around them at all? why not simply:

 

jason

 

That works and all. But I do simply because it does not harm anything, if something changes in MySQL in the future your SQL should still work and it takes an extra .05 seconds to add the backticks in and no more memory used to boot.

 

Adding in the backticks, to me, is a simple and easy way to know that the SQL will work and not fail cause of a column name. Worth the ease of mind, in my opinion.

[nankoweap]

fair enough. i reckon i'm at the other end of the spectrum. hell, i don't touch that key unless i'm typing a tilde.

[Daniel0]

I actually think the backtick is an incredibly annoying character. On Danish keyboards you don't have a key for just that, but rather a key for adding the grave accent above a letter, e.g. è. You need to use the shift key though, otherwise you'll get the acute accent. So you have to press Shift+´ and even then you don't get the char. It'll wait for the character you wish to apply it to, so you can press something like Space. It requires a minimum of three keystrokes to get that character using a Danish keyboard. I don't use it unless necessary in MySQL queries.

[premiso]

I actually think the backtick is an incredibly annoying character. On Danish keyboards you don't have a key for just that, but rather a key for adding the grave accent above a letter, e.g. è. You need to use the shift key though, otherwise you'll get the acute accent. So you have to press Shift+´ and even then you don't get the char. It'll wait for the character you wish to apply it to, so you can press something like Space. It requires a minimum of three keystrokes to get that character using a Danish keyboard. I don't use it unless necessary in MySQL queries.

 

Yea, if I had to go through that just to do ` I would not use it either lol! But my keyboard = 1 keystroke.