WordPress Database Functions & Usage

WordPress has it's own built-in database functions that plugin developers can use. Since WP already uses a mySQL database, everything you need as a developer is already there.

WordPress Options vs Custom Database Usage

Whenever you want to save some type of information to the WP database through a plugin, you basically have two choices: create custom options or create custom database tables.

Technically, almost any situation could use either method. However, it is still important to consider which one will actually work the best.

If you simply wish to create a standalone setting that will be used by your plugin in a variety of ways, I recommend to use a custom WP option.

However, if you need to save information to the database that has multiple pieces that all go together or information that needs to be easily searched, you're better off creating custom database tables.

This particular tutorial lesson is to provide training on working with the database, so you may also want to take a look at my tutorial on creating and using WordPress options.

Important Note Concerning WP Database Usage

One of the most important things that developers need to understand about working with the WP database is that the table prefix variable must be used! This helps to ensure that the plugin will be compatible with both standard and multisite installations of WordPress, and it will also be compatible if the user has a customized WP database table prefix name.

Sample code using WP database table prefix variable ($wpdb->prefix):

// Brings global $wpdb variable into the local scope, which allows access to the
// WP database class functions and the table prefix variable

global $wpdb;

// $wpdb->prefix is the database table prefix variable
// databasetablename is the name of the database table, but without the prefix
// wp_ is the default table prefix

$wpdb->query("SELECT * FROM `".$wpdb->prefix."databasetablename WHERE id = 1");

Create a Custom WP Database Table

Although there are tables that already exist in the WordPress database by default, many plugin developers will want to be able to create their own database tables to save and manage information for their own plugin.

WordPress uses MySQL as the database type, but it uses it's own database class library.

To use this database class, you simply need to bring it into the local scope of each plugin function where you need it by calling the global variable - $wpdb.

The basic function used to work with the database is the $wpdb->query() function.

The example code below shows how to use the query function to create a new table in the WordPress database.

// First bring the WP database class into the local scope

global $wdpb;

// Then create the table
// First specify the name of the table
// Here, I have named this table "rsplugins_users"
// After that, each field for the database table needs to be specified
// Teaching MySQL is a bit beyond the scope of this tutorial, so be sure to research this
// topic if you need help on how to do this. A number of example fields have been
// provided for this table, which will at least give you a variety of field type examples

$query = "CREATE TABLE `".$wpdb->prefix."rsplugins_users` (
`rspid` BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`rspemail` VARCHAR( 255 ) NOT NULL ,
`rsprating` DECIMAL( 2,1 ) NOT NULL ,
`rspcount` INT( 11 ) NOT NULL ,
`rspupgrade` TINYINT( 1 ) NOT NULL ,
`rspdata` TEXT NOT NULL ,
UNIQUE (`rspemail`),
INDEX (`rsprating`),
INDEX (`rspcount`),
INDEX (`rspupgrade`));
";

// To execute this query to create the table, just use the query()
// class function and reference the $query variable we just saved

$wpdb->query($query);

Insert Data To The Database

Now that we have created a custom database table, we can use this table to store data that is needed for the plugin.

This can be done with the standard query() class function, but WordPress actually provides a function specifically for this purpose: $wpdb->insert().

With this function, you do not need to worry as much about the data being saved to the database because WordPress will check that there is not SQL injection attack code in the data. If you simply insert data using the query() function, you will also need to use the prepare() function to ensure that data is not dangerous code. I'll cover the prepare() function later in this tutorial.

The example code below shows how to insert new data into the table.

// As always, first bring the WP database class into the local scope

global $wdpb;

// Next, save the name of the database table you want to work with to a variable

$table_name = '`'.$wpdb->prefix.'rsplugins_users`';

// Now we need an array that contains all of the field names and values

$data_array = array(
'rspemail' => 'email@example.com',
'rsprating' => '5.0',
'rspcount' => 10,
'rspupgrade' => 1,
'rspdata' => 'Some miscellaneous data for the user - perhaps custom saved profile information'
);

// Now we need another array that has the same number of variables
// This second array will store the data type for each field - WP uses this to
// verify the data is valid before saving it. Possible values are:
// '%s' for strings, '%d' for whole numbers, '%f' for floats (decimals)

$datatype_array = array(
'%s',
'%f',
'%d',
'%d',
'%s'
);

