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.

6 Answers
6

Backticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set (see below) It is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

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.

MySQL also expects DATE and DATETIME literal values to be single-quoted as strings like '2001-01-01 00:00:00'

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 (more on this later...).

Prepared statements

When working with prepared statements, consult the documentation to determine whether or not the statement's placeholders must be quoted. The most popular APIs available in PHP, PDO and MySQLi, expect unquoted placeholders, as do most prepared statement APIs in other languages:

"but single quotes are more widely accepted by other RDBMS" - using single quotes for string literals is defined (and required) by the SQL standard
–
a_horse_with_no_nameJul 14 '14 at 8:57

"It is recommended to use an API supporting prepared statements instead, as protection against SQL injection" - Using prepared statements will not only do that, it will also save the DBMS from compiling the "same" query over and over again.
–
LennartMar 11 at 4:41

(There are good answers above regarding the SQL nature of your question, but this may also be relevant if you are new to PHP)

Perhaps it is important to mention that PHP handles single and double quoted strings differently... single-quoted strings are 'literals' and are pretty much WYSIWYG strings. Double-quoted strings are interpreted by PHP for possible variable-substitution. (backticks in PHP are not exactly strings, they execute a command in the shell and return the result).

Examples:

$foo = "bar";
echo 'there is a $foo'; // there is a $foo
echo "there is a $foo"; // there is a bar
echo `ls -l`; // ... a directory list

A string is a sequence of bytes or characters, enclosed within either
single quote (“'”) or double quote (“"”) characters.

So if your string contains single quote, then you could use double quote to quote the string, or if it contains double quote, then you could use single quote to quote the string. But if your string contains both single quote and double quote, you need to escape the one that used to quote the string.

Mostly, we use single quote for sql string value, so we need to use double quote for php string.

But if $val1 or $val2 contains single quote, that will make your sql be wrong. So you need to escape it before used in sql, that is what mysql_real_escape_string for. (Although prepared statement is better.)

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.