When you create a WordPress plugin, you can usually store and retrieve all of your plugin data using the WordPress options() functions. Sometimes, though, you need a more robust storage solution for the data gathered by your plugin. When you need to store large amounts of data, or you need to have more control over the storage structure, you can make use of WordPress’s custom MySQL tables. These custom tables can be created, updated, and deleted using WordPress functions, rather than interacting with the database directly. In this post series, we’ll discuss how to create a custom table to store our data, how to insert data into our custom tables, how to update our custom tables, and, finally, how to delete rows in our custom tables. *Note* – this series assumes that you have already created your plugin.
Creating custom tables inside WordPress
The code for creating a custom table is pretty simple, but involves a little bit of MySQL.
In the sample below, you’ll need to replace my_custom_table_name with whatever you want your table name to be. It should be something unique to your plugin. You’ll also want to replace my_title and show_title with your table’s column names. You can add additional columns by using the format: “column_name column_type NULL/NOT NULL,“. (A quick Google search for MySQL column types will get you a list of proper values)
[php]
$sql = "CREATE TABLE my_custom_table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
my_title longtext NOT NULL,
show_title text NOT NULL,
UNIQUE KEY id (id)
);";
require_once(ABSPATH . ‘wp-admin/includes/upgrade.php’); //Include the file we need to make creating custom tables possible.
dbDelta($sql); //function that actually creates our database table
[/php]
And that’s all there is to it, we now have a custom table in our WordPress database in which we can store our plugin data.
Next post we’ll talk about how to insert rows into our newly created table.