[SOLVED] SQL error!

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

[SOLVED] SQL error!

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Last edited by Weirdan on Thu Jul 08, 2004 5:35 pm, edited 1 time in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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* ;)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

heh ;)
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

UPDATE orders SET `$name` = '$name1' WHERE fullname = '$fullname'
?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Mysql doesn't like -'s in table names, so change product-carrots to productcarrots or backquote it:
`product-carrots`
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post 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!
Post Reply