How to debug PDO database queries?

Question 1

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?

Question 2

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.

Question 3

You have to specify fetching mode

$q->fetchAll(PDO::FETCH_ASSOC);

Question 4

When you prepare the $sql_data array, you need to prefix the parameter name with a colon:

array('queryString' => $value);

should be:

array(':queryString' => $value);

In your first SELECT, you have AGINST instead of AGAINST.

Your second SELECT appears to be missing a table name after FROM, and a WHERE clause. The LIKE parameters are also not correctly formatted. It should be something like:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE '%:queryString%' OR aricle_text_".$lang." LIKE '%:queryString%'";

Update 1 >>

For both SELECT statements, you need unique identifiers for each parameter, and the LIKE wildcards should be placed in the value, not the statement. So your second statement should look like this:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE :queryString OR aricle_text_".$lang." LIKE :queryString2";

Note queryString1 and queryString2, without quotes or % wildcards. You then need to update your array too:

$sql_data = array(':queryString1' => "%$value%", ':queryString2' => "%$value%");

See the Parameters section of PDOStatement->execute for details on using multiple parameters with the same value. Because of this, I tend to use question marks as placeholders, instead of named parameters. I find it simpler and neater, but it's a matter of choice. For example:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE ? OR aricle_text_".$lang." LIKE ?";

$sql_data = array("%$value%", "%$value%");

<< End of Update 1

I'm not sure what the second SELECT is for, as I would have thought that if the first SELECT didn't find the query value, the second wouldn't find it either. But I've not done much with MySQL full text searches, so I might be missing something.

Anyway, you really need to check the SQL, and any errors, carefully. You can get error information by printing the results of PDOStatement->errorCode:

$sth->execute($sql_data);
$arr = $sth->errorInfo();
print_r($arr);

Update 2 >>

Another point worth mentioning: make sure that when you interpolate variables into your SQL statement, that you only use trusted data. That is, don't allow user supplied data to be used for table or column names. It's great that you are using prepared statements, but these only protect parameters, not SQL keywords, table names and column names. So:

"SELECT * FROM ".$db_prefix."_base"

...is using a variable as part of the table name. Make very sure that this variable contains trusted data. If it comes from user input, check it against a whitelist first.

<< End of Update 1

You should read the MySQL Full-Text Search Functions, and the String Comparison Functions. You need to learn how to construct basic SQL statements, or else writing even a simple search engine will prove extremely difficult.

There are plenty of PDO examples on the PHP site too. You could start with the documentation for PDOStatement->execute, which contains some examples of how to use the function.

If you have access to the MySQL CLI, or even PHPMyAdmin, you can try out your SQL without all the PHP confusing things. If you are going to be doing any database development work as part of your PHP application, you will find being able to test SQL independently of PHP a great help.

Question 5

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

Question 6

Regardless of which database you are using, it can be more efficient to put multiple queries into one statement. If you perform the queries separately, you have to make a call to the database across the network (or at least, between processes if on the same machine), get a connection to it (including autheticating), pass in the query, return the resultset, and release the connection for each query.

Even if you use connection pooling, you are still passing more requests back and forth than is necessary.

So, for example, if you combine two queries into one, you have saved all of that extra calling back and forth for the second query. The more queries you combine, then, the more efficient your app can become.

For another example, many apps populate lists (such as for dropdownlists) when they start up. That can be a number of queries. Performing them all in one call can accelerate startup time.

How to debug PDO database queries?

Answers

Looking in the database log

php,sql,pdo