$query = "SELECT * FROM `users` WHERE `username` = 'admin'";#works
$query = "SELECT * FROM 'users' WHERE 'username' = 'admin'";#does not work
Is this yet another quirk Im going to have to get used to, or is something funny going on?
views:
45answers:
3
+7
A:
Single quotes (') and double quotes (") are used to specify strings in MySQL. Backticks (`) are used for column/table references.
Your second query will fail for two reasons:
'users'specifies a string, not a reference to the tableusers, andFROMexpects a table reference.'username' = 'admin'does a string comparison, and the stringusernameis never equal to the stringadmin.
Daniel Vandersluis
2010-09-07 19:47:20
+1: SQL in general considers anything inside single quotes to be text, not a column name. MySQL uses backticks (no others to my knowledge), but they're [only necessary to escape the times when someone made the poor decision to use what is a reserved word in MySQL](http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html)
OMG Ponies
2010-09-07 19:51:05
If you enable ANSI mode, then the standard double quotes are accepted as well for column and table names (making MySQL more compliant with the SQL standard - in fact there are a bunch of options that should be turned on to disable some of the non-standard MySQL quirks)
a_horse_with_no_name
2010-09-07 19:56:44
A:
It's not legal syntax to quote the name of a column with '
The ` (backtick) is used to quote identifiers.
Since none of your columns are reserved keywords, this would work too:
"SELECT * FROM users WHERE username = 'admin'"
nos
2010-09-07 19:48:52
A:
In MySQL, by default single-quotes (') and double-quotes (") are literal string delimiters, while backticks (`) are identifier quotes. If you set the SQL mode to include ANSI_QUOTES, then double-quotes will also be identifier quotes rather than literal string delimiters.
Hammerite
2010-09-07 19:50:58