Subscribe
WordPress trainings, support, consulting and help

The ultimate guide to working with the WordPress database

WordPress provides the wpdb class of functions for all database manipulations. Methods from this class shouldn’t be called directly because WordPress provides global variable called $wpdb representing a usable instance.

Note: always use the variable $wpdb and remember to globalize it before using it at any custom functions in your Themes or Plugins.

Here is an example of using $wpdb for my_custom_table:


$result = $wpdb->get_results( "SELECT id, name from my_custom_table" );

The wpdb class could be used to manipulate either default or custom tables from your WordPress database. Querying the standard tables usually could be done with other functions, such as:

  • get_posts, query_posts or a custom WP_Query instance for posts
  • get_post_meta, add_post meta for postmeta data
  • get_option/add_option for the options table

And other handy functions. However, specific queries with joins or very comprehensive conditions could be built only with wpdb.

More information about $wpdb you can find in WordPress Codex.

The example

We will create simple example of creating a simple post rating system

For the goal we will need a our custom database table wp_posts_rating with 3 fields: id, post_id and rating.

Setting the stage

We will create our new database table into the WordPress database. We could do it manually with the mysql console tool, MySQL Workbench or phpMyAdmin (or any other tool that allows for a direct manipulation). However, if a plugin of ours relies on that table, this snippet would create it automatically:


global $wpdb;
$table_name = $wpdb->prefix . "posts_rating ";
$sql = "CREATE TABLE $table_name (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
post_id INT UNSIGNED,
rating SMALLINT,
PRIMARY KEY pk_wp_post_rating ( id ),
UNIQUE KEY uk_wp_post_rating_post_id ( post_id )
);
";

$wpdb->query( $sql );

Now in our database we could see our new database table.

Now we have insert ratings for some of the post and we have table with 5 rows.

Next step

Now we can create a simple function in out theme’s functions.php that can display in ascending order all rated posts.

For the sake of our example we would be using a basic WordPress installation and display our content based on the Twenty Eleven theme.

Now go to Appearance => Editor and from Twenty Eleven files choose Theme Functions(functions.php).

At the bottom of functions.php we will add our custom function display_all_rated_post.


// Function that display all rated posts
function display_all_rated_post() {

global $wpdb;

$result = $wpdb->get_results( ”
SELECT id, post_id, rating
FROM wp_post_rating
ORDER BY rating DESC”
);

echo ‘
<table border=”1″>
<tr>
<th>ID</th>
<th>Post ID</th>
<th>Rating</th>
</tr>
‘;

foreach ($result as $row) {
echo ‘<tr>’;
echo ‘<td>’ . $row->id . ‘</td>’;
echo ‘<td>’ . $row->post_id . ‘</td>’;
echo ‘<td>’ . $row->rating . ‘</td>’;
echo ‘</tr>’;
}

echo ‘</table>’;
}

This function will get all rated posts from wp_post_rating table.

We could also create a shortcode that display result from function.

Bellow function code we will add this:

// Shortcode that will display result from the display_all_rated_post() function
add_shortcode(‘rated_posts’, ‘display_all_rated_post’);

We should have this code in out functions.php:

Add shortcode to the page

We will create a simple page from Pages => Add New

and will put [rated_posts] in page WYSIWYG Page editor and will Publish the page.

The result

After publishing our page we should have this:

Conclusion

Leave a Reply