MySQLi Syntax Error (PHP) on INSERT using Variables

Question

I am attempting to insert some user-inputted data into my MySQL table using the following command:

$sql = "INSERT INTO Queued ('$role') VALUES ('$sname')";

Interestingly enough, I get the following error:

Error: INSERT INTO Queued ('Tops') VALUES ('Summoner') 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 ''Tops') VALUES ('Summoner')' at line 1

To be honest, I am relatively new at using PHP as well as MySQL, but I can't seem to find the error in my syntax; the Queued table does exist, $role and $sname are both strings so I encased them in single quotes. I suspect this is a newbie mistake, could anyone point me in the right direction?

Use INSERT INTO Queued ($role), but better yet, use a prepared statement. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 17, 2018 at 6:10
Ah, thanks that fixed the problem. Why do I need to exclude single quotes around $role but not $sname? Aren't they both interpreted as strings? — Tristan Melton
– Tristan Melton, Commented Jul 17, 2018 at 6:12
Yes, they are both interpreted as strings, but that happens with or without single quotes. Column names don't take single quotes in MySQL (or really in any other database). — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 17, 2018 at 6:12
Possible duplicate of When to use single quotes, double quotes, and back ticks in MySQL — Nigel Ren
– Nigel Ren, Commented Jul 17, 2018 at 6:28

Lovepreet Singh · Accepted Answer · 2018-07-17 06:11:14Z

2

This is due to use of single quotes ' around the column name. The query should be like:

$sql = "INSERT INTO Queued ($role) VALUES ('$sname')";

OR

$sql = "INSERT INTO Queued (`$role`) VALUES ('$sname')";

answered Jul 17, 2018 at 6:11

Lovepreet Singh

4,8701 gold badge21 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tristan Melton Over a year ago

What is the role of the `s around $role? Interpret this as non-SQL?

Tim Biegeleisen Over a year ago

The OP should be using a statement, and this isn't the best answer which can be given. Also, backticks around $role aren't needed.

Lovepreet Singh Over a year ago

` are usually used around column names in mysql query.

Hussain · Accepted Answer · 2018-07-17 06:33:29Z

0

Try this format

$sql = "INSERT INTO Queued ('".$role."') VALUES ('".$sname."')";

answered Jul 17, 2018 at 6:33

Hussain

499 bronze badges

Comments

firashelou · Accepted Answer · 2018-07-17 07:08:07Z

0

`s role is to differentiate between built in SQL words and the column names, so if a word is used for name of a column that might be also a built in sql expression then `` are needed around it

answered Jul 17, 2018 at 7:08

firashelou

13110 bronze badges

Collectives™ on Stack Overflow

MySQLi Syntax Error (PHP) on INSERT using Variables

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related