php mysql query syntax question regarding ""'s

Question

Forgive me, I'm a beginner. The following code returns a parse error:

$query = "INSERT INTO scenario_needgames VALUES ("$id", "Battle of the Bulge")";

the query builder in phpMyAdmin gave me this slightly modified string that works:

$query = "INSERT INTO scenario_needgames VALUES (\"$id\" , \"Battle of the Bulge\");";

but I'm confused as to why the quotes need to be escaped when they're actually part of the query syntax, and not - say - part of a title or string? The introductory book I'm learning from doesn't include those for such simple strings.

The $id value is 7 digits, 4 letters and then 3 numbers if you're curious.

Thank you.

Answer 1

Double quotes need to be escaped within a double quoted string, alternatively you can use a single quoted string and not have to escape the double quotes, but then you cannot directly interpolate variables, you have to use concatenation instead:

$query = 'INSERT INTO scenario_needgames VALUES ("' . $id . '", "Battle of the Bulge")';

Alternatively, just replace your inner double-quotes with single-quotes:

$query = "INSERT INTO scenario_needgames VALUES ('$id', 'Battle of the Bulge')";

I would suggest using mysql_real_escape_string to correctly and safely quote strings. You might also like to have a look at using prepared statements instead with PDO or mysqli.

Answer 2

They are escaping because of PHP, not MySQL. You must escape " characters in "-" string because " character can be interpreted as the end of the string.

Look at the code coloring in your answer. The first string is colored wrongly: the parts in " and " are colored like code, not string

Answer 3

It's PHP which return a parse error.

From the point of view of PHP, a string is a sequence of characters delimited by quotes. One at the beginning and one at the end.

When you want to put quotes inside the string, you need to escape them, so PHP knows the internal quotes are not the end of the string.

Unless you escape them, PHP will return a parse error because the thing you're assigning to $query is not a valid string (with a quote at each end, only)