Creating Custom Database Tables in WordPress

Posted on by

I was working on a project where I need to create a database table for my WordPress plugin. Generally we use mysql_query($sqlquery) for executing query in our PHP project. But in case of wordpress it’s bit different. WordPress provide different method for it.
Suppose I am using following query:

CREATE TABLE Emp (Empid BIGINT(20) NOT NULL AUTO_INCREMENT, Name VARCHAR(255) NOT NULL,Email VARCHAR(255) NOT NULL,Phone VARCHAR(255) NOT NULL, PRIMARY KEY  (Empid) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

In above query I am creating a table named “Emp” with “Empid”,”Name”,”Email”,”Phone” fields.
Now we want some PHP method for creating table in database. See below:

$field_sql="CREATE TABLE Emp (Empid BIGINT(20) NOT NULL AUTO_INCREMENT, Name VARCHAR(255) NOT NULL,Email VARCHAR(255) NOT NULL,Phone VARCHAR(255) NOT NULL, PRIMARY KEY  (Empid) ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";

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

dbDelta()

dbDelta ( string|array $queries = '', bool $execute = true )

dbDelta is a wordpress function which Modifies the database based on specified SQL statements. It is used for creating new tables and updating existing tables to a new structure.

For using database prefix and charset you need to add some changes in your mysql query.

For Table prefix

 global $wpdb;
 $table_name = $wpdb->prefix . 'EMP';

For Charset

global $wpdb;
$charset_collate = $wpdb->get_charset_collate();

Now final query is

 global $wpdb;
 $table_name = $wpdb->prefix . 'EMP';
 $charset_collate = $wpdb->get_charset_collate();

 $field_sql="CREATE TABLE $table_name (Empid BIGINT(20) NOT NULL AUTO_INCREMENT, Name VARCHAR(255) NOT NULL,Email VARCHAR(255) NOT NULL,Phone VARCHAR(255) NOT NULL, PRIMARY KEY  (Empid) ) $charset_collate;";

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