How to debug PDO database queries?

Question

You say this :

I never see the final query as it's sent to the database

Well, actually, when using prepared statements, there is no such thing as a "final query" :

• First, a statement is sent to the DB, and prepared there
  • The database parses the query, and builds an internal representation of it
• And, when you bind variables and execute the statement, only the variables are sent to the database
  • And the database "injects" the values into its internal representation of the statement

So, to answer your question :

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

No : as there is no "complete SQL query" anywhere, there is no way to capture it.

The best thing you can do, for debugging purposes, is "re-construct" an "real" SQL query, by injecting the values into the SQL string of the statement.

What I usually do, in this kind of situations, is :

• echo the SQL code that corresponds to the statement, with placeholders
• and use var_dump (or an equivalent) just after, to display the values of the parameters
• This is generally enough to see a possible error, even if you don't have any "real" query that you can execute.

This is not great, when it comes to debugging -- but that's the price of prepared statements and the advantages they bring.

Looking in the database log

Although Pascal MARTIN is correct that PDO doesn't send the complete query to the database all at once, ryeguy's suggestion to use the DB's logging function actually allowed me to see the complete query as assembled and executed by the database.

Here's how: (These instructions are for MySQL on a Windows machine - your mileage may vary)

• In my.ini, under the [mysqld] section, add a log command, like log="C:\Program Files\MySQL\MySQL Server 5.1\data\mysql.log"
• Restart MySQL.
• It will start logging every query in that file.

That file will grow quickly, so be sure to delete it and turn off logging when you're done testing.

Probably what you want to do is use debugDumpParams() on the statement handle. You can run that any time after binding values to the prepared query (no need to execute() the statement).

It doesn't build the prepared statement for you, but it will show your parameters.

Sure you can debug using this mode {{ PDO::ATTR_ERRMODE }} Just add new line before your query then you will show the debug lines.

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$db->query('SELECT *******');  

An old post but perhaps someone will find this useful;

function pdo_sql_debug($sql,$placeholders){
    foreach($placeholders as $k => $v){
        $sql = preg_replace('/:'.$k.'/',"'".$v."'",$sql);
    }
    return $sql;
}