Dealing With DataBase Errors

Posted on April 8th, 2011

Any time you are working with queries it’s nice to see error messages. By default, if a custom query fails nothing is returned, so it’s hard to determine what is wrong with your query. The wpdb class provides functions for displaying MySQL errors to the page. Here’s an example of using these functions:

show_errors();

$liveposts = $wpdb->get_results( $wpdb->prepare(“SELECT ID, post_title

FROM $wpdb->posts_FAKE WHERE post_status = ‘publish’”) );

$wpdb->print_error();

?>

The show_errors function must be called directly before you execute a query. The print_error function must be called directly after you execute a query. If there are any errors in your SQL statement the error messages are displayed. You can also call the $wpdb->hide_errors() function to hide all MySQL errors, or call the $wpdb->flush() function to delete the cached query results.

The database class contains additional variables that store information about WordPress queries. Following is a list of some of the more common variables:

print_r($wpdb->num_queries); // total number of queries ran

print_r($wpdb->num_rows ); // total number of rows returned by the last query

print_r($wpdb->last_result ); // most recent query results

print_r($wpdb->last_query ); // most recent query executed

print_r($wpdb->col_info ); // column information for the most recent query

Another very powerful database variable is the $queries variable. This stores all of the queries run by WordPress. To enable this variable you must first set the constant value SAVEQUERIES to TRUE in your wp-config.php file. This tells WordPress to store all of the queries executed on each page load in the $queries variable. First drop this line of code in your wp-config.php file:

define(‘SAVEQUERIES’, true);

Now all queries will be stored in the $queries variable. You can display all of the query information like so:

print_r($wpdb->queries); // stores all queries executed during page load

This is especially handy when troubleshooting slow load times. If a plugin is executing an obscene number of queries, that can dramatically slow down load times in WordPress. Remember to disable the SAVEQUERIES constant option when you are finished viewing queries because storing all queries can also slow down load times.

The database query class is a major asset when working with the WordPress database directly, as you may be when developing a plugin or building a more complex Loop. All of the previously mentioned database class functions use specific escaping techniques to verify that your queries are executed in the safest manner possible. To borrow from Randall Munroe’s xkcd joke, you don’t want a user hand-crafting an input item that contains DROP TABLES as a malicious SQL injection, resulting in the loss of your WordPress database tables. The query preparation and escaping functions ensure that inputs don’t become SQL functions, no matter how craftily they’re set up. It is essential that you follow these methods for querying data to ensure your web site is the most efficient and uses the safest techniques possible.