CODEWITHSUNDEEP
phpmysqlpdo
fckye
fckye

Reputation: 85

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?'

When I execute this code: 

$db = new Database();
$statement = $db->prepare("SHOW TABLES LIKE :table");            
if(!$statement)
    throw New Exception($db->errorInfo()[2]);

foreach($tables as $table){
    $statement->execute(array(':table' => $table));
    if($statement->rowCount() == 0)
        echo 'Table ' . $table . ' doesn\'t exist';
}

I get the following error:

Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1 in something.php on line 4

The error isn't thrown when I put single quotes around :table like so: $statement = $db->prepare("SHOW TABLES LIKE ':table'"); - but I thought you weren't supposed to put single quotes around parameters in prepared statements?
Also, I set this attribute to the database: $db->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);, so that $statement is false when wrong syntax used in the query, so I can throw an exception. The query can be executed when PDO::ATTR_EMULATE_PREPARES is set to true.

My question is: how can I check for errors in my query when using PDO prepared statements? And why do I get the syntax error?

Upvotes: 1

Views: 1347

Answers (1)

Barmar
Barmar

Reputation: 781340

You need to use the PDO::ATTR_EMULATE_PREPARES option because native prepared statements can only use placeholders where expressions are allowed. SHOW TABLES LIKE requires a literal string, not an expression, so you can't use a native placeholder there. So put this before your code:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

Upvotes: 3

Related Questions