How to fix syntax error with single and double quotes

Question

I am new to php and cannot seem to find the error in what I have. I am trying to update a database with a number. I am using an exact replica of what works in another row of the same table. However, I keep getting an error that says there is a syntax error near 'order=IF(LENGTH('3')=0, order, '3') WHERE id='1" at line 1'. Notice that there is a single quote then double quote after id='1". Is there something wrong in my code?

if(isset($_POST['nso']))
{
$nso=$_POST['nso'];
$id=$_POST['id'];
$sql="UPDATE series SET order=IF(LENGTH('$nso')=0, order, '$nso') WHERE id='$id'";
$response=mysql_query($sql) or die("Not able to update." .mysql_error());
echo "<meta http-equiv='refresh' content='0;url=DBE.php'>";
}

• diegoperini: What is type of series.id column in your db? Sidenote: Avoid mysql_x() calls in php. They are now deprecated and considered unsafe. Prefer a library like mysqli that support prepared statements. Sidenote2: Always escape data you recieve from users before putting them in your queries.
• Jeremy Tyler: The series.id is my primary key and basically tells me what row to edit. It is an INT(11). Thanks for the tips. I am really new at this, but am taking a crack at a project in order to learn.
• diegoperini: Did you try removing single quotes that encapsulate $id in your query? $sql="UPDATE series SET order=IF(LENGTH('$nso')=0, order, '$nso') WHERE id=$id"; <- Like this
• Jeremy Tyler: Just tried it and it gave me the same error saying "id=1' at line 1"

Answer 1

You can try putting order in backticks because it is a keyword in mysql.

UPDATE series SET `order`=IF(LENGTH('$nso')=0, `order`, '$nso') WHERE id='$id'

If this doesn't work you can try removing apostrophes from around values of columns with (most probably) numerical values:

id:

UPDATE series SET `order`=IF(LENGTH('$nso')=0, `order`, '$nso') WHERE id=$id

order:

UPDATE series SET `order`=IF(LENGTH('$nso')=0, `order`, $nso) WHERE id='$id'

or both:

UPDATE series SET `order`=IF(LENGTH('$nso')=0, `order`, $nso) WHERE id=$id

• Jeremy Tyler: Tried your suggestion, but I am still getting problems with single and double quotes. Here is the message I received when followed your suggestion... syntax error near "order'=IF(LENGTH('3')=0, 'order', '3') WHERE id='3"
• Nebril: Note there's a difference between ' from your code and ` from mine. I hope this is the issue here.
• Jeremy Tyler: Ok, thanks for that clarification. I will check on it and update back soon.
• Jeremy Tyler: Ah... Finally! Yes, your solution worked! It was the ` that did the trick. I made a few other mistakes in my attempts, but managed to correct them along the way. Thanks for the help!
• Carlos Campderrós: ` is called a backtick