WordPress Database Functions

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" );


Posted

in

by

Comments

13 responses to “WordPress Database Functions”

  1. […] can find a nice introduction and examples at WP Engineer. Here’s a sample of how you would go about changing the title of a post with a certain […]

  2. FreewareMatter Avatar

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

  3. Ben Avatar

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

  4. Dominic Avatar

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

  5. Simon Avatar

    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.

  6. Tsalagi Avatar
    Tsalagi

    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.

  7. dameer Avatar

    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.

  8. sebastien Avatar
    sebastien

    I insert a post with this function
    $my_post_name = "test3";

    // check if title already exists in wpdb
    global $wpdb;

    if($wpdb->get_row("SELECT post_title FROM wp_posts WHERE post_title = '" . $my_post_name . "'", 'ARRAY_A')) {
    echo $my_post_name.' | already exists';}
    else
    {
    echo 'generating new post: '. $my_post_name.' ;';
    // Create post object - in this case just containing one image
    $my_post = array();
    $my_post['post_title'] = $my_post_name;
    $my_post['post_content'] = '';
    $my_post['post_status'] = 'draft'; // or 'publish'
    $my_post['post_author'] = 1;
    $my_post['post_category'] = array(228);

    // Insert the post into the database
    wp_insert_post( $my_post );
    sleep(1); //wait 1 second between posts
    }

    but my post is insert one time, and two time, nd three time …
    what can i do ?

    i suppose that this line is not avalable :
    "SELECT post_title FROM wp_posts WHERE post_title = '" . $my_post_name . "'", 'ARRAY_A')

    what do you think about this ?

    thx

  9. dameer Avatar

    I guess your dB query is not properly written coz it always returns false…
    Try this:

    if( $wpdb->get_row( "SELECT post_title FROM $wpdb->posts WHERE post_title = $my_post_name" ) ) { ... }

  10. sebastien Avatar
    sebastien

    thx dameer ! but that doesn’t work…

  11. sebastien Avatar
    sebastien

    dameer : in fact it ws a problem of syntax. It works if the syntax is :

    if( $wpdb->get_row(“SELECT post_title FROM $wpdb->posts WHERE post_title = ‘$my_post_name’;” ) ) {

  12. Travis Smith Avatar
    Travis Smith

    Hello,

    I have WP 3.0 with MS enabled and I am pull the sites information from my subdomains via $wpdb.

    global $wpdb;
    $blogs = $wpdb->get_results( $wpdb->prepare("SELECT blog_id, domain, path FROM $wpdb->blogs WHERE site_id = %d AND public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0' ORDER BY registered DESC", $wpdb->siteid), ARRAY_A );

    This gives me an array, e.g. Array ( [0] => Array ( [blog_id] => 10 [domain] => sample1.smithsaga.com [path] => / ) [1] ...)

    So, I want to add a key [logo] and store it permanently.

    So later in my foreach($blogs as $blog) where I have an input form for $blog[‘img’], and then I have $wpdb->insert( $wpdb->blogs, array( 'img' => $blog['img'] ), array( 'blog_id' => $blog['blog_id'] ) ); but I don’t think this is right. I have also tried $wpdb->update. So what am I missing or doing wrong? For the full snipit, see http://pastebin.com/U61ME2AU

  13. wetwetwafu Avatar
    wetwetwafu

    How can we add a database field where the search query will fetch the search result?

    THanks a lot for sharing this.