What's wrong with this query?

[dudejma]

I have this INSERT query:

 

$sql2 = "INSERT INTO hub_change (pilotID, from, to, reason, expDate) VALUES ('$pilotid', '$oldHub', '$newHub', '$reason', '$expDate')";

 

And I always get this error:

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 'from, to, reason, expDate) VALUES ('1', '2', '3', '4', '5')' at line 1

 

What's wrong with it, anyone know? Thanks!

[Muddy_Funster]

you are wrapping integer values within quotes, thus making them strings.  This will not please your database if the fields are set to int, take the quotes out.  Also, your actual error is coming from the fact that you have names a column in your table as "from". This is a reserved word.  you will either have to rename the column (Recomended) or put backticks arround the colum title to tell your database not to process this as it normaly would.

Final result using backticks should be:

INSERT INTO hub_change (pilotID, `from`, to, reason, expDate) VALUES ($pilotid, '$oldHub', '$newHub', '$reason', '$expDate'

 

I have only removed the single quotes from $pilotid as I expect that the others are possible varchar/text/date fields that you are entering numerical values into as test data, if they are not then you will need to remove the quotes from thise values aswell.

[The Little Guy]

it is highly recommended that you don't use reserved words as table columns. It just makes debugging harder.

[chintansshah]

make a habit to add (`) for table fields in queries, But before that you should understand the database designing. Because you should not take (from) as a field name. DO NOT USE reserve keyword as a field name.

[Muddy_Funster]

make a habit to add (`) for table fields in queries, But before that you should understand the database designing. Because you should not take (from) as a field name. DO NOT USE reserve keyword as a field name.

 

If you do not use reserverd words then there is absoloutly no need to use backticks either.  Why should anyone get into that habit?

[fenway]

you are wrapping integer values within quotes, thus making them strings.  This will not please your database if the fields are set to int, take the quotes out.

But if you're not quoting your literals, you will be open to injection attacks.

[Muddy_Funster]

But if you're not quoting your literals, you will be open to injection attacks.

shouldn't that be dealt with independantly?

[fenway]

I like to strictly enforce where expressions can be used and where the can't be.  Quoting an integer doesn't really hurt.

[kickstart]

make a habit to add (`) for table fields in queries, But before that you should understand the database designing. Because you should not take (from) as a field name. DO NOT USE reserve keyword as a field name.

 

I would be inclined to say to never use a back tick around field names, etc. That way mistakenly using a reserved word for a field name is obvious as early as possible and can be changed to something less likely to cause issues before much code has been written.

 

All the best

 

Keith

[fenway]

Yup, agreed -- it's a horrible crutch, useful only for dealing with legacy schemas or random third-party garbage.