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.

  • Share/Bookmark