PHP insert into SQL statement with several parameters

Question

i have a simple php INSERT INTO SQL statement that simply refuses to update several columns at once. i have no idea why but the following statement is acceptabel;

$sql = "INSERT INTO niceTable (first) VALUES ('Hello')";

however if i try to following

$sql = "INSERT INTO niceTable (first, last) VALUES ('Hello', 'You')";

it breaks down and throws the following error:

"Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'desc) VALUES ('Hello', 'update')' at line 1"

I have checked the syntax, but it seems ok. I am using a one.com server. Anyone got any tips?

I suggest you find the correct SQL statement. The one that is actually reporting the error rather than just any randon SQL statement in your code. The letters `desc` dont appear in your example code — RiggsFolly, Jul 27 '15 at 14:56
the error message you posted along with the column names, do not reflect the question — Funk Forty Niner, Jul 27 '15 at 15:10
i switched the names of the column naturally to reflect an example. other answer got the question and answered it — Saucepan, Jul 27 '15 at 15:14

Funk Forty Niner · Answer 1 · 2015-07-27T15:08:44.470

As per your "posted code":

The reason being that first and last are MySQL reserved words

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

and require special attention.

Either wrap them in ticks or rename them to something other than reserved keywords.

INSERT INTO niceTable (`first`, `last`)

Edit: However, your error doesn't support the issue here, nor the column name(s):

for the right syntax to use near 'desc)

this tells me you are using desc which is also another MySQL reserved word.

You should also use prepared statements

https://en.wikipedia.org/wiki/Prepared_statement

Plus, should your inputs contain characters that MySQL may complain about such as apostrophes John O'Neil then you will need to escape those values.

MySQL will interpret that as ('Hello', 'John O'Neil') in turn causing another syntax error.

Escaping it, would interpret it as ('Hello', 'John O\'Neil') making it valid.

I'm thinking ahead here.

thank you for the elaborate answer! i am planning to use prepared statements, however at this point i just wanted to establish the connection :). cheers! — Saucepan, Jul 27 '15 at 15:15
@Saucepan you're welcome, glad to have been of help. *Cheers* — Funk Forty Niner, Jul 27 '15 at 15:16

score 2 · Accepted Answer · answered Jul 27 '15 at 14:56

2

Your actual query (not the one in your question) seems different. The error message seems to have desc somewhere, which is a reserved word. If you use reserve words as column names (don't), you should enclose them in backticks:

INSERT INTO tbl (`order`, `desc`) VALUES ('foo', 'bar');

answered Jul 27 '15 at 14:56

Bart Friederichs

33,050
15
95
195

I upvoted this because you noticed the actual column name OP is *really* using. Good catch Bart. – Funk Forty Niner Jul 27 '15 at 15:07
oh, i had no idea there was reserved words. i updated it and changed the word, and it worked! thank you for your help! – Saucepan Jul 27 '15 at 15:08

score 1 · Answer 3 · edited Jul 27 '15 at 15:14

1

Enclose your column names in backticks

Last is a function in MySQL

$sql = "INSERT INTO niceTable (`first`, `last`) VALUES ('Hello', 'You')";

edited Jul 27 '15 at 15:14

Funk Forty Niner

74,450
15
68
141

answered Jul 27 '15 at 14:54

Geoff Atkins

1,693
1
17
23

PHP insert into SQL statement with several parameters

3 Answers3