How to work with WordPress Database Table

Creating a WordPress Custom Database Table

Once we've decided that a custom table is necessary, we need to create the table. Before we do that we'll store the name of our custom table in $wpdb. This global contains all the information pertaining to the database for the current blog (it will change from site to site, when using multi-site). We'll add our table name to this global. 

add_action( 'init', 'wptuts_register_activity_log_table', 1 );

add_action( 'switch_blog', 'wptuts_register_activity_log_table' );

 

function wptuts_register_activity_log_table() {

    global $wpdb;

    $wpdb->wp_activity_log = "{$wpdb->prefix}wp_activity_log";

}

The above code uses $wpdb->prefix to add a prefix to the table name. The prefix is by default wp_ but can be altered by the user in wp-config.php. This is necessary when you might have more than one WordPress install using the same database, but may also be changed for other reasons. As such we can not assume the prefix is wp_. As with functions, classes and settings etc, we should ensure our table name is unique.

Pulling Data Back with $wpdb Query

WordPress uses the $wpdb class to access data within the WordPress database (and the table we just created). 

To start off we’ll set $wpdb as a global so we can access the database and then using $wpdb we’ll run the get_results function to pull all our table’s data into an array called $results and print them to the screen:

<?php
global $wpdb;
$results = $wpdb->get_results("SELECT * FROM wp_activity_log");
print_r($results);
?>

We can use this code example to list out any rows we might be pulling back into our ‘$results’ array. If we only want to pull a specific row or column we can use get_row() or get_col() .

Adding to the WordPress Database with $wpdb Insert

The code example below adds a new activity to the ‘wp_activity_log’ table. The insert function includes the table name, an array of data to enter, and the format the data is in. The first array begins with a column name (‘name’) and then inserts a variable into it ($activity_name). These could also be values themselves instead of variables, but in most cases we’ll be using variables that we captured via a form. The second array contains the variable type for each column inserted into the row. In this example, the first two are strings (%s) and the second two are numbers (%d). We could also have a floating point number (%f).

global $wpdb;

$activity_name = 'Toby';
$activity_for = 'boy';
$category1 = 1;
$category2 = 0;

$wpdb->insert('wp_activity_log',
     array(
          'name'=>$activity_name,
          'gender'=>$activity_for,
          'category1'=>$category1,
          'category2'=>$category2
     ),
     array( 
          '%s',
          '%s',
          '%d',
          '%d'
     )
);