Page 1 of 1
[SOLVED] SQL error!
Posted: Thu Jul 08, 2004 5:21 pm
by Joe
I am having a bit of a problem with an order form that I am developing and it is caused by the sql that I am using. I am not sure if it's possible to do this but I was basically trying to execute 18 querys on one table at one time.
My code go's like:
Code: Select all
$sql = "SELECT * FROM images WHERE tag = '0'";
$result = mysql_query($sql) or die(mysql_error());
while (true)
{
$row = mysql_fetch_assoc($result);
if ($row == false) break;
$name = $row['name'];
$name1 = $_POST[$name];
$sql = "UPDATE orders SET $name = $name1 WHERE fullname = $fullname";
$result = mysql_query($sql) or die(mysql_error());
}
The error that I get is:
You have an error in your SQL syntax near '(carrots = 350) WHERE fullname = Test Name' at line 1
Posted: Thu Jul 08, 2004 5:33 pm
by Weirdan
where the brackets (in bold) comes from?:
(carrots = 350
) WHERE fullname = Test Name' at line 1
and you need to put the quotes around the $fullname and $name1 like this:
Code: Select all
$sql = "UPDATE orders SET $name = '$name1' WHERE fullname = '$fullname'";
and turn your second:
Code: Select all
$result = mysql_query($sql) or die(mysql_error());
into
Code: Select all
mysql_query($sql) or die(mysql_error());
because you're overwriting $result on first iteration, so on second iteration mysql_fetch_assoc($result) would fail.
Posted: Thu Jul 08, 2004 5:34 pm
by markl999
You need to quote the vars otherwise sql treats them as column names and not values (unless it's an INT type column):
Code: Select all
$sql = "UPDATE orders SET $name = '$name1' WHERE fullname = '$fullname'";
*beaten to it*

Posted: Thu Jul 08, 2004 5:36 pm
by Weirdan
heh

Posted: Thu Jul 08, 2004 5:44 pm
by Joe
Now with the code:
Code: Select all
$sql = "SELECT * FROM images WHERE tag = '0'";
$result = mysql_query($sql) or die(mysql_error());
while (true)
{
$row = mysql_fetch_assoc($result);
if ($row == false) break;
$name = $row['name'];
$name1 = $_POST[$name];
$sql = "UPDATE orders SET $name = '$name1' WHERE fullname = '$fullname'";
mysql_query($sql) or die(mysql_error());
}
I get the error: You have an error in your SQL syntax near 'product-carrots = '350' WHERE fullname = 'Test Name'' at line 1
I had to change carrots to product-carrots.
Posted: Thu Jul 08, 2004 5:47 pm
by Weirdan
Code: Select all
UPDATE orders SET `$name` = '$name1' WHERE fullname = '$fullname'
?
Posted: Thu Jul 08, 2004 5:51 pm
by Joe
Damn this is pretty tricky, The error I seem to get now is:
You have an error in your SQL syntax near ''product-carrots' = '350' WHERE fullname = 'Test Name'' at line 1
Posted: Thu Jul 08, 2004 5:52 pm
by markl999
Mysql doesn't like -'s in table names, so change product-carrots to productcarrots or backquote it:
`product-carrots`
Posted: Thu Jul 08, 2004 6:05 pm
by Joe
Thanks alot. I got rid of the -'s in the database table and used:
Code: Select all
while (true)
{
$row = mysql_fetch_assoc($result);
if ($row == false) break;
$name = $row['name'];
$name1 = $_POST[$name];
$name = str_replace("-","", $name);
$sql = "UPDATE orders SET $name = '$name1' WHERE fullname = '$fullname'";
mysql_query($sql) or die(mysql_error());
}
as the code. All worked perfect!