You should think about creating your own database table in WordPress when you have large amounts of data which you need to report on it.
In this article I’ll explain why this is so, when you know you need your own custom table and how to create your own table in WordPress which can still be utilized by all the WordPress functionality.
// Table of Contents
- What is a Database table in WordPress Development?
- When should you consider creating your own Table?
- How to create your own table in WordPress?
- How to migrate a set of WordPress Posts to its own Table?
- Learn WordPress customization throughly with an online course at Udemy
// What is a Database table in WordPress Development?
WordPress runs on a set of Softwares or a ‘stack’ of technologies as they are known. One of these is MYSQL which is software that allows you to manage a Relational Database.
Relational Databases are useful because you can ‘net’ or ‘connect’ together various kinds of Data and conceptualise them as Objects. Like ‘Houses’ and ‘Owners’. A home has an owner and an owner has a home. One home could have multiple owners and one owner could have multiple homes.
Relational Databases allow you to record these relationships with ease and can be access by your PHP code and displayed around your WordPress Site.
// When should you consider creating your own Table?
Default WordPress way of storing Data:
WordPress stores most of its data in the ‘wp-posts’ table in the Database but also a significant amount of related data in the ‘wp_postmeta’ table.
This has its advantages and disadvantages but in this case if you are trying to access and work with large amounts of data in these tables then it can be slow and inefficient to get all of them in MYSQL.
This is because the way in which post meta data (stored in the wp_postmeta table) is technically related to the posts (stored in the wp_posts table) themselves doesn’t take full advantage of the relational potential of MYSQL.
Instead of storing each piece of meta data as a column in the wp_postmeta table (which would be faster to access) WordPress stores meta data in one column differentiated by different ‘meta keys’ (which is slow to access).
The later is slower because when MYSQL is searching for posts and their related meta data it needs to search through a large column of meta keys to find the appropriate ones for that particular post before returning the result. If it were a column with a single vale (as it would if each meta data was stored as a new column).
Size of the data and how do you plan to access it?
With the above in mind we can then say that if you are creating a large set of data with which you will more than likely want to search through and report of or access in large numbers then you will be better off creating your own table.
If you don’t then you will find that your site can be very slow when loading the various information you want to present to the user.
// How to create your own table in WordPress?
You will need to edit your theme’s (or child themes) functions.php.
Step 1:
Find this file at https://yoursite.com/wp-content/themes/yourtheme/funcitons.php
Step 2:
Add the below code but make sure to alter it to your circumstances by renaming the table and column names and function names to suit your own circumstances.
<?php
// existing code in functions.php
function exampleCreateNewTable() {
global $wpdb;
$table_name = $wpdb->prefix . "exampleTable";
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE IF NOT EXISTS $table_name (
id bigint(20) NOT NULL AUTO_INCREMENT,
columnOne bigint(20) UNSIGNED NOT NULL,
created_at datetime NOT NULL,
PRIMARY KEY id (id)
) $charset_collate;";
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);
}
add_action('init', 'exampleCreateNewTable');To explain the above code a little…
- We create a custom function which runs on the ‘init‘ action in WordPress which is very early in the WordPress process so our table will be created without error.
- We ensure to only create this table once by having ‘IF NOT EXISTS‘.
- NOTE: Remember to add as many column names as you need for your own table.
How to access this table in other parts of your website?
The above newly created table can be accessed in other parts of your site by using normal WordPress functions similar to this. For full documentation of this class and how to use it you can see the official WordPress docs here or view an alternate version by DesignModo here.
global $wpdb;
$results = $wpdb->get_results( "SELECT * FROM exampleTable" );
echo '<pre>';
print_r($results);
echo '</pre>';// How to migrate a set of WordPress Posts to its own Table?
If you are in need of migrating data from the the wp_posts (or another table) then you could do something like this.
Step 1:
Export the table as XML from phpMyAdmin. You could do this by going to phpMyAdmin and exporting the MYSQL table data that you want to place in the new table.
One example of this that would be common in WordPress would be to go to the ‘wp_posts’ table and click the ‘export’ button. Select the data you want to be in the new table, then select ‘export as XML’ and hit ‘go’.
This will produce a file with which you can use in the next step.
NOTE: Only export the data with which you want to be in the new table as columns.
Step 2:
Add this file to your file system. Perhaps in the theme or in the plugin you are working in. For example, you could add the XML file at https://yoursite.com/wp-content/themes/yourtheme/assets/exportOfDataToMigrate.xml
Step 3:
Add the below code to your theme’s (or child theme) to your functions.php file.
<?php
// existing code in functions.php
if($_GET['migrate'] == 'go') {
$xmlToMigrate = file_get_contents('https://yoursite.com/wp-content/themes/yourtheme/assets/exportOfDataToMigrate.xml');
$xml = new SimpleXMLElement($xmlToMigrate);
foreach($xml->allRows->row as $item){
mysqli_query($conn, "INSERT INTO exampleTable (columnNameOne, columnNameTwo ,columnNameThree ) VALUES (". $item->columnNameOne.", '". $item->columnNameTwo."', '" .$item->columnNameThree."')" );
}
}To explain the above code a little,
- We will wrap this loop in a URL Parameter so that it will only run if you load a URL such as this and load it, https://yoursite.com?migrate=go
- The code then goes and gets the XML file with which you exported the data and creates a string out of it.
- It then goes through this new string and adds each XML row in the file to the table ‘exampleTable’.
// Learn WordPress customization throughly with an online course at Udemy
If you are having difficulty understanding this tutorial and then I suggest you start from scratch with WordPress in general. The best way to learn is with a better all around general understanding of WordPress. Check out some of the courses on Udemy related to WordPress. They cover developer level courses and more non-dev CMS editor type tutorials as well.
