How to debug PDO database queries?
Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code.
Prepared PDO statements are faster and better and safer, but one thing bothers me: I never see the final query as it's sent to the database. When I get errors about the syntax in my Apache log or my custom log file (I log errors inside a catch block), I can't see the query that caused them.
Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?
Answer
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.