How to Move Custom Fields’ Data to Custom Tables
Tags: wordpress
Published: 2021-04-07 02:50:07

<!-- wp:paragraph -->

<p>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.</p>

<p>Read till the end to watch our video tutorial.</p>

<!-- wp:heading -->

<h2>Why You Need to Move Custom Fields' Data to Custom Tables</h2>

<!-- /wp:heading -->

<!-- wp:paragraph -->

<p>By default, WordPress saves custom fields' values in the <code>wp_postmeta</code> table in the database, in which each row stores a custom field's data. If you move these data of <a href="https://metabox.io/series/custom-fields">custom fields</a> to a custom table, all fields will be saved in<strong> only ONE row.</strong> Therefore, you can release the burden of your database and improve your website performance dramatically!</p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p>Ok, now we will do it in action:</p>

<!-- /wp:paragraph -->

<!-- wp:heading -->

<h2>Preparation</h2>

<!-- /wp:heading -->

<!-- wp:paragraph -->

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

<!-- /wp:paragraph -->

<!-- wp:list -->

<ul>

<li><a href="https://metabox.io/">Meta Box</a> core plugin to have a custom fields framework. It empowers you to create custom fields so quickly and easily. Just download it from <a href="https://wordpress.org/plugins/meta-box/">wordpress.org</a>.</li>

<li><a href="https://metabox.io/plugins/mb-custom-table/">MB Custom Table</a> 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.</li>

<li><a href="https://metabox.io/plugins/meta-box-builder/">Meta Box Builder</a> is also a premium extension of Meta Box. It gives you a UI to create and manage custom fields right on the backend.</li>

</ul>

<!-- /wp:list -->

<!-- wp:paragraph -->

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

<p><strong>But please note that</strong>:</p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p><em>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.</em></p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p><em>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.</em></p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

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

<p><img class="aligncenter" src="https://i.imgur.com/rHoumB6.png" alt="The field group for the Companies post type in Meta Box Builder plugin" width="1352" height="543" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>These custom fields are displayed in the <strong>Companies</strong> post type like this:</p>

<p><img class="aligncenter" src="https://i.imgur.com/WapcPoq.png" alt="The custom fields are displayed in the Companies post type." width="1333" height="549" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>Before you move it, the custom field's data above is still saved in the <code>wp_postmeta</code>:</p>

<p><img class="aligncenter" src="https://i.imgur.com/ntTFIwh.png" alt="The custom field's data is still saved in the wp_postmeta table." width="1000" height="404" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

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

<!-- /wp:paragraph -->

<!-- wp:heading -->

<h2>Step 1: Create a Custom Table</h2>

<!-- /wp:heading -->

<!-- wp:paragraph -->

<p>Now, go to <strong>Meta Box</strong> &gt; <strong>Custom Fields</strong> and edit the field group I mentioned above:</p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p>Go to the <strong>Settings</strong> tab and just choose <strong>Save data in a custom table</strong>.</p>

<p><img class="aligncenter" src="https://i.imgur.com/3VOC2zd.png" alt="Create a custom table in field group settings" width="1347" height="904" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

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

<p><img class="aligncenter" src="https://i.imgur.com/K0nd0vy.png" alt="Enter the name of the new custom table" width="1387" height="999" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>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 <code>wp_custom_company</code>.</p>

<p><img class="aligncenter" src="https://i.imgur.com/zolJh3a.png" alt="A new custom table shows up in the database of the WordPress website" width="1342" height="413" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>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 <code>wp_postmeta</code> table, so we need to use some code to move it to the new <code>wp_custom_company</code> table.</p>

<!-- /wp:paragraph -->

<!-- wp:heading -->

<h2>Step 2: Move the Data to the New Custom Table</h2>

<!-- /wp:heading -->

<!-- wp:paragraph -->

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

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p><strong>Note</strong>: This script runs on the backend and only the admin can run it.</p>

<!-- /wp:paragraph -->

<!-- wp:preformatted -->

<pre class="wp-block-preformatted">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 "

&lt;script&gt;

setTimeout( () =&gt; {

window.location.href = '$url';

}, 3000 );

&lt;/script&gt;

";

die;

}

add_action( 'admin_init', 'estar_child_data_company' );</pre>

<!-- /wp:preformatted -->

<!-- wp:paragraph -->

<p>In this code:</p>

<!-- /wp:paragraph -->

<!-- wp:list -->

<ul>

<li><code>estar</code> is the prefix name of the theme I'm using (eStar). You can download this free theme <a href="https://gretathemes.com/wordpress-themes/estar/" rel="follow">here</a>;</li>