// All of the information we need to save this new record to the database is now
// stored in variables. The only thing left to do now is to call the insert()
// class function with these stored variables/arrays

if ($wpdb->insert($table_name, $data_array, $datatype_array))
{

// By enclosing the insert function in an 'if' statement, you can verify whether the
// record was successfully saved or not.
// If it has reached here, it was successfully saved
// You may have noticed that I left out the 'rspid' field when creating this record
// even though the field exists in the database
// That field was actually specified as an 'AUTO_INCREMENT' field
// This means that each new record will be auto assigned a number for that field,
// which will be one higher than the previous number assigned
// I have stored the value of 'rspid' from this record insert as a variable below

$rspid = $wpdb->insert_id;

}else{

// If this is reached, the record was not saved successfully
// This may be a good place to output some text to let the user know

echo 'There was a problem trying to save your information. Please verify that all information is correct and try again.';

}

Update Saved Database Data

Once data is saved in the database, you can always update that data with new information using the $wpdb->update() class function.

This works very similar to the insert() function except that it needs two additional arrays to work.

// As always, first bring the WP database class into the local scope

global $wdpb;

// To avoid repeating myself, I am going to use the exact same $table_name variable and
// the same $data_array and $datatype_array arrays with this example
// The data arrays should contain the information that you want to update for this record
// You will also need two more arrays to update a database record
// These two arrays help to identify the record you want to update
// I am going to identify that record using the value of the 'rspid' field
// First, I need another data array that specifies the field name and value to search for
// (this could be more than one field)

$search_array = array(
'rspid' => 1
);

// Next, I need an array to identify the type of data for this field

$searchtype_array = array(
'%d'
);

// Now I have all of the needed information to execute the database update query

$wpdb->update($table_name, $data_array, $search_array, $datatype_array, $searchtype_array);

WP Database Functions: prepare() and get_row()

The prepare() function should be used in combination with other WP database functions (except insert and update) whenever you are using information that could potentially have malicious SQL injection code (ie, user input).

To use this class function, you need to use the data types ('%s', '%d', or '%f') within the actual query string that you want to execute.

Then, an array of values is passed to the prepare function. This array should contain the data that will replace the data types in the query (in the same order).

To retrieve saved information, there are a number of different WP functions that can be used.

The most commonly used of these, the get_row() function, allows you to retrieve a single row or record from the database.

The example code below shows a simple query using the prepare and get_row functions.

// As always, first bring the WP database class into the local scope

global $wdpb;

// This is a variable being saved from user input

$rsplugins_userinput = $_POST['userinput'];

// The query string, which contains a data type of '%s'
// will represent the user input variable

$query = "SELECT * FROM `".$wpdb->prefix."rsplugins_users where rspemail = %s limit 1";

// Create an array with the data type values

$data_array = array(
$rsplugins_userinput
);

// Now, we execute this query and use the prepare function

$results = $wpdb->get_row($wpdb->prepare($query, $data_array), ARRAY_A);

// $results is now an array of variables
// Each variable is a field in this database table

$user_email = $results['rspemail'];

WP Database Function: get_results()

When you are looking to retrieve more than one row with a single database query, you will want to use the $wpdb->get_results() function.

This works very similar to the get_row() function, except that the resulting array has another array level to it to separate each row of data.

// As always, first bring the WP database class into the local scope

global $wdpb;

// This is a variable being saved from user input

$rsplugins_userinput = $_POST['userinput'];

// The query string, which contains a data type of '%s'
// will represent the user input variable

$query = "SELECT * FROM `".$wpdb->prefix."rsplugins_users where rspemail LIKE %s";

// Create an array with the data type values

$data_array = array(
$rsplugins_userinput
);

// Now, we execute this query and use the prepare function

$results = $wpdb->get_results($wpdb->prepare($query, $data_array), ARRAY_A);

// This time, the $results variable has another level to it
// If you need to access all of this information, you can
// loop through it

// This ensures the array is not empty

if (!empty($results))
{

foreach ($results as $num => $record)
{

// $num is a number that increments with each record
// $record is an array for the record

// For example, the field value for 'rspid'

$rspid = $record['rspid'];

// This same variable could also be accessed as this

$rspid = $results[$num]['rspid'];

}

}

Comments are closed.