How to debug PDO database queries?

by

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.

More Related Contents:

  1. Cleansing User Passwords
  2. How to apply bindValue method in LIMIT clause?
  3. How to properly set up a PDO connection
  4. Getting raw SQL query string from PDO prepared statements
  5. PDO with INSERT INTO through prepared statements [closed]
  6. Use bound parameter multiple times
  7. How to replace MySQL functions with PDO?
  8. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  9. Do SQL connections opened with PDO in PHP have to be closed
  10. Using value of a column as index in results using PDO
  11. I’m getting this error code: Invalid argument supplied for foreach() [duplicate]
  12. PDO and MySQL ‘between’
  13. PDO Return All Rows [duplicate]
  14. PDO Exception Questions – How to Catch Them
  15. Order results from two queries based on time in second query [closed]
  16. Save the data into a database and return another value from database using PHP
  17. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  18. PDO’s query vs execute
  19. Resetting array pointer in PDO results
  20. PDO::PARAM for type decimal?
  21. How to print SQL statement in codeigniter model
  22. How to execute raw queries with Laravel 5.1?
  23. How to populate HTML dropdown list with values from database
  24. Retrieve (or simulate) full query from PDO prepared statement
  25. PDO returning incorrect, but duplicate, data. Key’s not in database.
  26. Set PDO to throw exceptions by default [duplicate]
  27. Fatal error: Call to a member function bindParam() [duplicate]
  28. How to escape single-quote (apostrophe) in string using php
  29. Insert value list does not match column list: 1136 Column count doesn’t match value count
  30. PHP Validating the File Upload [duplicate]

Leave a Comment