Some simple datadase queries

Posted on April 8th, 2011

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.