Debugging PDO prepared queries with MySQL [duplicate]

Question

I'm making the change from mysql_* calls to using PDO calls and am unable to figure out how to debug my actual SQL when developing new code.

Under mysql_* calls I could write some SQL in a PHP function and could echo the actual SQL to see what the processor was working with.

I haven't been able to find such a beastie in the PDO library. debugDumpParams looks like it should, but it doesn't spit back the bound statement.

Examples of problems I have encountered:

1. In my very first attempt I was binding a string and was including the quotes in the SQL statement, despite binding with a data_type of string - it was only by fluke I tried removing the quotes from the statement; debugging would have let me see there was a repeated quote in there.

2. I copied some code from one project to another and accidentally forgot to correct the database name. Naturally the SQL failed because the tables didn't exist in the other DB. But the program just returned the correct false result. Nowhere in the PHP logs or the MySQL logs or anywhere did I get a hint that the table didn't exist where I was looking for it.

So, how are other people doing debugging for PDO SQL calls? What am I missing? :)

Answer 1

For the first problem, checking the query is difficult indeed. There isn't much you can do about this except for logging the queries on your database server. The reason is that prepared queries are not (always) simply concatenating data into the query. The whole point is that the data is sent separately from the query. You can access the query string though via $yourStatement->queryString. It will only show the query with the parameters.

For your second problem, by default PDO doesn't throw an exception when an error occurs. It's up to you to check for them. You can change this though. http://php.net/manual/en/pdo.error-handling.php

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Then, you can catch problems by a try/catch block around your query execution.