Feature
Post

Category
Code


WordPress Plugins and Database Interaction

How To Write a WordPress Plugin Series

This post was written as part of the How to Write a WordPress Plugin series.

When you are writing a plugin, you will inevitably have to store variables in a database and retrieve them. Fortunately WordPress makes data retrieval simple with options and a database object. This post will cover storing and retrieving data from a WordPress database.

Storing Data in a Database

There are two main ways to store data in the WordPress database:

  1. Create your own table.
  2. Use Options

Since most plugins will not require their own database table, I will only cover options. However, the WordPress codex has detailed instructions on how to set up your own table.
WordPress Options

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

  • add_option
  • get_option
  • update_option
  • delete_option

add_option

The add_option function accepts four variables, with the name of the option being required. The variables are: add_option($name, $value, $description, $autoload);

This function is beneficial for adding data to the database for retrieval later.

The $name variable should be unique so that you don’t overwrite someone else’s option, or someone else doesn’t write over yours.

I usually don’t use this function because update_option pretty much does the same thing.

get_option

The get_option function allows you to retrieve a previously stored option from the database. It accepts only one variable, which is the name of the option to retrieve. The function format is: get_option($option_name);

update_option

The update_option function works about the same as the add_option function except that it also updates an option if it already exists. I personally like the double functionality of the function and prefer it over add_option when storing data to the database.

The function format is: update_option($option_name, $new_value);

delete_option

The delete_option function deletes options from the database. The function format is: delete_option($option_name);

A Code Example

You might recall from previous posts in this series that I stored options in the database as an array. Here is a sample function followed by a brief explanation:

//Returns an array of admin options
function getAdminOptions() {
$devloungeAdminOptions = array('show_header' => 'true',
'add_content' => 'true',
'comment_author' => 'true',
'content' => '');
$devOptions = get_option($this->adminOptionsName);
if (!empty($devOptions)) {
foreach ($devOptions as $key => $option)
$devloungeAdminOptions[$key] = $option;
}
update_option($this->adminOptionsName, $devloungeAdminOptions);
return $devloungeAdminOptions;
}

On lines 3 – 6, I begin an associative array that will eventually be stored in the WordPress database as an option (line 12). 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.

The WordPress Database Class

Another powerful method of storing and retrieving data from the WordPress database is using the WordPress Database class object. In a function, a reference to the class would look like:

function sample_function() {
global $wpdb;
}

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:

function sample_function() {
global $wpdb;
$comments = $wpdb->get_row("SELECT count(comment_approved) comments_count FROM $wpdb->comments where comment_approved = '1' group by comment_approved", ARRAY_A);
echo $comments['comments_count'];
}

Here’s what the function does:

  • On line 2, we add a reference to the $wpdb variable.
  • On line 3, we call a function inside the wpdb class called get_row.
  • On line 3, we retrieve data from the comments table ($wpdb->comments).
    We specify that we want the data returned as an associative array (ARRAY_A).
  • On line 4, 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.

Conclusion

The purpose of this post was to give you a glimpse into storing and retrieving variables from the WordPress database.

For further reading, please check out these links:


  1. By Peter posted on October 2, 2007 at 12:21 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    Thank you, this is very helpful.

  2. By Rahul Bansal posted on July 22, 2008 at 10:42 am
    Want an avatar? Get a gravatar! • You can link to this comment

    Hi Ronald,
    I am developing a plugin which spans across multiple PHP files.
    One file contains wordpress plugin specific declaration and call to get_option() or other function works form it.
    But other PHP file could not call any wordpress built in function or any info.
    Do I need to use some require/include in the beginning of other PHP files?

    Hope to get an answer,
    Thanks,
    -Rahul :-)

  3. By sk posted on September 20, 2008 at 5:34 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    good post

  4. By J posted on January 4, 2009 at 4:56 pm
    Want an avatar? Get a gravatar! • You can link to this comment

    First of all, thank you for the wonderful tutorial! I’ve tried using the links you provided to create a database table upon activation of a plugin but it is not happening. Since I followed this tutorial, I was wondering if you could provide an example of how to create a db table upon activation based on this tutorial? I would greatly appreciate it.

  5. TrackbackDr-Hamza’s SpaceWordPress links: uge 48 - 2007 « WordPress TipsPaige’s Blog » Blog Archive » WordPress Plugins and Database InteractionAndis interaktive Fundgrube » Eigene Wordpress Plugins schreiben | Willkommen in meiner interaktiven Fundgrube. Hier finden Sie so allerlei Interessantes aus verschiedenen Themenbereichen.Essential Wordpress Plugin Development Resources, Tutorials and Guides : Speckyboy Design Magazine