<li><code>http://yourwebsite/wp-admin/?move-data-companies=1</code>: replace it with your own website URL;</li>

<li>We will run the script when the URL has the <code>move-data-companies=1</code> parameter. It means when you click the URL <em>http://yourwebsite.com/wp-admin/?move-data-companies=1</em>, it will automatically move the data.</li>

</ul>

<!-- /wp:list -->

<!-- wp:paragraph -->

<p>After that, we need to write these 2 functions:</p>

<!-- /wp:paragraph -->

<!-- wp:list -->

<ul>

<li><code>star_child_admin_records_get_companies()</code>: to take the list of posts</li>

<li><code>estar_child_move_data_company()</code>: to move data for each post</li>

</ul>

<!-- /wp:list -->

<!-- wp:paragraph -->

<p>Now, put this code to your <code>functions.php</code> file:</p>

<!-- /wp:paragraph -->

<!-- wp:preformatted -->

<pre class="wp-block-preformatted">function estar_child_admin_records_get_companies( $paged = 1 ) {

$args = [

'post_type'      =&gt; 'companies',

'posts_per_page' =&gt; 100,

'paged'          =&gt; $paged,

'fields'         =&gt; 'ids',

'orderby'        =&gt; 'ID',

];

$query = new WP_Query( $args );

return $query-&gt;posts;

}

function estar_child_move_data_company( $post_id ) {

global $wpdb;

echo 'Migrating company ', $post_id, '&lt;br&gt;';

$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-&gt;insert( 'wp_custom_company', $data );

}

$meta_key_array = [

'company_name',

'address',

'start_day',

];

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

$wpdb-&gt;query( $wpdb-&gt;prepare( "

DELETE FROM wpqq_postmeta

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

$post_id

) );

}</pre>

<!-- /wp:preformatted -->

<!-- wp:paragraph -->

<p><strong>Explanation</strong>:</p>

<!-- /wp:paragraph -->

<!-- wp:table -->

<figure class="wp-block-table">

<table>

<tbody>

<tr>

<td><strong>Code</strong></td>

<td><strong>Explanation</strong></td>

</tr>

<tr>

<td><code>'company_name', 'address', 'start_day'</code></td>

<td>The ID of 3 custom fields in the <strong>Companies</strong> post type</td>

</tr>

<tr>

<td><code>$wpdb-&gt;insert( 'wp_custom_company', $data ) </code></td>

<td>The function to insert data to the <code>wp_custom_company</code> table we created in the step 1</td>

</tr>

<tr>

<td><code>$wpdb-&gt;query( $wpdb-&gt;prepare( "DELETE FROM wpqq_postmetaWHERE post_id = %d AND meta_key IN ( $meta_key )",$post_id</code></td>

<td>This code is to delete the old data in the <code>wp_postmeta</code> table</td>

</tr>

<tr>

<td><code>'Companies'</code></td>

<td>The slug of the post type</td>

</tr>

<tr>

<td><code>'posts_per_page' =&gt; 100</code></td>

<td>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.</td>

</tr>

</tbody>

</table>

</figure>

<!-- /wp:table -->

<!-- wp:paragraph -->

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

<p><img class="aligncenter" src="https://i.imgur.com/M0JQhXM.png" alt="Enable moving the custom fields' data to the custom table with Meta Box plugin." width="856" height="248" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>After running the script, I go to the <code>wp_custom_company</code> custom table to check. And here is the result.</p>

<p><img class="aligncenter" src="https://i.imgur.com/JkBQPEK.png" alt="The old data of custom fields is on the new custom table" width="1074" height="562" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>As you can see, the custom fields' data of the <strong>Companies</strong> post type is now moved to the <code>wp_custom_company</code> table.</p>

<p><img class="aligncenter" src="https://i.imgur.com/tRqgKhA.png" alt="The custom fields' data of the Companies post type is moved to the custom table." width="723" height="322" /></p>

<!-- /wp:embed -->

<!-- wp:paragraph -->

<p>It also is deleted from the <code>wp_postmeta</code> table at the same time.</p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p>Ok, we've done it!</p>

<h2>Video Tutorial</h2>

<p>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.</p>

<p>https://youtu.be/JaEvtYa4Hcg</p>

<!-- /wp:paragraph -->

<!-- wp:heading -->

<h2>Final Words</h2>

<!-- /wp:heading -->

<!-- wp:paragraph -->

<p>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.</p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p>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.</p>

<!-- /wp:paragraph -->

<!-- wp:paragraph -->

<p>Good luck and have a "field" day!</p>

<!-- /wp:paragraph -->