Use of ' in SQL statement

Question

I was doing some php; I found that the following code works

function get_total_urls() {    
    $total = mysql_query('SELECT COUNT(`url_key`) FROM `urls`');
    return (int)mysql_result($total, 0);
}
get_total_urls();

But following code shows warning

function get_total_urls() {    
    $total = mysql_query("SELECT COUNT('url_key') FROM 'urls'");
    return (int)mysql_result($total, 0);
}
get_total_urls();

Shows the following warning:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given.

Please help; why the 2nd code shows warning but 1st code is ok? The 2nd code fails to get the count of 'url_key' from database table.

Is there anyway to write the first code without back-tick and it will work perfectly?

That's the difference between an apostrophe and a backtick. In MySQL this carries meaning ... Backticks describe identifiers, whereas apostrophes mark strings. — Sirko, Mar 28 '15 at 16:28
The 2 queries are total different things. Use `'` for static strings and backticks to escape table and column names — juergen d, Mar 28 '15 at 16:29

score 4 · Accepted Answer · answered Mar 28 '15 at 18:34

This is your statement:

$total = mysql_query('SELECT COUNT(`url_key`) FROM `urls`');

In MySQL, backticks are used to delimit identifiers -- when they conflict with reserved words or contain unseemly characters (such as spaces, periods, commas, and so on). Because it is a good idea to have identifiers that do not conflict with reserved words and that contain no unseemly characters, backticks are rarely needed.

You can just write:

$total = mysql_query('SELECT COUNT(url_key) FROM urls');

Single quotes should only be used for string and date constants. Do not use them for identifiers, they just lead to confusion. As you have no doubt noticed.

...although you probably shouldn't be using a deprecated API — Strawberry, Mar 28 '15 at 19:01

score 2 · Answer 2 · edited May 23 '17 at 12:06

Both the codes are different.

Backticks are to be used for table and column identifiers,

The same as in your example; where url_key is a column name and urls is a table_name.

$total = mysql_query('SELECT COUNT(`url_key`) FROM `urls`');

But Backticks are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set.

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 in insert statement.

Ex:

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

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

Double quotes are also 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.

Find more explanation on this with similar example: When to use single quotes, double quotes, and backticks in MySQL

Use of ' in SQL statement

2 Answers2