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
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Thu Jul 08, 2004 5:21 pm
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
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Thu Jul 08, 2004 5:33 pm
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.
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Thu Jul 08, 2004 5:34 pm
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*
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Thu Jul 08, 2004 5:36 pm
heh
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Thu Jul 08, 2004 5:44 pm
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.
Weirdan
Moderator
Posts: 5978 Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine
Post
by Weirdan » Thu Jul 08, 2004 5:47 pm
Code: Select all
UPDATE orders SET `$name` = '$name1' WHERE fullname = '$fullname'
?
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Thu Jul 08, 2004 5:51 pm
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
markl999
DevNet Resident
Posts: 1972 Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)
Post
by markl999 » Thu Jul 08, 2004 5:52 pm
Mysql doesn't like -'s in table names, so change product-carrots to productcarrots or backquote it:
`product-carrots`
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Thu Jul 08, 2004 6:05 pm
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!