MySQL – when to use single quotes, double quotes, and backticks?
Question by Nate
I’ve been reading answers to similar questions on here for about 20 minutes, but it seems like there is no definitive answer to this question.
I am a newbie PHP programmer and am trying to learn the best way to write queries. I also understand the importance of being consistent, and up until now I have essentially randomly used single quotes, double quotes, and backticks without any real thought.
Example:
$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';
Also, in the above example, consider that "table," "col[n]," and "val[n]" may be variables.
What is the standard for this? What do you do?
Thanks for your advice.
Answer by Michael Berkowski
Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword.
Single quotes should be used for string values like in the VALUES() list. Double quotes are supported by MySQL for string values as well, but single quotes are more widely accepted by other RDBMS, so it is a good habit to use single quotes instead of double.
So using your example, I would double-quote the PHP string and use single quotes on the values 'val1', 'val2'. NULL is a MySQL keyword, and a special (non)-value, and is therefore unquoted.
None of these table or column identifiers are reserved words or make use of characters requiring quoting, but I’ve quoted them anyway with backticks.
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'val1', 'val2')";
//---------------------------------------------------------------^^^^^^^^^^^^^^^^ Single-quoted strings
//----------------------------------------------------------^^^^^ Unquoted keyword
//-------------------^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Backtick table & column
The quoting patterns for variables do not change, although if you intend to interpolate the variables directly in a string, it must be double-quoted in PHP. Just make sure that you have properly escaped the variables for use in SQL.
// Same thing with some variable replacements
$query = "INSERT INTO `$table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";
According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:
ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
You can use characters beyond that set as table or column identifiers, but then you must quote (backtick) them.
Answer by Starx
Backticks are generally used to indicate an identifier and as well be safe from accidentally using the Reserved Keywords.
For example:
Use `database`;
Here the back ticks will help the server to understand that the database is infact the name of the database, not the database identifier.
Same can be done for the table names and field names. This is very good habit if you wrap your database identifier with a backticks.
Check this answer to understand more about backticks.
Now about Double quotes & Single Quotes (Michael has already mentioned that).
But, to define a value you have to use either single or double quotes. Lets see another example.
INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, title1);
Here I have deliberately forgot to wrap the title1 with a quotes. Now the server will take the title1 as a column name (i.e. an identifier). So, to indicate its a value you have to use either double or single quotes.
INSERT INTO `tablename` (`id, `title`) VALUES ( NULL, 'title1');
Now, in combination with PHP, double quotes and single quotes make your query writing time so easier. Lets see a modified version of the query in your question.
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";
Now, using double quotes in the PHP, you will make the variables $val1, and $val2 to use their values thus creating a perfectly valid query. Like
$val1 = "my value 1";
$val2 = "my value 2";
$query = "INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, '$val1', '$val2')";
Will make
INSERT INTO `table` (`id`, `col1`, `col2`) VALUES (NULL, 'my value 1', 'my value 2')
