150 WordPress Database

Part of the “How to Write a WordPress Plugin” Series

This Lesson Objectives

In previous lessons we introduced plugins, built a basic working plugin template file structure and created a plugin class for our plugin.
Every WordPress based web site on the Internet today is connected to a MySQL database backend that stores information about that web site. This database contains all of the data required for your web site, including its content, users, links, metadata, settings, and everything else. This lesson covers how and what data is stored in the database, how to work with that data and how to use WordPress to help you build amazing web sites using that data.

Series Contents

Database Schema

The default installation of WordPress contains 11 core database tables. The database structure is designed to be very minimal yet allow for endless flexibility when developing and designing for WordPress. To start your understanding the WordPress database schema begin by viewing the database diagram.

• Note: Installed plugins and themes have the ability to create custom tables so your WordPress database may contain more tables than just the default WordPress tables.
• Important: Within the standard installation of WordPress no integrity between the tables is enforced. If you are creating a plugin or extension that manipulates the WordPress database, your code must do the housekeeping so that no orphan records remain in the tables.

Database Table Structure

I’m not going to give a design tutorial on MySQL databases just this quick overview, so if you need help designing your database you will need to look elsewhere (sorry).

You’ll be glad to know the WordPress database table structure is very consistent. Each table contains a unique ID field, the auto-incrementing primary key field. Each table also contains one or more indexes on fields, which improves the speed of data retrieval when executing queries against the data.

Table Details

Currently, 11 database tables have been created for WordPress. The following is a list of those tables with brief details on data stored.

wp_comments	Contains all comments within WordPress. Individual comments are linked back to posts through a post ID.
wp_commentsmeta	Contains all meta data for comments.
wp_links	Contains all links added via the Link Manager section.
wp_options	Stores all web site options set under the Administration > Settings panel. Also stores plugin options, active plugins and themes. This is a general storage area.
wp_postmeta	Contains all post metadata (custom fields). Some plugins may add their own information to this table.
wp_posts	The core of WordPress. Contains all posts, pages, navigation menu items, media records, attachment pointers and revisions. This is usually the largest table in the database.Note: The attachments are physically stored on your hosting server as a standard file.
wp_term_relationships	Joins taxonomy terms with content (that is, posts, links, and so on). Posts are associated with categories and tags from the wp_terms table and this association is maintained in the wp_term_relationships table.
wp_term_taxonomy	Defines what taxonomy each term is assigned (category, link, or tag) for the entries in the wp_terms table.
wp_terms	Contains all taxonomy terms defined for your web site. The categories for both posts and links and the tags for posts.
wp_usermeta	Contains metadata for users (first name, last name, nickname, user level, and so on).
wp_users	Contains a list of your web stie users (login, password, e-mail).

