How to Create Database Tables in WordPress Plugin using wpdb

Posted on 16th June 2023

Introduction

In this article, we will show you how to create database tables in WordPress plugin using wpdb class. First we will create a custom plugin and then we will use wpdb class to create database tables inside that plugin. This is very useful when you are developing a WordPress plugin that needs to store data in database.

Creating a Custom Plugin

First thing you need to do is create a custom plugin. For this, you need to create a new directory inside wp-content/plugins/ directory. For example, you can name it my-custom-plugin. Inside this directory, you need to create a new file and name it my-custom-plugin.php. Now you need to open this file and add following code to it.

<?php
/*
Plugin Name: My Custom Plugin
Plugin URI: http://example.com/my-custom-plugin
Description: This is a custom plugin for WordPress
Version: 1.0
Author: John Doe
Author URI: http://example.com
*/
?>

Now you need to activate this plugin from WordPress admin area. After activating the plugin, you should see My Custom Plugin menu item in left sidebar of WordPress admin area.

Creating Database Tables using wpdb

Now that we have created our custom plugin, we can start using wpdb class to create database tables. First we will define a function and then we will use that function to create database tables. Add following code to your plugin.

<?php
function wpdb_create_tables(){
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}my_custom_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
register_activation_hook( __FILE__, 'wpdb_create_tables' );
?>

This code will create a database table named wp_my_custom_table. You can change this table name to anything you want. This table will have three columns named id, name, and email. You can add more columns to this table according to your needs.

You can use following code to insert data into this table.

<?php
global $wpdb;
$wpdb->insert(
'wp_my_custom_table',
array(
'name' => 'John Doe',
'email' => 'john@example.com'
),
array(
'%s',
'%s'
)
);
?>

You can use following code to retrieve data from this table.

<?php
global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM wp_my_custom_table" );
foreach ( $results as $result ) {
echo $result->name . '<br>';
echo $result->email . '<br>';
}
?>

You can use following code to update data in this table.

<?php
global $wpdb;
$wpdb->update(
'wp_my_custom_table',
array(
'name' => 'John Smith'
),
array( 'id' => 1 ),
array(
'%s'
),
array( '%d' )
);
?>

You can use following code to delete data from this table.

<?php
global $wpdb;
$wpdb->delete(
'wp_my_custom_table',
array( 'id' => 1 )
);
?>

You can use following code to drop this table.

<?php
global $wpdb;
$wpdb->query( "DROP TABLE IF EXISTS wp_my_custom_table" );
?>

Conclusion

In this article, we showed you how to create database tables in WordPress plugin using wpdb class. First we created a custom plugin and then we used wpdb class to create database tables inside that plugin. This is very useful when you are developing a WordPress plugin that needs to store data in database.

To continue creating database tables in WordPress plugin using wpdb, you need to have a table prefix. This is a string of characters that will be added to the beginning of each table name. By default, the table prefix is wp_. You can change this by editing the wp-config.php file.

Next, you need to create a new file in your plugin directory and name it something like create-tables.php. In this file, you will first need to include the wp-config.php file so that you have access to the database connection information.

get_charset_collate();

$sql = “CREATE TABLE {$wpdb->prefix}myplugin_table1 (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT ‘0000-00-00 00:00:00’ NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
UNIQUE KEY id (id)
) $charset_collate;

$sql .= “CREATE TABLE {$wpdb->prefix}myplugin_table2 (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT ‘0000-00-00 00:00:00’ NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
UNIQUE KEY id (id)
) $charset_collate;”;

require_once( ABSPATH . ‘wp-admin/includes/upgrade.php’ );
dbDelta( $sql );

?>

In the code above, we are creating two database tables. The first table is named {$wpdb->prefix}myplugin_table1 and the second table is named {$wpdb->prefix}myplugin_table2. The $charset_collate variable contains the character set and collation for the database tables. The dbDelta function is used to create the database tables.

Once the database tables have been created, you can start adding data to them. To do this, you can use the $wpdb->insert() function. The following example shows how to insert data into the {$wpdb->prefix}myplugin_table1 table.

insert(
$wpdb->prefix . ‘myplugin_table1’,
array(
‘time’ => current_time( ‘mysql’ ),
‘name’ => ‘John Doe’,
‘text’ => ‘Lorem ipsum dolor sit amet…’
)
);
?>

The code above will insert a new row into the {$wpdb->prefix}myplugin_table1 table. The data that is being inserted is an array that contains the columns and values that we want to insert. The time column is set to the current time and the name and text columns are set to the values ‘John Doe’ and ‘Lorem ipsum dolor sit amet…’ respectively.

If you want to retrieve data from a database table, you can use the $wpdb->get_results() function. The following example shows how to retrieve all rows from the {$wpdb->prefix}myplugin_table1 table.

get_results( “SELECT * FROM {$wpdb->prefix}myplugin_table1” );

foreach ( $rows as $row ) {
echo $row->name . ‘
‘;
echo $row->text . ‘
‘;
}
?>

In the code above, we are using the $wpdb->get_results() function to retrieve all rows from the {$wpdb->prefix}myplugin_table1 table. We are then looping through each row and printing out the name and text columns.

You can also use the $wpdb->update() function to update data in a database table. The following example shows how to update the name column in the {$wpdb->prefix}myplugin_table1 table.

update(
$wpdb->prefix . ‘myplugin_table1’,
array(
‘name’ => ‘Jane Doe’
),
array(
‘id’ => 1
)
);
?>

In the code above, we are using the $wpdb->update() function to update the name column in the {$wpdb->prefix}myplugin_table1 table. The data that we are updating is an array that contains the column and value that we want to update. In this case, we are updating the name column to the value ‘Jane Doe’. The where clause is used to specify which row we want to update. In this case, we are updating the row with an id of 1.