SQL Syntax Error

[Whatsthis4]

I'm trying to update multiple columns at once in my database. The updated information is coming from another form page. This is my code and below it is the error. I have also tried this with single quotes and no brackets and I receive the same error message without the brackets with quotes.

line 21> $query = "UPDATE inventory SET type = $type [, order = $order]
[WHERE id = $id] LIMIT 1";

could not execute query.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 '[, order = 02] [WHERE id = 2] LIMIT 1' at line 21

Below works fine for a single column, so the connection gets made.

line 21> $query = "UPDATE vehicles SET type='$type'
WHERE id='$id' LIMIT 1";

Thank you in advance for any help offered.

[dreamcatcher]

Hi Whatsthis4,

You don`t need those square brackets in your query, which is no doubt throwing the error.

Try:

$query = "UPDATE inventory SET type = '$type',order = '$order' WHERE id = '$id' LIMIT 1";

dc

[henry0]

Welcome on WebmasterWorld!

what's the brakets for?

try:
$query = "UPDATE inventory
SET type = '$type',
order = '$order'
WHERE id = $id LIMIT 1 ";

<<< EDIT: you shoot faster than I did-
Hi Dreamcatcher, I guess we are on the same time zone :)
>>>

[Whatsthis4]

Thank you for your response.

I have tried that combination and a few others. Still get the error message with the changes reflected. This is how my book showed to do it and was my first try. The brackets were something I found on the web. I cut this short because it easier to change with less columns. There are really about 16 columns to update.

$query = "UPDATE vehicles SET type = '$type',order = '$order',stockID = '$stockID'
WHERE id = '$id' LIMIT 1";

could not execute query.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 'order = '02',stockID = '227' WHERE id = '2' LIMIT 1

According to my book this is supposed to be very straight forward?

[henry0]

In that case I would look at all var values
and echo all vars to be sure that they are passed OK.
a little tip when you echo a whole bunch of var
do this
for example about ID
echo"ID: $id<br>";
so you know what you deal with and get some format.

[Whatsthis4]

I did that when I created the other page. I sent it to a different page that checked the output and it listed everything as it should be. The output shown in the error has the right numbers also which tells me it is getting the right values.

Thanks though. I've been at this page for a while now. Getting a small bald spot from the hair ripping. : )

[inveni0]

Okay, two things in my experience:

First, if I'm using mySQL, I get errors trying to run plain SQL code. Not sure what your setup is.

The most likely is this. You haven't used ` to enclose "order". Try:

$query = "UPDATE inventory SET type = '$type', `order` = '$order' WHERE id = '$id' LIMIT 1";

See, ORDER BY is a command in queries. Since you have a column named `order`, you must separate with ` so that the code knows it's not a command, and is, instead, a column name.

[Whatsthis4]

inveni0, you rock!

That was exactly it. Thank you all so much!