Jump to content

[SOLVED] PHP Sql Syntax Error on Update Query (maybe a reserved word?)


jaxdevil

Recommended Posts

I copied the error and my code below. I do not understand what the problem is? I tried encapsulating the word mod in quotes like this `mod` but that didn't help/ Any ideas?

 

An error has ocured: 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 '= 'WCCD-WCC650CX1'' at line 1:1064
A

 

<?php
$query = "UPDATE products SET supplier='$supplier',cat='$cat',subcat='$subcat',`man`='$man',`mod`=$mod,name='$name',list='$list',price='$price',specs='$specs',pdf='$pdf',active='$active' WHERE mod= '$mod'";
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
print "<h1>Inventory Update Successful</h1><br>data<br /><br /><em>Updated!</em><br /><br />";
?>

Link to comment
Share on other sites

The column named mod is the one that has the value of WCCD-WCC650CX1 (the variable for that is $mod), which is why I gathered from the sql error the fault was somewhere around there. I have this code on multiple places in mutliple sites, I modified this one slightly but this portion of the code is almost exactly to the other ones and they all work, I don't know why this one is not. I mean if it is a reserved word I should be able to close it off with the `` at the beginning and end of the word but that isn't working.

 

I appreciate your help :)

Link to comment
Share on other sites

Yes but I use the same variables in many php scripts I have made, this update function I use on multiple things and change the variables for the particular database, but I always use the same variable as the column name, for simplicity and clean coding. I haven't had that problem before. Any ideas?

Link to comment
Share on other sites

The value before is WCCD-WCC650CX , I was just running the first test of it after copying the code over and modifying it for this purpose. So I just added a 1 on the end of the model number. So the script is trying to change from WCCD-WCC650CX -> WCCD-WCC650CX1

 

Thanks guys!

 

SK

Link to comment
Share on other sites

You don't have $mod enclosed in quotes (it's a string).

`mod`='$mod'

 

I thought about that too, thats actually why the quotes are off of it, if you can see I had them on everything (I always put them on everything) but since I couldn't figure it out I just tried every combination of ways to format the string trying to 'get lucky'. So $mod is actually the only one I removed the quotes on. I just put them back and its still the same error.

 

Thanks for the idea though.

 

SK

Link to comment
Share on other sites

Sometimes I find it helps to echo the $query to see exactly what it's trying to do...

 

if the value of $mod is WCCD-WCC650CX1

and the row `mod`before the update is WCCD-WCC650CX

 

then this would present a problem because you're trying to UPDATE ... WHERE mod= '$mod'

 

in other words WHERE mod=WCCD-WCC650CX1 which I don't think would be found?

 

I would try making another variable here,

 

<?php
$oldmod = substr($mod,0,strlen($mod)-1); // removes the '1' at the end of $mod.
$query = "UPDATE products SET supplier='$supplier',cat='$cat',subcat='$subcat',`man`='$man',`mod`=$mod,name='$name',list='$list',price='$price',specs='$specs',pdf='$pdf',active='$active' WHERE mod= '$oldmod'";
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
print "<h1>Inventory Update Successful</h1><br>data<br /><br /><em>Updated!</em><br /><br />";
?>

Link to comment
Share on other sites

Thank you Symmetry!

 

That didn't work, but it put me in the right area to see where the problem is. It was the WHERE statement that was the problem, the mod='$mod' was without quotes, I made it `mod`='$mod' and it works now! YAY!!

 

Thanks everyone for the help, I wouldn't have figured it out with out you.

 

Thanks Symmetry!

Link to comment
Share on other sites

Oh great, something else now. Well the function is processing, it has no more sql errors, but the data is posting incorrectly. You see the 11 fields, right? Well it is posting the $price variable into the `man` column, and posting the $list variable into the `price` column, and is posting nothing into the `list` column, or rather it is erasing the data that was in the `list` column since it is leaving nothing in that field after the updating has processed.

 

Please look at the revised code and see if you see anything. I put quotes on everything now, just to be safe, but that didn't do it.

 

<?php
$query = "UPDATE products SET `supplier`='$supplier',`cat`='$cat',`subcat`='$subcat',`man`='$man',`mod`='$mod',`name`='$name',`list`='$list',`price`='$price',`specs`='$specs',`pdf`='$pdf',`active`='$active' WHERE `mod`='$oldmod'";
mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());
print "<h1>Inventory Update Successful</h1><br>data<br /><br /><em>Updated!</em><br /><br />";
?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.