How to Implement a Custom Database Schema with wpdb in WordPress Plugin
Posted on 18th June 2023
As a WordPress plugin developer, you may need to implement a custom database schema to store data. In this article, we will show you how to implement a custom database schema with wpdb in WordPress plugin.
First thing you need to do is connect to your WordPress database using phpMyAdmin or any other MySQL database management tool.
Once connected, you need to create a new database table. For this example, we will name our database table wp_my_plugin_table.
You can use the following SQL code to create the database table:
CREATE TABLE `wp_my_plugin_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`my_field_1` varchar(255) NOT NULL,
`my_field_2` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Now that you have created the database table, you need to write some code to connect to your database and insert data into it.
We will be using the WordPress $wpdb class to connect to our database and run our SQL queries.
The first thing you need to do is include the wp-config.php file which will give us access to the $wpdb class.
include_once( ABSPATH . ‘wp-config.php’ );
Next, we need to define some global variables for our database table name and WordPress database table prefix.
global $wpdb;
$table_name = $wpdb->prefix . “my_plugin_table”;
$wpdb->query( “INSERT INTO $table_name (my_field_1, my_field_2) VALUES (‘value 1’, ‘value 2’)” );
This code will insert a new row into our database table with the values ‘value 1’ and ‘value 2’ for the fields my_field_1 and my_field_2 respectively.
You can also use the $wpdb->update() method to update an existing row in the database table.
$wpdb->update(
$table_name,
array(
‘my_field_1’ => ‘value 1’,
‘my_field_2’ => ‘value 2’
),
array( ‘id’ => 1 ),
array(
‘my_field_1’ => ‘%s’,
‘my_field_2’ => ‘%s’
),
array( ‘%d’ )
);
This code will update the row with id 1 in our database table and set the values of my_field_1 and my_field_2 to ‘value 1’ and ‘value 2’ respectively.
You can also use the $wpdb->delete() method to delete a row from the database table.
$wpdb->delete(
$table_name,
array( ‘id’ => 1 ),
array( ‘%d’ )
);
This code will delete the row with id 1 from our database table.
You can also use the $wpdb->get_results() method to retrieve data from the database table.
$results = $wpdb->get_results( “SELECT * FROM $table_name” );
foreach ( $results as $result ) {
echo $result->my_field_1 . ” – ” . $result->my_field_2;
}
This code will retrieve all rows from our database table and print the values of my_field_1 and my_field_2 for each row.
You can also use the $wpdb->get_row() method to retrieve a single row from the database table.
$result = $wpdb->get_row( “SELECT * FROM $table_name WHERE id = 1″ );
echo $result->my_field_1 . ” – ” . $result->my_field_2;
This code will retrieve the row with id 1 from our database table and print the values of my_field_1 and my_field_2.
You can also use the $wpdb->get_var() method to retrieve a single value from the database table.
$result = $wpdb->get_var( “SELECT my_field_1 FROM $table_name WHERE id = 1” );
echo $result;
This code will retrieve the value of my_field_1 from the row with id 1 in our database table.
That’s all, we hope this article helped you learn how to implement a custom database schema with wpdb in WordPress plugin. You may also want to check out our guide on how to debug WordPress plugins.
If you liked this article, then please subscribe to our YouTube Channel for WordPress video tutorials. You can also find us on Twitter and Facebook.
The above code would give us a table called ‘custom_table’ with two columns, ‘id’ and ‘name’. We can now use the $wpdb object to interact with our database.
Inserting Data
We can insert data into our database table using the $wpdb->insert() method. This method takes three parameters – the table name, an array of data to insert, and an array of data types. The data type array is used to tell WordPress how to sanitize and validate the data before inserting it into the database.
$wpdb->insert(
‘custom_table’,
array(
‘name’ => ‘John Doe’,
),
array(
‘%s’,
)
);
The above code would insert a new row into our ‘custom_table’ with the name ‘John Doe’.
Updating Data
We can update data in our database table using the $wpdb->update() method. This method takes three parameters – the table name, an array of data to update, and an array of where conditions. The data type array is used to tell WordPress how to sanitize and validate the data before updating the database.
$wpdb->update(
‘custom_table’,
array(
‘name’ => ‘Jane Doe’,
),
array(
‘id’ => 1,
),
array(
‘%s’,
),
array(
‘%d’,
)
);
The above code would update the row with an id of 1 in our ‘custom_table’ and set the name to ‘Jane Doe’.
Deleting Data
We can delete data from our database table using the $wpdb->delete() method. This method takes two parameters – the table name and an array of where conditions. The data type array is used to tell WordPress how to sanitize and validate the data before deleting from the database.
$wpdb->delete(
‘custom_table’,
array(
‘id’ => 1,
),
array(
‘%d’,
)
);
The above code would delete the row with an id of 1 from our ‘custom_table’.
Selecting Data
We can select data from our database table using the $wpdb->get_results() method. This method takes a SQL query as a parameter and returns the results as an object.
$results = $wpdb->get_results( “SELECT * FROM custom_table” );
foreach ( $results as $result ) {
echo $result->name;
}
The above code would select all rows from our ‘custom_table’ and output the ‘name’ column for each row.
Conclusion
In this article, we’ve seen how to create a custom database table and interact with it using the $wpdb object. We’ve also seen how to insert, update, delete, and select data from our database table.
To finish off our example we need to flush the rewrite rules to make sure our custom URL works. The easiest way to do this is by visiting the Permalinks page in the WordPress admin. This will trigger the rewrite rules to be refreshed and our custom URL should work.
If you’re not using pretty permalinks then you can flush the rewrite rules by adding the following code to your plugin:
function my_plugin_flush_rewrite_rules() {
flush_rewrite_rules();
}
register_activation_hook( __FILE__, ‘my_plugin_flush_rewrite_rules’ );
And that’s it! You should now have a working custom database schema in your WordPress plugin.
