When to use single quotes, double quotes, and backticks?

Site Admin Joined: Jun 30, 2012 Posts: 213 Location: NL

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.

Example

Code:

$query = 'INSERT INTO table (id, col1, col2) VALUES (NULL, val1, val2)';

It should be

Code:

$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, 'val1', 'val2', '2001-01-01')";

Variable interpolation

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.

Code:

// Same thing with some variable replacements


$query = "INSERT INTO `$table` (`id`, `col1`, `col2`, `date`) VALUES (NULL, '$val1', '$val2', '$date')";

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:

Code:

// PDO example with named parameters, unquoted


$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (:id, :col1, :col2, :date)";





// MySQLi example with ? parameters, unquoted


$query = "INSERT INTO `table` (`id`, `col1`, `col2`, `date`) VALUES (?, ?, ?, ?)";

Characters requring backtick quoting in identifiers:

According to MySQL documentation, you do not need to quote (backtick) identifiers using the following character set:

Code:

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, including whitespace for example, but then you must quote (backtick) them.

	bestbuildpc Forum Index -> RavenNuke -> Tutorials	All times are GMT + 2 Hours
Page 1 of 1

Browser Support