WordPress Database Query Using The WPDB Class

As a plugin developer or WordPress hacker, accessing the database used by a WordPress install is vital.  This can be accomplished through a few different means, but the best is by using the WPDB class that is provided.  The only requirement for using this class is that your code exists within the WordPress install (plugins, themes, etc).

WPDB Queries

Let’s say that you would like to run a simple query that returns all of the rows in the “posts” table.  With the WPDB class, all you need to do is execute:

1
$rows = $wpdb->get_results( "SELECT * FROM $wpdb->posts" );

When this code is executed, it returns the entire table “posts” ($wpdb->posts) as an array of objects into the $rows variable.  From there, it’s easy enough to iterate over the array using a foreach loop.

WPDB Insert

Inserting data into a table is easy using the WPDB class.  All you need to know are the column name(s), the table name, and data you want to store.  I’ll lead with an example:

1
$wpdb->insert( 'links', array( 'link_url' => 're-cycledair.com', 'visit' => 12 ), array( '%s', '%d' ) );

This example of $wpdb->insert, inserts “re-cycledair.com” and “12” into the link_url and visit columns of the “links” table respectively.  The third argument in this function is one that tells the WPDB what type these values are.  The first value is a string, so we use “%s”, and the second is an integer, so we use “%d”.

If you would like to know the auto-incremented id of this insert, simply call:

1
$wpdb->insert_id

WPDB Update

Updating rows in a table is also easy with the WPDB class. Here is an example of an update.

1
$wpdb->update( 'links', array( 'link_url' => 'wordpress.org'), array( 'ID' => 15), array( '%s'), array( '%d' ) )

As you can see, this works a lot like $wpdb->insert. The first argument is the table name. The second argument is an array of column-value pairs. The third argument is the where condition (if ID is equal to 15). The fourth argument tells the WPDB class that you are updating a string, and the fifth argument says the WHERE condition is an integer.

WPDB Prepare: Protect Against SQL Injection

One thing every WordPress developer needs to know about is SQL injection. SQL injection is when someone is able to modify your SQL query to execute their own. To prevent this kind of malicious attack, the WPDB class has a method called “prepare”. “Prepare” will take your input data an sanitize it, so that it cannot be used in a SQL injection attack. An example is as follows:

1
2
3
4
5
$wpdb->query( $wpdb->prepare( "
	INSERT INTO $wpdb->posts
	( post_id, post_content )
	VALUES ( %d, %s)",
        15, "this is un'safe" ) );

As with previous examples, the “%d” and “%s” function as placeholders for the sanitized data.

With those functions and a little bit of work, you should be writing WordPress database queries with the WPDB class in no time!

Author: Jack Slingerland

I'm a software engineer working and living in Raleigh, NC. I work in Python, Django, Node.js, React+Flux, AngularJS, and PHP. I like to work out with Kettlebells, run, and spend my free time with my wife, cat, and dog.

9 thoughts on “WordPress Database Query Using The WPDB Class”

  1. I’m trying to import several posts to a non wordpress website to reflect the latest entries… think of it as a news feed that pulls info from my WP SQL database. Although I haven’t seen much people attempting this, I thought maybe you could help and could go well for clarifying a few things in your post:

    My root folder contains a wordpress file where I installed the blog

    root/wordpress

    and my code is the following

    get_results(“SELECT post_title, post_content FROM {$wpdb->wp_posts} WHERE post_status = ‘publish’ ORDER BY post_date DESC LIMIT 0,5”);

    echo $result;

    foreach ($results as $reprint)
    {

    $title = $reprint->post_title;
    $content = $$reprint->post_content;
    }
    mysql_close();
    ?>

    After the code has been executed i get: Fatal error: Call to a member function on a non-object in … line 22 (I’ve noted line 22 in my code). Any help is appreciated especially anything pertaining classes, how to instance them and verifying that $wpdb is accessible under the above mentioned conditions.

    Thank you 🙂

    1. To use WordPress functions outside of WordPress, you’ll need to include wp-blog-header.php in your script. After that, you should be able to access the global $wpdb variable (or any other WordPress function) if you need it. Hope this helps!

      1. Thanks Jack for the quick reply! Indeed I needed to include the wp-blog-header.php and to my understanding I should also include a few other ones…

        include_once(‘wp-config.php’);
        include_once(‘wp-load.php’);
        include_once(‘/wp-includes/wp-db.php’);
        include_once(‘wp-blog-header.php’);

        and judging from the paths to the functions that these guys call, I should also execute my “‘post_importing.php”‘ from the folder where my wordpress is installed right?

        I’ve also made sure to declare my global variable:

        global $wpdb;

        Well I have done all of the above and moved my “post_importing.php” file to root/wordpress (to account for the include paths) and still get the Fatal error: Call to a member function on a non-object in … line 22

        Btw, my line 22 is:

        $result=get_results(“SELECT post_title, post_content FROM {$wpdb->wp_posts} WHERE post_status = ‘publish’ ORDER BY post_date DESC LIMIT 0,5″);

        Could it be related to initializating the $wpdb varible which is an instannce of the class wpdb… I remember reading it in the codex but don’t really know how to do it. Thanks again for any help.

  2. the site is not seeing very good in look and feel,but your posts is easy to understand…..and also add more abt wordpress functions,query,etc

Leave a Reply

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