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):

1. $wpdb->query('query'); - Runs any query, used for inserts, updates, deletes etc.
2. $wpdb->get_var('query',column_offset,row_offset); - Get a single variable
3. $wpdb->get_row('query', output_type, row_offset); - Get a single row
4. $wpdb->get_col('query',column_offset); - Get a single column as a dimensional array
5. $wpdb->get_results('query', output_type); - Get generic results

The returned results’ type can be changed by setting ‘output_type’, this can be:

1. OBJECT - This is the default output, and returns an object.
2. ARRAY_A - This returns the results as an associative array.
3. 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:

1. $wpdb->posts - The post table that stores posts and pages
2. $wpdb->postmeta - The table which stores custom field data
3. $wpdb->categories - The category table
4. $wpdb->post2cat - The post2cat table which stores the id’s of posts and categories that are linked
5. $wpdb->comments - The comments table
6. $wpdb->users - The table of users and user details
7. $wpdb->links - Stores links
8. $wpdb->link2cat - The link2cat table which stores the id’s of links and categories that are linked
9. $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.

1. $wpdb->show_errors(); - Echoes mysql errors on the page
2. $wpdb->hide_errors(); - Hides mysql errors on the page
3. $wpdb->print_error(); - Prints mysql errors on the page
4. $wpdb->flush(); - Flushes the result cache

Useful variables

After running a query, some useful variables become available:

1. $wpdb->num_rows - Count of the number of rows returned by the last query (useful in SELECT queries)
2. $wpdb->insert_id — ID of the AUTO_INCREMENT value of the last query (useful in INSERT queries)
3. $wpdb->rows_affected - Count of the number of rows affected by the last query (useful in INSERT, UPDATE and DELETE queries)
4. $wpdb->last_query - The last query ran
5. $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 &raquo;'); ?>
				</div>
 
				<p class="postmetadata">Posted in <?php the_category(', ') ?> | <?php edit_post_link('Edit', '', ' | '); ?>  <?php comments_popup_link('No Comments &#187;', '1 Comment &#187;', '% Comments &#187;'); ?></p>
			</div>
 
		<?php endwhile; ?>
 
		<div class="navigation">
			<div class="alignleft"><?php next_posts_link('&laquo; Previous Entries') ?></div>
			<div class="alignright"><?php previous_posts_link('Next Entries &raquo;') ?></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!

Related Entries

• Wordpress development techniques #2 - Fetching RSS feeds with wordpress
• Create a Wordpress Recent-Posts Widget
• Dont do what Anthony Dont Does
• Wordpress Sidebar Login Widget Update
• Wordpress Download Monitor Plugin v1.6

Popular Entries

• Anti-aliased Rounded corners with JQuery
• JQuery Curvy Corners Update (v1.1.1)
• The great wordpress plugin update extravaganza of the year
• Book Review - Learning jQuery
• Back with a New look