How to Move Custom Fields’ Data to Custom Tables

As you all may know, custom fields are definitely so powerful and effective in WordPress. However, if your website has too many custom fields' data, your database will be bloated considerably. So, to solve this problem, you should try moving your custom fields' data to a custom table.

Read till the end to watch our video tutorial.

Why You Need to Move Custom Fields' Data to Custom Tables

By default, WordPress saves custom fields' values in the wp_postmeta table in the database, in which each row stores a custom field's data. If you move these data of custom fields to a custom table, all fields will be saved in only ONE row. Therefore, you can release the burden of your database and improve your website performance dramatically!

Ok, now we will do it in action:

Preparation

To move your custom fields' data to a custom table, here are the tools you need:

• Meta Box core plugin to have a custom fields framework. It empowers you to create custom fields so quickly and easily. Just download it from wordpress.org.
• MB Custom Table is a premium extension of Meta Box to save custom fields' data in a custom table instead of in term / post / user meta tables like default WordPress.
• Meta Box Builder is also a premium extension of Meta Box. It gives you a UI to create and manage custom fields right on the backend.

Next, take a look at the custom fields that we're going to move.

But please note that:

You definitely can move all the custom fields' data in a website to an only row. However, saving custom fields' values of all custom post types in one table will make it difficult to manage, categorize, and may cause confusion for you.

Thus, we're going to move the data of each post type to a separate custom table. This method is a bit more time-consuming, but in a long run, it will be more beneficial. That's because the custom fields' data of different post types are saved in different tables.

Here, we already have a post type named Companies with Company name, Address, and Start day custom fields. This is the field group of these fields in Meta Box Builder:

These custom fields are displayed in the Companies post type like this:

Before you move it, the custom field's data above is still saved in the wp_postmeta:

To move this custom field's data to a custom table, follow these steps:

Step 1: Create a Custom Table

Now, go to Meta Box > Custom Fields and edit the field group I mentioned above:

Go to the Settings tab and just choose Save data in a custom table.

After that, some options will appear, and then you have to choose Create table automatically and enter the name of the custom table. I'll name it wp_custom_company:

So, you have just created a custom table automatically with Meta Box Builder. And now, in your database, you will see a new table named wp_custom_company.

From now on, the new data will be automatically saved in the custom table whenever you update. How about the old data of this post type? It is still in the wp_postmeta table, so we need to use some code to move it to the new wp_custom_company table.

Step 2: Move the Data to the New Custom Table

Here is the script to copy the custom fields' data in the wp_postmeta table and paste it to the wp_custom_company. Then, it will delete the old data in the wp_postmeta table. Just insert this code into the functions.php file.

Note: This script runs on the backend and only the admin can run it.

function estar_child_data_company() {

if ( empty( $_GET['move-data-companies'] ) || ! current_user_can( 'manage_options' ) ) {

return;

}

$paged = isset( $_GET['estar-child-paged'] ) ? $_GET['scf-paged'] : 0;

$paged += 1;

$url = add_query_arg( 'estar-child-paged', $paged, 'http://yourwebsite/wp-admin/?move-data-companies=1' );

$posts = estar_child_admin_records_get_companies( $paged );

if ( empty( $posts ) ) {

die( 'Done' );

}

foreach ( $posts as $post ) {

estar_child_move_data_company( $post );

}

echo "

<script>

setTimeout( () => {

window.location.href = '$url';

}, 3000 );

</script>

";

die;

}

add_action( 'admin_init', 'estar_child_data_company' );

In this code:

• estar is the prefix name of the theme I'm using (eStar). You can download this free theme here;
• http://yourwebsite/wp-admin/?move-data-companies=1: replace it with your own website URL;
• We will run the script when the URL has the move-data-companies=1 parameter. It means when you click the URL http://yourwebsite.com/wp-admin/?move-data-companies=1, it will automatically move the data.

After that, we need to write these 2 functions:

• star_child_admin_records_get_companies(): to take the list of posts
• estar_child_move_data_company(): to move data for each post

Now, put this code to your functions.php file:

function estar_child_admin_records_get_companies( $paged = 1 ) {

$args = [

'post_type'      => 'companies',

'posts_per_page' => 100,

'paged'          => $paged,

'fields'         => 'ids',

'orderby'        => 'ID',

];

$query = new WP_Query( $args );

return $query->posts;

}

function estar_child_move_data_company( $post_id ) {

global $wpdb;

echo 'Migrating company ', $post_id, '<br>';

$data = [];

$data['ID'] = $post_id;

$data['company_name'] = get_post_meta( $post_id, 'company_name', true );

$data['address'] = get_post_meta( $post_id, 'address', true );

$data['start_day'] = get_post_meta( $post_id, 'start_day', true );

$data = array_filter( $data ); 

if ( $data ) {

$wpdb->insert( 'wp_custom_company', $data );

}

$meta_key_array = [

'company_name',

'address',

'start_day',

];

$meta_key = "'" . implode( "','", $meta_key_array ) . "'";

$wpdb->query( $wpdb->prepare( "

DELETE FROM wpqq_postmeta

WHERE post_id = %d AND meta_key IN ( $meta_key )",

$post_id

) );

}

Explanation:

Code	Explanation
'company_name', 'address', 'start_day'	The ID of 3 custom fields in the Companies post type
$wpdb->insert( 'wp_custom_company', $data )	The function to insert data to the wp_custom_company table we created in the step 1
$wpdb->query( $wpdb->prepare( "DELETE FROM wpqq_postmetaWHERE post_id = %d AND meta_key IN ( $meta_key )",$post_id	This code is to delete the old data in the wp_postmeta table
'Companies'	The slug of the post type
'posts_per_page' => 100	The number of the posts that is handled when we run the script. To run the script at best, we should handle only 100 posts each time. After each time, the script will be automatically refreshed and handle the next 100 posts.

After adding code, you can go to this URL: http://yourwebsite/wp-admin/?move-data-companies=1 to enable moving the custom fields' data to the custom table.

After running the script, I go to the wp_custom_company custom table to check. And here is the result.

As you can see, the custom fields' data of the Companies post type is now moved to the wp_custom_company table.

It also is deleted from the wp_postmeta table at the same time.

Ok, we've done it!

Video Tutorial

This video tutorial will help you move custom fields' data to custom tables more easily. And don't forget to like and subscribe to our channel to have more video.

https://youtu.be/JaEvtYa4Hcg

Final Words

This work means a lot if you have a website with lots of data inputted into the fields. So, take advantage of this special feature from Meta Box now to make your site faster and more performative.

As I said at the beginning, you may think that following my method (move the data of the custom fields of each post type at one time) may take a bit more than moving custom fields' data for all post types at once. However, we highly recommend it because that will be more beneficial in the long run and help you manage your data more easily.

Good luck and have a "field" day!