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!