WordPress MySQL database tables. For more detailed information see the WordPress codex, [http://codex.wordpress.org/Database_Description]

As you can see, WordPress has arranged the data so each table has a specific purpose. To retrieve all the web site content, for example, you would query the wp_posts table. The following SQL query is an example of how to extract all of your posts from the database, this is what happens in the default WordPress Loop:

SELECT * FROM wp_posts
WHERE post_type = ‘post'
ORDER BY post_date DESC

This query selects all records from wp_posts with a post_type of ‘post’ and sorted in descending order by post_date (reverse chronological order).

• If you wanted to view pages, just change the post_type field to ‘page’.

WordPress Plugins and Database Interaction

When you are writing your plugins, you will inevitably need to store and retrieve data. Most useful WordPress Plugins need to obtain data from users and save it between sessions, for use in filter, action and template functions.

This information has to be saved in the WordPress database, in order to be persistent between sessions. Fortunately for us, WordPress makes data storage and retrieval a relatively simple task with Options and Database Objects.

This rest of this lesson will cover storing and retrieving data from a WordPress database.

Storing Data in a Database

For our purposes, there are two recommended ways to store data in the WordPress database:

• Use the WordPress “Option” mechanism.
  This method is appropriate for storing small amounts of relatively static, named pieces of data.
• Create your own database table.
  This method is appropriate for data not associated with individual posts, pages, attachments, or comments, the type of data that will grow as time goes on.

Unless your plugin requires an extensive database or you’re storing lots of records or variables, most plugins will not require their own database table and Options will suffice.

See the WordPress codex for detailed instructions on database tables.

WordPress Options

With WordPress options, saving and retrieving data from the database can be as simple as a function call. WordPress has four functions for options:

1. add_option()
2. update_option()
3. get_option()
4. delete_option()

add_option()

Creates a new option, i.e. adds data to the database. Does nothing if option already exists. The function accepts four variables, with the $name parameter being required. The $name variable should be unique so you don’t overwrite someone else’s option, or someone else doesn’t overwrite your option.

I suggest you seriously consider using the update_option() function instead as it does the same thing better.

add_option($name, // Required (string). Name of the option to be added.
   $value, // Optional (mixed). The option value to be stored.
   $deprecated, // Optional (string). Just pass an empty string or null.
   $autoload); // Optional, defaults = 'yes'.

update_option()

Creates or Updates an option value in the database. The function accepts two variables, both of which are required.

update_option($option_name, // Required (string). Name of the option to update.
   $newvalue); // Required. (string|array|object) The new value for the option.

get_option()

The get_option() function allows you to retrieve a previously stored option from the database. It requires only one variable, which is the name of the option to retrieve.

get_option($option); // Required (string). Name of the option whose value you want returned.

delete_option()

The delete_option() function, as you might expect, deletes options from the database.

delete_option($option_name); // Required (string). Name of the option to delete.

A Code Example

You might recall from the previous lesson (143-admin-menu-0v3) that I stored options in the database as an array.

// Assign a default option name
var $pk_admin_options_name = 'pk_admin_options_data';

// Assign default option values
var $admin_options_array = array(
   'pk_search_text' => 'WordPress',
   'pk_replace_text' => 'WORDPRESS',
   'pk_url_text' => 'http://WordPress.org');

// Get options from database
$pk_options = get_options($this->pk_admin_options_name);
if (!empty($pk_options)) {
   foreach ($pk_options as $key => $option)
      $admin_options_array[$key] = $option;
} else {
   update_options($this->pk_admin_options_name,$this->admin_options_array)
}

We begin by assigning a default option name followed by an associative array that will eventually be stored in the WordPress database as an option. I do this so I don’t have to store multiple options (each a database call). This technique helps with code bloat, database queries, and naming collisions with other plugin authors.

WordPress Database Class

Another powerful method of storing and retrieving data from the WordPress database is using the WordPress Database class object.

When using the wpdb() class, you must first define $wpdb as a global variable before it will be available for use. To do so just insert, global $wpdb before any $wpdb->function calls.

• Methods in the wpdb() class should not be called directly.

The $wpdb object can be used to read data from any table in the WordPress database (such as custom plugin tables), not just the standard tables that WordPress creates.

In a function, a reference to the global wpdb() class would look like this:

0   PHP:
1   function sample_function() {
2      global $wpdb;
3   …
4   }

After this variable is referenced, you can access the many useful functions of the wpdb() class. For example, say we want to retrieve the total number of comments for our WordPress installation. Here’s a function that does that using the wpdb() class:

0   PHP:
1   function sample_function() {
2      global $wpdb;
3      $comments = $wpdb->get_row("SELECT count(comment_approved)
4         comments_count FROM $wpdb->comments where comment_approved = '1'
5         group by comment_approved", ARRAY_A);
6      echo $comments['comments_count'];
7   }

Here’s what the function does:

• On line 2, we add a reference to the global $wpdb variable.
• On line 3, we call a function inside the $wpdb class called get_row.
• On line 4, we retrieve data from the comments table ($wpdb->comments).
• On line 5, we specify that we want the data returned as an associative array (ARRAY_A).
• On line 6, we echo out the result. Since I wanted the results returned as an associative array, I simply call the variable I assigned the results in SQL, which was comments_count.

The $wpdb class is a very large class with a lot of functionality. I suggest heading over the wpdb() class page and looking over what the wpdb() class is capable of.

• See: http://xref.wordpress.org/trunk/wordpress/Database/wpdb.html

Protect Queries Against SQL Injection Attacks

One of the most important functions in the wpdb() class is the prepare function which prevents SQL injection attacks on your web site. This function is used for escaping variables passed to your SQL queries. All queries should be passed through the prepare function before being executed. The prepare method supports both a sprintf()-like and vsprintf()-like syntax.

To enable WordPress to execute your queries in the safest possible way you should use the database class wpdb() located in wp-includes/wp-db.php. This object class contains all the method functions you will need for working with the database.

<?php
global $wpdb;
$field_key = "address";
$field_value ="10 Downing St";

$wpdb->query( $wpdb->prepare(
   "
      INSERT INTO $wpdb->my_custom_table
      ( id, field_key, field_value )
      VALUES ( %d, %s, %s )
   ",
   1,
   $field_key,
   $field_value
 ) );
 ?>

• The WordPress Data Validation article is a must-read for all WordPress code contributors and plugin authors.

WordPress Database Lesson Conclusion

Well, I hope you found something useful and enjoyed this article on the WordPress Database.