which1ispink which1ispink - 4 months ago 19
MySQL Question

Why do I get "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax..."? (PDO-PHP)

I know this question has been asked many times but from the many ones I looked at it seems they always used a mysql reserved keyword as a table or column name or actually have a syntax error here or there, but I don't think I have either so I'd appreciate some help figuring out why I get this. Here's the function that causes the error:

public function user_exists($db, $username) {
$query = $db->prepare("SELECT COUNT('id') FROM 'users' WHERE 'username' = ?");
$query->bindValue(1, $username);

try {
$query->execute();
$rows = $query->fetchColumn();

if($rows == 1) {
return false;
}
else {
return true;
}
}
catch(PDOException $e) {
die($e->getMessage());
}
}


Then when it's called with, say, 'anything' as its $username parameter, here's the full error I get:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQLsyntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users' WHERE 'username'= anything' at line 1

Answer

Use backticks to escape table or column names, not quotes.

SELECT COUNT(`id`) FROM `users` WHERE `username` = ?