WordPress Database Queries, Operations, and Errors

WordPress Database Class

WordPress features an object class with method functions for working with the database directly. This database class is called wpdb and is located in wp-includes/wp-db.php. Any time you are querying the WordPress database in PHP code you should use the wpdb class. The main reason for using this class is to allow WordPress to execute your queries in the safest way possible.

Simple Database Queries

When using the wpdb class, you must first define $wpdb as a global variable before it will be available for use. To do so just drop this line of code directly preceding any $wpdb function call:

global $wpdb;

One of the most important functions in the wpdb class is the prepare function. This function is used for escaping variables passed to your SQL queries. This is a critical step in preventing SQL injection attacks on your web site. All queries should be passed through the prepare function before being executed. Here’s an example:

<?php

$field_key = "address";

$field_value ="123 Elm St";

$wpdb->query( $wpdb->prepare("INSERT INTO $wpdb->my_custom_table

    ( id, field_key, field_value ) VALUES ( %d, %s, %s )",1,

      $field_key, $field_value) );

 

?>

This example adds data into a non-default, custom table in WordPress that you would have previously created. When using prepare, make sure to replace any variables in your query with %s for strings and %d for integers. Then list the variables as parameters for the prepare function in the exact same order. In the preceding example, %d represents 1, %s represents $field_key, and the second %s represents $field_value. The prepare function is used on all queries from here on out.

Notice this example uses $wpdb->my_custom_table to reference the table in WordPress. This translates to wp_my_custom_table if wp_ is the table prefix. This is the proper way to determine the correct table prefix when working with tables in the WordPress database.

The wpdb query method is used to execute a simple query. This function is primarily used for INSERT, UPDATE, and DELETE statements. Despite its name, it’s not only for SQL SELECT queries, but will execute a variety of SQL statements against the database. Here’s a basic query function example:

<?php

$wpdb->query( $wpdb->prepare(" DELETE FROM $wpdb->my_custom_table WHERE id = ‘1’

    AND field_key = ‘address’ " ) );

 

?>

As you can see you execute your query using the wpdb class query function to delete the field “address” with an ID of 1. Although the query function allows you to execute any SQL query on the WordPress database, other database object class functions are more appropriate for SELECT queries . For instance, the get_var function is used for retrieving a single variable from the database:

<?php

$comment_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*)

    FROM $wpdb->comments;"));

echo ‘<p>Total comments: ‘ . $comment_count . ‘</p>’;

?>

This example retrieves a count of all comments in WordPress and displays the total number. Although only one scalar variable is returned, the entire result set of the query is cached. It’s best to try and limit the result set returned from your queries using a WHERE clause to only retrieve the records you actually need. In this example, all comment record rows are returned, even though you display the total count of comments. This would obviously be a big memory hit on larger web sites.

Complex Database Operations

To retrieve an entire table row you’ll want to use the get_row function. The get_row function can return the row data as an object, an associative array, or a numerically indexed array. By default the row is returned as an object, in this case an instance of the per-post data. Here’s an example:

<?php

$thepost = $wpdb->get_row( $wpdb->prepare( "SELECT *

    FROM $wpdb->posts WHERE ID = 1" ) );

echo $thepost->post_title;

?>

This retrieves the entire row data for post ID 1 and displays the post title. The properties of $thepost object are the column names from the table you queried, which is wp_posts in this case. To retrieve the results as an array you can send in an additional parameter to the get_row function:

<?php

$thepost = $wpdb->get_row( $wpdb->prepare( "SELECT *

    FROM $wpdb->posts WHERE ID = 1" ), ARRAY_A );

print_r ($thepost);

?>

By using the ARRAY_A parameter in get_row your post data is returned as an associative array. Alternatively, you could use the ARRAY_N parameter to return your post data in a numerically indexed array.

Standard SELECT queries should use the get_results function for retrieving multiple rows of data from the database. The following function returns the SQL result data as an array:

<?php

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

    FROM $wpdb->posts WHERE post_status = ‘publish’") );

 

foreach ($liveposts as $livepost) {

    echo ‘<p>’ .$livepost->post_title. ‘</p>’;

}

?>

The preceding example is querying all published posts in WordPress and displaying the post titles. The query results are returned and stored as an array in $liveposts, which you can then loop through to display your query values.

The WordPress database class also features specific functions for UPDATE and INSERT statements. These two functions eliminate the need for custom SQL queries, because WordPress will create them for you based on the values passed into the function. Let’s explore how the insert function is structured:

$wpdb->insert( $table, $data );

The $table variable is the name of the table you want to insert a value into. The $data variable is an array of field names and data to be inserted into those field names. So, for example, if you want to insert data into a custom table, you would execute this:

<?php

$newvalueone = ‘Hello World!’;

$newvaluetwo = ‘This is my data’;

$wpdb->insert( $wpdb->my_custom_table, array( ‘field_one’ => $newvalueone,

    ‘field_two’ => $newvaluetwo ) );

 

?>

The first thing you do is set two variables to store the data you want to insert. Next you execute the insert function, passing in both variables through an array. Notice how you set field_one and field_two as the two fields you are inserting. You can pass any field available in the table you are inserting with data to insert into that field.

The update function works very similarly to the insert function, except you also need to set the WHERE clause variable so WordPress knows which records to update:

$wpdb->update( $table, $data, $where );

The $where variable is an array of field names and data for the SQL WHERE clause. This is normally set to the unique ID of the field you are updating, but can also contain other field names from the table.

<?php

$newtitle = ‘My updated post title’;

$newcontent = ‘My new content’;

$my_id = 1;

$wpdb->update( $wpdb->posts, array( ‘post_title’ => $newtitle,

    ‘post_content’ => $newcontent ), array( ‘ID’ => $my_id ) );

?>

First you set your updated title and content variables. You also set a variable $my_id that contains the ID of the post you want to update. Next you execute the update function. Notice that the third parameter you send is an array containing your WHERE clause values, in this case the post ID. The preceding query updates the title and content for post ID 1. Remember, you can send multiple values through the WHERE parameter when updating a table record.

The insert and update functions shown do not need to be wrapped with the prepare function. Both of these functions actually use the prepare function after concatenating the query from the values passed to the functions. This is a much easier method than manually creating your insert and update queries in WordPress.

Dealing With Errors

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:

<?php

$wpdb->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. 

This article is excerpted from chapter 6 "Database Management" of the book "Professional WordPress Design and Development" by Hal Stern, David Damstra, Brad Williams (ISBN: 978-0-470-56054-9, Wrox, 2010, Copyright Wiley Publishing Inc.)

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *