Connecting your plugin to the WordPress Database

Connecting to the WordPress database is very simple. The $wpdb variable connects to the class which controls the access to the database meaning your plugin can easily use the database without having to write a connection script. The $wpdb class also contains routines which will make your life easier. This post details the $wpdb variable and the basic routines it supports. It is intended to help those building a plugin which needs a database and does not describe how to build a plugin.

Audience: WordPress Plugin Developers
Level: Beginner – Intermediate
Assumed Knowledge: You know how to build a WordPress Plugin and how to write queries in MySQL.

In this tutorial we are going to build a simple plugin which when a user visits a page in your WordPress environment their IP, the page visited, and time is stored in the database which the results will be shown in the admin section of WordPress. This plugin is going to be very basic yet you can easily imagine just how much analytics you could possibly do with the data captured.

The Plug-in has 3 main parts:

  1. Creating the table when the plugin is first activated
  2. Tracking the visits
  3. An admin page to see the summary of visits

The point to this plugin is to demo how to connect to the database using the WordPress API, though you could us it for basic analytics.

The main routines you will use for plugin is likely to be:

  • get_var – used to retrieve a single value (covered)
  • get_row – used to get a single row (covered)
  • get_results – used to get a collection of results (covered)
  • update – used to update rows (not covered)
  • insert – used to insert a row (covered)
  • $wpdb -> insert_id – returns the id of the last inserted row with an auto-increment field (not covered)
  • $wpdb -> num_rows?- returns the number of rows effected by the last query (covered)
  • $wpdb->posts etc – returns the table with the posts (covered)

The Final Code

For those of you wishing to skip ahead head is the final result:
[cc lang="PHP"]
get_var(“show tables like ‘”. $theTable . “‘”) != $theTable) { //using the get_var routine which is best to get a single value
$wpdb->query($sql); //the query function lets us execute most MySQL querys
}
}

function trackeraddtoheader(){//function which gets the page details and stores them using the insert method and an array
global $post, $wpdb; //wordpress post and wpdb global object
$currentpage["page"]=$post->post_title; //loads the title of the current page or post being loaded
$currentpage["IP"]=$_SERVER['REMOTE_ADDR'];//Gets the IP Address
$currentpage["createdDate"]=date( ‘Y-m-d H:i:s’, time());//Formats PHP time into MySQL
$wpdb->insert( ‘our_tracker’, $currentpage);//insert the captured values
}

function tracker_menu() {//Function to create our menu
add_menu_page(‘Tracker’, ‘Tracker’, ‘administrator’, ‘tracker-options’, ‘tracker_options’);
}

function tracker_options() {//function to build the contents for the admin window
global $wpdb;
?>

Tracker

  • Total visits get_var(“SELECT count(*) FROM our_tracker”); ?>
  • Total unique visits get_var(“SELECT count(DISTINCT IP) FROM our_tracker”); ?>
  • Top get_results(“SELECT page, count(IP) AS theCount FROM our_tracker GROUP BY page ORDER BY theCount DESC LIMIT 0,10″);
    echo $wpdb->num_rows;
    ?> page’s visited

    1. page; ?> – theCount; ?>
  • Top get_results(“SELECT page, count(DISTINCT IP) AS theCount FROM our_tracker GROUP BY page ORDER BY theCount DESC LIMIT 0,10″);
    echo $wpdb->num_rows;
    ?> page’s with unique visits

    1. page; ?> – theCount; ?>
  • Page with top visits details get_var(“SELECT page FROM our_tracker GROUP BY page ORDER BY count(*) DESC LIMIT 0,1″);
    $topPageID = get_page_by_title($topPage)->ID;
    $topPageDetails = $wpdb->get_row(“SELECT * FROM $wpdb->posts WHERE ID = ” . $topPageID);
    if($topPageDetails){
    echo ”
      “;
      foreach($topPageDetails as $detail => $value){
      echo ”
    • ” . $detail . ” – ” . $value . “
    • “;
      }
      }

      ?>

show_errors();
add_action(“loop_end”, “trackeraddtoheader”);
register_activation_hook( __FILE__, ‘tracker_activate’ ); //hook to call the function when it is activated
add_action(‘admin_menu’, ‘tracker_menu’);
$wpdb->hide_errors();
/*
$wpdb->update( ‘table’, array( ‘column1′ => ‘value1′, ‘column2′ => ‘value2′ ), array( ‘ID’ => 1 ), array( ‘%s’, ‘%d’ ), array( ‘%d’ ) )
*/
?>
[/cc]

When you want to connect to the WordPress database you have to remember to call the global $wpdb in your function. This is a very simple script:
[cc lang="PHP"]
global $wpdb;
[/cc]

Creating a table

So the plugin can store the data the first thing we want to do is to create the table, at the same time we do not want to create the table twice. In this snippet we see both the get_var and query routines used. At the end of the code register_activation_hook( __FILE__, ‘tracker_activate’ ) tells WordPress to execute the function tracker_activate when the plugin is activated. The function createTable() is a reusable function that can be called multiple times if you have to create multiple tables. The createTable function first calls the global $wpdb class which is very important to execute any of the routines. Next it checks if the table exists using the get_var routine this only one value is being returned and we want is as a variable. If the table doesn’t exist createTable creates the table using the query routine. A limitation I have en-counted with the query routine has been that it can only execute one query per a call.

[cc lang="PHP"]
function tracker_activate(){ //This is all the stuff the plug-in needs to do when it is activated
$sql = “CREATE TABLE `our_tracker` (`ID` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `IP` TEXT NOT NULL, `page` TEXT NOT NULL, `createdDate` TIMESTAMP NOT NULL) ENGINE = MyISAM;”;
createTable(‘our_tracker’, $sql); //Little function to check that the table does not already exist
}

function createTable($theTable, $sql){//reusable function
global $wpdb;//call $wpdb to the give us the access to the DB
if($wpdb->get_var(“show tables like ‘”. $theTable . “‘”) != $theTable) { //using the get_var routine which is best to get a single value
$wpdb->query($sql); //the query function lets us execute most MySQL querys
}
}
[/cc]

Inserting Data

To insert data we first load it into an array and use the $wpdb->insert routine. You could also use the $wpdb-query routine, yet it does not have the SQL protection of $wpdb->insert. First we load data into an array using the field names as keys the values and then use that array to in the insert routine. Our function is called each time the loop is end by line add_action(“loop_end”, “trackeraddtoheader”).

[cc lang="PHP"]
function trackeraddtoheader(){//function which gets the page details and stores them using the insert method and an array
global $post, $wpdb; //wordpress post and wpdb global object
$currentpage["page"]=$post->post_title; //loads the title of the current page or post being loaded
$currentpage["IP"]=$_SERVER['REMOTE_ADDR'];//Gets the IP Address
$currentpage["createdDate"]=date( ‘Y-m-d H:i:s’, time());//Formats PHP time into MySQL
$wpdb->insert( ‘our_tracker’, $currentpage);//insert the captured values
}
[/cc]

Retrieving Data

We can retrieve data using get_var, get_row or get_results.

The get_var returns a variable for the results and is suited for times when a single variable is being returned. In this example we are getting a count of all of the visits.

[cc lang="PHP"]

  • Total visits get_var(“SELECT count(*) FROM our_tracker”); ?>
  • [/cc]

    The get_results routine returns an object which contains the rows data. It is sort of like an array, and if you want to return it as an array you can. I prefer the object. In the below example the $wpdb->num_rows returns the number of rows that were affected by the last command. To extract each of the rows we use a foreach each.

    [cc lang="PHP"]
    $visits=$wpdb->get_results(“SELECT page, count(IP) AS theCount FROM our_tracker GROUP BY page ORDER BY theCount DESC LIMIT 0,10″);
    echo $wpdb->num_rows;

    foreach ($visits as $visit){
    echo $visit->page;
    echo $visit->theCount;
    }
    [/cc]

    If you just want a single row returned the get_row method is best. It gets only the first row of the returned results. Again I still prefer the object.

    [cc lang="PHP"]
    $topPageDetails = $wpdb->get_row(“SELECT * FROM $wpdb->posts WHERE ID = ” . $topPageID);
    if($topPageDetails){
    echo ”

      “;
      foreach($topPageDetails as $detail => $value){
      echo ”
    • ” . $detail . ” – ” . $value . “
    • “;
      }
      }
      [/cc]

      About these ads

    2 comments


    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s