Wordpress development techniques #1 – Running custom queries using the ‘wpdb’ class
In this first edition of “Wordpress development techniques” I will be talking about one of the most useful classes available for use in wordpress: wpdb.
The wpdb class (WordPress DataBase class) is based on the ezSQL class, and handles database connections and queries.
Why use it? You can make all sorts of custom queries, and pull information from the wordpress database, including posts! Read on for more information on this useful class…
What can I use wpdb for?
The wpdb class can be used for running queries on the wordpress database, giving you access to ALL wordpress tables, including posts and categories.
An example use, and the example we will focus on in this article, is pulling recent posts from the database, for example, on a homepage.
Using wpdb
Where can wpdb be used?
Wpdb can be used in templates and plugins within wordpress, it can also be used in web pages that are not part of wordpress if you include the following files:
1 2 | include_once('wp-config.php'); include_once('wp-includes/wp-db.php'); |
If your using the code from a plugin or template file, these files are already included.
Getting started with wpdb
When using wpdb, it is a good idea to first set its scope to global:
1 | global $wpdb; |
This will allow you to use the wpdb class in your code. Wpdb automatically handles the database connection, so you can immediately start running queries.
Running query’s
To run a query you can call one of the following functions within wpdb (replacing ‘query’ with your sql code):
$wpdb->query('query');– Runs any query, used for inserts, updates, deletes etc.$wpdb->get_var('query',column_offset,row_offset);- Get a single variable$wpdb->get_row('query', output_type, row_offset);– Get a single row$wpdb->get_col('query',column_offset);– Get a single column as a dimensional array$wpdb->get_results('query', output_type);– Get generic results
The returned results’ type can be changed by setting ‘output_type’, this can be:
OBJECT– This is the default output, and returns an object.ARRAY_A– This returns the results as an associative array.ARRAY_N– This returns the results as a numbered array.
I tend to only use the ‘object’ type, this is down to personal preference.
Get table names easily
wpdb provides an easy method to get wordpress table names, by using the following variables within queries:
$wpdb->posts– The post table that stores posts and pages$wpdb->postmeta– The table which stores custom field data$wpdb->categories– The category table$wpdb->post2cat– The post2cat table which stores the id’s of posts and categories that are linked$wpdb->comments– The comments table$wpdb->users– The table of users and user details$wpdb->links– Stores links$wpdb->link2cat– The link2cat table which stores the id’s of links and categories that are linked$wpdb->options– The table of wordpress options
Keeping data safe for insertion
When inserting data into the database, it shoukd always be escaped to stop people hacking your site, wpdb offers a handy little function for doing this – $wpdb->escape
1 | $safe_string = $wpdb->escape($unsafe_string); |
The above code runs the escape function on an unsafe string, and puts the result into the $safe_string varible. This can then be inserted into the database using wpdb safely.
Other useful functions
Some other useful functions wpdb has are listed below.
$wpdb->show_errors();– Echoes mysql errors on the page$wpdb->hide_errors();– Hides mysql errors on the page$wpdb->print_error();– Prints mysql errors on the page$wpdb->flush();– Flushes the result cache
Useful variables
After running a query, some useful variables become available:
-
$wpdb->num_rows– Count of the number of rows returned by the last query (useful in SELECT queries) $wpdb->insert_id— ID of the AUTO_INCREMENT value of the last query (useful in INSERT queries)$wpdb->rows_affected– Count of the number of rows affected by the last query (useful in INSERT, UPDATE and DELETE queries)$wpdb->last_query– The last query ran$wpdb->last_results– The results of the last query ran
Recent posts example
So I’ve covered the basics, now lets put wpdb into action. Lets say we have a homepage on a wordpress blog, the template for this page is home.php.
On this page we want to show the latest post, plus a list of recent posts (like the setup here at Blue Anvil).
The normal loop
To show the latest entry, just use the normal wordpress loop, as well as query_posts to limit it to one post.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <?php query_posts('showposts=1'); // Return only 1 post global $more; $more = 0; // set $more to 0 in order to only get the first part of the post ?> <?php if (have_posts()) : ?> <?php while (have_posts()) : the_post(); ?> <div class="post" id="post-<?php the_ID(); ?>"> <h2><a href="<?php the_permalink(); ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>"><?php the_title(); ?></a></h2> <small><?php the_time('F jS, Y') ?> <!-- by <?php the_author() ?> --></small> <div class="entry"> <?php the_content('Read the rest of this entry »'); ?> </div> <p class="postmetadata">Posted in <?php the_category(', ') ?> | <?php edit_post_link('Edit', '', ' | '); ?> <?php comments_popup_link('No Comments »', '1 Comment »', '% Comments »'); ?></p> </div> <?php endwhile; ?> <div class="navigation"> <div class="alignleft"><?php next_posts_link('« Previous Entries') ?></div> <div class="alignright"><?php previous_posts_link('Next Entries »') ?></div> </div> <?php else : ?> <h2 class="center">Not Found</h2> <p class="center">Sorry, but you are looking for something that isn't here.</p> <?php include (TEMPLATEPATH . "/searchform.php"); ?> <?php endif; ?> |
The recent posts
Now lets use wpdb to get recent posts!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | <h2>Recently posted</h2>
<ul id="recent">
<?php
global $wpdb; // Give wpdb with global scope
// This query selects all posts that are published, orders them by date, offsets the first post by 1 (so we dont show the main post in the list) and limits it to 3 (so we only show 3 recent posts)
$sql = 'SELECT DISTINCT * FROM '.$wpdb->posts.' AS POSTS
WHERE POSTS.post_type="post"
AND POSTS.post_status="publish"
ORDER BY POSTS.post_date DESC
LIMIT 1,3;';
$posts = $wpdb->get_results($sql); // Run our query, getting results as an object
if (!empty($posts)) { // If the query returned something
foreach ($posts as $post) { // Loop though our results!
$post->post_date = date("F j, Y",strtotime($post->post_date)); // Format the date
// Get excerpt, or make our own from content
if (empty($post->post_excerpt)) {
$post->post_excerpt = explode(" ",strrev(substr(strip_tags($post->post_content), 0, 175)),2);
$post->post_excerpt = strrev($post->post_excerpt[1]);
$post->post_excerpt.= " [..]";
}
// Output the row
print(
'<li><a href="'.get_permalink($post->ID).'">
<strong>'.$post->post_title.' - '.$post->post_date.'</strong><br/>'.$post->post_excerpt.'</a></li>'
);
}
}
?>
</ul> |
You will notice in the above example, I got the post data by using $post->, there is another way of doing this which enables you to use the normal wordpress template functions (e.g. the_content();). To allow this, add the following code within the foreach loop:
1 | setup_postdata($post); |
You can now use functions like the_content();, the_excerpt(); and so on instead of $post->post_content etc.
More information on wpdb
- http://codex.wordpress.org/Function_Reference/wpdb_Class
- http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query
Next week…
Next week we will be looking at wordpress’ built in RSS feed grabbing features, and create our own delicious RSS feed aggregator. See you then!
Found this post useful? Why not buy me a coffee!
Anthony Brewitt says:
I will definetly be using this as a resource – thanks Mike!
Comment made on June 22, 2007 at 12:34 pm
Mike Cherim says:
That’s excellent Mike. Thanks. I can see lots of uses for that. For recent post in particular, I like that and may implement it on a site I’m making. Currently I have this…
<?php wp_get_archives('type=postbypost&limit=4'); ?>…which, with the Run PHP plugin (which is a must have as far as I’m concerned) I can grab listed links to the posts
postbypost. Not sure if there’s a description or excerpt parameter to add to it do grab some post content as well.The number in the string is the number displayed.
Comment made on June 23, 2007 at 12:47 am
James Oppenheim says:
Nice article, can’t wait for the next one. Keep up the good work.
Comment made on June 23, 2007 at 4:35 am
Mike Jolley says:
@Mike – I take it you put that inside actual wordpress pages? I prefer making a template for a page, then the user can choose that as the template when they create it.
@ant and @James – Thanks. Ill get the next one out next week
Comment made on June 23, 2007 at 8:23 am
Mike Cherim says:
Yes, that’s what I did in this case, Mike. It is embedded directly in a page.
Comment made on June 23, 2007 at 2:12 pm
Joseph says:
Another great post – thanks! Would it be possible to use this technique in conjunction with your secure and accessible contact form to pull an email address stored in a post’s custom field and therefore have the contents of the contact form sent to an email address depending on the post ID. I think something like this is being used here:
http://geofeat.com/directory/air_water/water_home/water_filter_systems/40616/
Do you think this would work?
Cheers, Joe
Comment made on September 26, 2007 at 9:18 am
Andy Bailey says:
great post, I have been toying with using the wp database for reads and writes since I started making plugins for wordpress. So far I haven’t needed to use the database but I think my future plans for plugins will definitely use it so thanks for making it so clear on how to use wp-db.php!
Comment made on November 7, 2007 at 11:57 am
James Gardner says:
Hey,
Excellent article, just what I was looking for! Perhaps you could help me with something? I have created my own table and want to create an admin panel which lists it’s contents – can I facilitate something within wpdb to organize my results into pages automatically?
Comment made on December 2, 2007 at 2:00 pm