The WordPress $wpdb class explained

The WordPress MySQL database class is quite powerful.  This database class can be used for other projects with some very simple modifications, and is actually based on/borrowed from another project called ezSQL by Justin Vincent.

The WordPress database class code is located in <WordPress root>/wp-includes/wp-db.php.  The $wpdb object is created at the very bottom using the following lines of code (comments removed):

if ( ! isset($wpdb) ) {
	$wpdb = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
}

Below, I listed a few of the main functions in the $wpdb object.  These functions will help make your job easier when creating plugins and extending the WordPress core, or even working on a completely different project.  I did not include functions used for debugging/error reporting, as that could easily be its own lesson.

  • set_prefix($prefix) — used to set table prefix for WordPress tables, can be used to override prefix at any time.
  • prepare($query) — safely prepares an SQL query for execution with sprintf()-like syntax.
  • query($query) — perform a MySQL database query with current connection
  • insert($table, $data, $format) — insert a row into a table via arrays.
  • update($table, $data, $where, $format, $where_format) — update a row in a table via arrays.
  • get_var($query, $x, $y) — retrieve a single variable from the database.
  • get_row($query, $output, $y) — retrieve a single row from the database.
  • get_col($query, $x) — retrieve a single column from the database in array format.
  • get_results($query, $output) — retrieve SQL result set from database… one or more rows.
/**
 * Insert a new option into the $wpdb->options table.
 * @uses $wpdb WordPress Database Object
 */
$wpdb->insert( $wpdb->options, array('option_name', 'new_option_key', 'option_value' => 'New Option Value', 'autoload' => 'yes') );

/**
 * Update an existing option in the $wpdb->options table.
 * @uses $wpdb
 */
$wpdb->update( $wpdb->options, array('option_value' => 'Newer Option Value'), array('option_name' => 'new_option_value') );

/**
 * Get a single post_id from the $wpdb->postmeta database.
 *
 * The $wpdb->prepare() function ensures that the query is
 * safe for use in the database.  The %d refers to an integer
 * and is replaced by $post_ID in a way that makes it query-safe.
 * The %s refers to a string and is also escaped.
 *
 * @uses $wpdb
 */
$post_id = $wpdb->get_var( $wpdb->prepare("SELECT post_id FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = 'enclosure' AND meta_value LIKE (%s)", $post_ID, $url . '&') );

/**
 * Delete a record from the $wpdb->options database.
 * @uses $wpdb
 */
$wpdb->query("DELETE FROM $wpdb->options WHERE option_name = '$name'");

Feel free to leave comments and ideas on what could make this tutorial better or clearer.

4 thoughts on “The WordPress $wpdb class explained”

  1. Excellent explanation. I’ve been scouring the web for the last week looking for this information. I’m looking for a way to access a table already in the wordpress database (families) using wpdb. I want to list the families and then click on the family name and go to a details page with that family’s information. Can you suggest which options variables based on my project I’d replace in the code? Thanks so much!

  2. Hi Great work and good explanation. I am new to wordpress and php. i am in the process of developing a standard website using wordpress(not a blog, obviously). My aim is to display mysql table data on a new page. i have gone through the docs on usage of wpdb class. but i cannot figure out the file to write the query using wpdb. i mean in which php file should i write the wpdb functions and how to show it on a wordpress page? It would be a great help if you could explain me in detail on how to use wpdb functions to retrieve whole data from a mysql table and display it on a wordpress page. Thanks in advance

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>