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.

Why do You Need to Move Custom Fields 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.
• 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.

Note:

You can move all the custom fields' data of all custom post types to one custom table. However, it will be 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:

And the custom field's data is 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 existing 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.

function estar_child_data_company() {
    if ( empty( $_GET['move-data-companies'] ) || ! current_user_can( 'manage_options' ) ) {
        return;
    }

    $paged = isset( $_GET['estar-child-paged'] ) ? $_GET['estar-child-paged'] : 0;
    $paged += 1;
    $url = add_query_arg( 'estar-child-paged', $paged, 'https://yourwebsite.com/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' );

This script will run when you access the URL https://yourwebsite.com/wp-admin/?move-data-companies=1. It's a URL to your WordPress backend, here we use a custom URL parameter move-data-companies to let the script knows to process the data.

The script will check if the current user is an admin, so normal users can't run it. When you access the URL, the script will get all the companies via estar_child_admin_records_get_companies() and for each company, it will process its data via estar_child_move_data_company().

To prevent long execution, the code doesn't process all companies at once. Instead, it process only a batch of companies with pagination.

Now, we need to write these 2 functions:

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

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
    ) );
}

This code queries 100 companies in each run. For each company, it gets the custom fields' data from the post meta, and then insert them into the new custom table. After that, it removes all custom fields from the post meta table.

After adding code, go to this URL: https://yourwebsite.com/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.

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.

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. If you have any questions, let us know in the comments.