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.

  • Pingback: Tweets that mention The WordPress $wpdb class explained « webjawns.com -- Topsy.com

  • http://www.leftfieldwebdesign.com Ed F

    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!

  • Akhilesh

    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

  • http://reiffs.dk Casper Reiff

    Really helped me out in my plug-in development. Damn that SQL is hardcore…
    Thank you!