WordPress Database Functions

October 12th, 2009 by Frank • WordPress Tutorials • 7 Comments

The WordPress database class is quite extensive and provides a range of methods to work effectively with the database and thereby use the WordPress standard.

You can find the class in /wp-includes/wp-db.php where the individual methods are documented.
I show the most important ones and give some small examples. It is important to work with these opportunities to ensure the safety of Plugins.


For the following four methods I created some syntax examples.

  • 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.
  • query($query) — perform a MySQL database query with current connection
  • get_results($query, $output) — retrieve SQL result set from database… one or more rows.
  • escape($data) — Escapes content for insertion into the database using addslashes(), for security

Also interesting are the methods below.

  • 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.
  • 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.
/**
 * insert
 */
$wpdb->insert( $wpdb->posts, array( 'post_title' => $mytitle ) );
 
$wpdb->insert( $wpdb->options, array(
            'option_name',
            'new_option_key',
            'option_value' => 'New Option Value',
            'autoload' => 'yes' )
            );
 
/**
 * update
 */
$wpdb->update( $wpdb->posts, array( 'post_title' => $mytitle ),
            array( 'ID' => $myid )
            );
 
$wpdb->update( $wpdb->options,
            array( 'option_value' => 'New Option Value' ),
            array( 'option_name' => 'new_option_value' )
            );
 
/**
 * get_var
 */
$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 . '&' )
            );
 
$content = $wpdb->get_var(
            $wpdb->prepare("SELECT post_content FROM " .
                    "$wpdb->posts WHERE " .
                    "post_title = %s AND " .
                    "ID = %d", $title, $id )
        );
 
/**
 * query
 */
$wpdb->query( "DELETE FROM $wpdb->options WHERE option_name = '$name'" );
 
$wpdb->query( "UPDATE $wpdb->posts SET post_title = '$mytitle' WHERE ID = $myid" );
 
/**
 * query and escape
 */
$mytitle = $wpdb->escape( $mytitle );
$myid    = absint( $myid );
$wpdb->query( "UPDATE $wpdb->posts SET post_title = '$mytitle' WHERE ID = $myid" );
 
/**
 * get_results
 */
$type = $wpdb->get_results( "SELECT post_type FROM " .
                "$wpdb->posts WHERE ID=$id" );

6 Comments
  1. FreewareMatter says:
    October 20, 2009 at 2:07 pm

    Very good post. I've searched documents about $wpdb class, but there's no "full" document. This post helps me very much. Thank you.

  2. Ben says:
    October 23, 2009 at 10:35 pm

    I second that, this is the best resource with examples that I could find online. More useful than the wordpress codex page. thanks!

  3. Dominic says:
    October 26, 2009 at 11:55 pm

    Thanks for posting this. I've been hand-coding all of my plugin SQL.

  4. Simon says:
    December 8, 2009 at 9:53 am

    Googling $wpdb brings up few resources relating to this rather useful set of wordpress functions. While the wordpress codex does provide some extensive coverage it isn't laid out as simple as this for those out there who may be new to php or those who need a quick and simple reference list. Nice one.

  5. Tsalagi says:
    January 22, 2010 at 11:14 pm

    Thanks for providing these functions. I am very interested in using these types of functions to add more functionality to my site. Where can I learn more information about how to put together such functions and where to use the syntax. For instance your use of get_var at the very beginning has an x and a x but it says get a single variable. What is the x and y for? And where can I learn about your use of absint,, prepare, escape and SET? Are these mysql query techniques or WordPress stuff? I sure would like to find a full fledged tutorial breaking all this stuff down so I can actually learn something other than how to copy and paste.

  6. dameer says:
    February 4, 2010 at 2:45 pm

    Just wanted to share something...
    Let's suppose you've created brand new table for the purpose of a plugin and want to insert some data. The following will not work:

    $wpdb->insert( $wpdb->my_new_table, array( 'post_title' => $mytitle ) );

    ...you have to do it this way:

    $wpdb->insert( $wpdb->prefix . 'my_new_table', array( 'post_title' => $mytitle ) );

    Hopefully people will find it useful.

1 Ping
  1. WordPress’s WPDB Class
Leave a Reply
WP Engineer Tags