westclicks

web development, software and tutorials.

tutorial – adding a database table only once in your wordpress plugin

In this tutorial we will add a table to the wordpress database the first time the plugin loads.

This will be done by checking the database to see if the table exists. If it doesn’t…. then we’ll create a new table.

first we can check if a table exists by setting up a query and equivocating the result using

$wpdb
A global object provided by WP to make database queries etc.
more at – WP Codex – $wpdb object

NOTE: It is important to call $wpdb globally first, as we need to use the objects methods and properties. More information is available in the wordpress

global $wpdb;
if( $wpdb->query( 'SELECT * FROM ' . $wpdb->prefix . 'yourtablename' ) === false ) {
    // create the table here
}

As you can see from the above php code we did 2 things:

  1. We made the $wpdb object available by referencing it globally.
  2. using the if statement we checked to see if there was a response from the database. $wpdb->prefix is important because not all users will use the standard wp_ table prefix in their databases.
    NOTE: the use of === which equates to EQUIVALENT TO, we need to do this because calling $wpdb->query can return both false or 0. IF the table did exist but had no rows… $wpdb->query would have returned 0, not false. We need to check for equivocating false because we are interested in a failed request.

Next we create a table in the database between the if{}

$table_name = $wpdb->prefix . "yourtablename";

$query = "CREATE TABLE " . $table_name . " (
	col1 mediumint(9) NOT NULL AUTO_INCREMENT,
	col2 varchar(120) NOT NULL,
	col3 varchar(120) NOT NULL,
	PRIMARY KEY  (col1),
	KEY (col2)
);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($query);

In the above code we did a few things:

  • we used $wpdb->prefix again to create the table name variable
  • created the $query string that contains the SQL to create the table and to make the table columns. Standard MYSQL syntax is used.
    NOTE: every column definition is on its own line…. see below.
  • include the upgrade.php file (as it normally isn’t explicitly included) inorder to use the dbDelta() function.
  • use the dbDelta function to run the $query string

Now… pretty straight forward… BUT…

Beware the use of the dbDelta() function requires a degree of precision. It is a pedantic beast.

  1. You have to put each field on its own line in your SQL statement ($query string)
  2. You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.
  3. You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.

That being said, the dbDelta() function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary.

Post a Comment

You must be logged in to post a comment.