Asked  7 Months ago    Answers:  5   Viewed   30 times

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?

 Answers

97

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 FilesMySQLMySQL Server 5.1datamysql.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.

Wednesday, March 31, 2021
 
themihai
answered 7 Months ago
95

You need

[PDO_SQLITE]
extension=pdo_sqlite.so

to be enabled, for sqlite:.subscribers.db

or, for windows:

[PHP_PDO_SQLITE]
extension=php_pdo_sqlite.dll

And ofcourse this extension in your ext directory

Wednesday, March 31, 2021
 
PeterTheLobster
answered 7 Months ago
12

PDOStatement::rowCount() returns

... the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Welcome to PDO, where the easy stuff works and the not-so-easy stuff ruins your day. SQLite is one of the drivers that doesn't have a reliable "how many rows are in my result set?" function. From the comments:

As of SQLite 3.x, the SQLite API itself changed and now all queries are implemented using "statements". Because of this, there is no way for PDO to know the rowCount of a SELECT result because the SQLite API itself doesn't offer this ability.

A return of false from PDOStatement::fetch() is a guarantee of "nothing came back," and your checking code is entirely sane, if a bit hard to read. You may wish to consider wrapping or deriving from PDO and PDOStatement for your own sanity.

(Disclaimer: I am a PDO fanboy.)

Wednesday, March 31, 2021
 
etsous
answered 7 Months ago
36

You can give your dynamically loaded script a name so that it shows in the Chrome/Firefox JavaScript debugger. To do this you place a comment at the end of the script:

//# sourceURL=filename.js

This file will then show in the "Sources" tab as filename.js. In my experience you can use 's in the name but I get odd behaviour if using /'s.

For more information see: Breakpoints in Dynamic JavaScript deprecation of //@sourceurl

Saturday, June 12, 2021
 
muaddhib
answered 5 Months ago
67

If you add define('SAVEQUERIES', true) to your configuration file, you can then list all the queries made for the current page by adding the following to your theme.

if (current_user_can('administrator')){
    global $wpdb;
    echo "<pre>";
    print_r($wpdb->queries);
    echo "</pre>";
}

See the documentation for more details: http://codex.wordpress.org/Editing_wp-config.php#Save_queries_for_analysis

Sunday, August 1, 2021
 
bumperbox
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share