Jump to content

SQL Syntax Problem


Recommended Posts

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 21Below 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.

Link to comment
Share on other sites

the following lets you know that the query is not what you expected

[, order = 02] [WHERE id = 2] LIMIT 1

maybe I am just old school... but it is good to echo your variable values when something like this goes wrong. just echo $query, and you will see what it is, and realize that it isn't the value you expect, right?try this instead:

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

I don't know what types your values are, but if they are varchar or other character data, the end result needs to be surrounded by single quotes so put single quotes around each variable in the query that needs it, take them off of the values that don't need them ( I wasn't sure what type "order" would be, so I left off the quotes )as far as still getting errors the other way you tried.. echo the value of $query to see what is going on. make adjustments until it looks like it should.I don't think you would get the same error if you leave out the brackets, because the error shows you a piece of the query near where the error was found.... if you don't have brackets... that part would definitely be different.as far as the brackets go... they are used to show the syntax of a statement and indicate optional values, you don't actually use them in your queriesThe following is the syntax for the UPDATE statement. the square brackets let us know that LOW_PRIORITY, IGNORE, WHERE, ORDER BY, LIMIT are optional. It also lets us know that we of the option to update 1 or many columns.

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name    SET col_name1=expr1 [, col_name2=expr2 ...]    [WHERE where_condition]    [ORDER BY ...]    [LIMIT row_count]

did I mention you should echo the value of $query so you can get a better idea what is going on?does sql not like you using the column name "order" since "order" is actually part of sql????? and a reserved word??from MySQL Documentation:

A common problem stems from trying to use an identifier such as a table or column name that is a reserved word such as SELECT or the name of a built-in MySQL data type or function such as TIMESTAMP or GROUP.
If an identifier is a reserved word, you must quote it as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”.
A reserved word can be used as an identifier if you quote it.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...