php - When to use single quotes, double quotes, and backticks in MySQL -

i trying learn best way write queries. understand importance of being consistent. until now, have randomly used single quotes, double quotes, , backticks without real thought.

example:

$query = 'insert table (id, col1, col2) values (null, val1, val2)'; 

also, in above example, consider "table," "col[n]," , "val[n]" may variables.

what standard this? do?

_{i've been reading answers similar questions on here 20 minutes, seems there no definitive answer question.}

backticks used table , column identifiers, necessary when identifier mysql reserved keyword, or when identifier contains whitespace characters or characters beyond limited set (see below) recommended avoid using reserved keywords column or table identifiers when possible, avoiding quoting issue.

single quotes should used string values in values() list. double quotes supported mysql string values well, single quotes more accepted other rdbms, habit use single quotes instead of double.

mysql expects date , datetime literal values single-quoted strings '2001-01-01 00:00:00'. consult the date , time literals documentation more details, in particular alternatives using hyphen - segment delimiter in date strings.

so using example, double-quote php string , use single quotes on values 'val1', 'val2'. null mysql keyword, , special (non)-value, , therefore unquoted.

none of these table or column identifiers reserved words or make use of characters requiring quoting, i've quoted them anyway backticks (more on later...).

functions native rdbms (for example, now() in mysql) should not quoted, although arguments subject same string or identifier quoting rules mentioned.

backtick (`) table & column ───────┬─────┬──┬──┬──┬────┬──┬────┬──┬────┬──┬───────┐                       ↓     ↓  ↓  ↓  ↓    ↓  ↓    ↓  ↓    ↓  ↓       ↓ $query = "insert `table` (`id`, `col1`, `col2`, `date`, `updated`)                         values (null, 'val1', 'val2', '2001-01-01', now())";                                ↑↑↑↑  ↑    ↑  ↑    ↑  ↑          ↑  ↑↑↑↑↑  unquoted keyword          ─────┴┴┴┘  │    │  │    │  │          │  │││││ single-quoted (') strings ───────────┴────┴──┴────┘  │          │  │││││ single-quoted (') date    ───────────────────────────┴──────────┘  │││││ unquoted function         ─────────────────────────────────────────┴┴┴┴┘     

variable interpolation

the quoting patterns variables not change, although if intend interpolate variables directly in string, must double-quoted in php. make sure have escaped variables use in sql. (it recommended use api supporting prepared statements instead, protection against sql injection).

// same thing variable replacements // here, variable table name $table backtick-quoted, , variables // in values list single-quoted  $query = "insert `$table` (`id`, `col1`, `col2`, `date`) values (null, '$val1', '$val2', '$date')"; 

prepared statements

when working prepared statements, consult documentation determine whether or not statement's placeholders must quoted. popular apis available in php, pdo , mysqli, expect unquoted placeholders, prepared statement apis in other languages:

// pdo example named parameters, unquoted $query = "insert `table` (`id`, `col1`, `col2`, `date`) values (:id, :col1, :col2, :date)";  // mysqli example ? parameters, unquoted $query = "insert `table` (`id`, `col1`, `col2`, `date`) values (?, ?, ?, ?)"; 

characters requring backtick quoting in identifiers:

according mysql documentation, not need quote (backtick) identifiers using following character set:

ascii: [0-9,a-z,a-z$_] (basic latin letters, digits 0-9, dollar, underscore)

you can use characters beyond set table or column identifiers, including whitespace example, must quote (backtick) them.