Ask your WordPress questions! Pay money and get answers fast! (more info)

How do I convert Custom Fields to a taxonomy? WordPress

  • SOLVED

I have several posts that have custom fields that I need converted into taxonomies. I am looking for someone to write the mysql statement for doing this.

I've tried using the Custom Field Taxonomy plugin, but it's outdated and doesn't work.

Thanks in advance!

Answers (3)

2013-04-24

Arnav Joy answers:

can you let us know fields you have in your posts


Matt comments:

I have fields like cp_additional_options with values of "Multiple Colors", "Photo Gallery", etc. that need to be changed to a taxonomy of "Popular Features" (in the database as pa_popular-features) with the appropriate term_id and term_taxonomy_id.


Arnav Joy comments:


try this

global $wpdb;
$sql = "SELECT post_id , meta_value FROM ".$wpdb->prefix."postmeta WHERE meta_key = 'cp_additional_options' ";
$res = $wpdb->get_results( $sql ) ;
if( !empty($res)){
foreach( $res as $r ){
$ret = wp_insert_term( $r->meta_value , 'Popular Features' );
if( !$ret->errors ){
$term_id = $ret['term_id'];
wp_set_post_terms( $r->post_id, $term_id, 'Popular Features');
}
}
}


Matt comments:

This didn't work. I put the code in my header.php and reloaded the homepage, but none of the cp_additional_options values are showing up as terms in the Popular Features taxonomy.


Arnav Joy comments:

you have to change name of your taxonomy at two places

$ret = wp_insert_term( $r->meta_value , 'Popular Features' );

wp_set_post_terms( $r->post_id, $term_id, 'Popular Features');

Popular Features is what you see is displayed at menu but you have to you actual name that you will get at url when you will click on it then at url you will see it like .......edit-tags.php?taxonomy=popular_feature or something similar so use this


Arnav Joy comments:

try this

global $wpdb;
$sql = "SELECT post_id , meta_value FROM ".$wpdb->prefix."postmeta WHERE meta_key = 'cp_additional_options' ";
$res = $wpdb->get_results( $sql ) ;
if( !empty($res)){
foreach( $res as $r ){
$ret = wp_insert_term( $r->meta_value , 'pa_popular-features' );
if( !$ret->errors ){
$term_id = $ret['term_id'];
wp_set_post_terms( $r->post_id, $term_id, 'pa_popular-features');
}
}
}


Matt comments:

Well... That kind of worked. It created many duplicates of each term. Do you know how to fix this?


Matt comments:

Also, it times out before it can convert all of the terms. But this is very close!


Arnav Joy comments:

I do not think so my code will create duplicate entries as "wp_insert_term" returns error if you try to insert term with the same name , so please delete all the terms first and then check my code it will work.

to increase execution time you can follow either of the following

1. By Wp-config.php Changes

set_time_limit(60);

2. In htaccess

php_value max_execution_time 60

3. Addition in php.ini file

max_execution_time = 30 ;

if that does not work then ask your hosting provider to increase the time limit.


Matt comments:

Alright thanks.


Matt comments:

For some reason this process seems to be skipping several items. I just ran it on another term and it worked properly, but it only moved 180 or so listings out of 3,800. Do you know why this might be? It didn't time out, but it still only moved a fraction of the fields.

2013-04-24

Daniel Yoen answers:

you mean like this ? :

meta_key = meta_value

meta_key (taxonomy name)
|_meta_value (taxonomy value)


Matt comments:

The custom fields are in the wp_postmeta table in the meta_key / meta_value columns

The taxonomies are in the wp_term_taxonomy table in the taxonomy / term_id columns... I think. Again, I'm not great with mysql and I don't know the difference between term_id and term_taxonomy_id nor do I quite understand the relationship between taxonomies and posts.

So for example, I have a custom field with meta_key cp_additional_options with a meta_value of "Photo Gallery" that needs to be changed to a taxonomy of pa_options and either a term_id of 3310 or term_taxonomy_id of 3759 or maybe both... I'm not really sure.

Does that help?


Daniel Yoen comments:

I think, you need custom taxonomy :

function add_custom_taxonomies()
{
register_taxonomy('pa_popular-features', 'post', array(
'hierarchical' => true,
'labels' => array(
'name' => _x('Popular Features', 'taxonomy general name'),
'singular_name' => _x('Popular Features', 'taxonomy singular name'),
'search_items' => __('Search Popular Features'),
'all_items' => __('All Popular Features'),
'parent_item' => __('Parent Popular Features'),
'parent_item_colon' => __('Parent Popular Features:'),
'edit_item' => __('Edit Popular Features'),
'update_item' => __('Update Popular Features'),
'add_new_item' => __('Add New Popular Features'),
'new_item_name' => __('New Popular Features Name'),
'menu_name' => __('Popular Features')
),
'rewrite' => array(
'slug' => 'pa_popular-features',
'with_front' => false,
'hierarchical' => true
)
));
}
add_action('init', 'add_custom_taxonomies', 0);


then, paste this line to header.php or index.php in theme folder

function generate_meta_keys()
{
global $wpdb;
$post_type = 'post';
$query = "
SELECT DISTINCT($wpdb->postmeta.meta_value)
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta
ON $wpdb->posts.ID = $wpdb->postmeta.post_id
WHERE $wpdb->posts.post_type = '%s'
AND $wpdb->postmeta.meta_key != ''
AND $wpdb->postmeta.meta_key NOT RegExp '(^[_0-9].+$)'
AND $wpdb->postmeta.meta_key NOT RegExp '(^[0-9]+$)'
";
$meta_keys = $wpdb->get_col($wpdb->prepare($query, $post_type));
return $meta_keys;
}
foreach (generate_meta_keys() as $metaval)
{
wp_insert_term($metaval, 'category');
}


Refresh your page, then check your backend in posts tab :-)

hope this help


Daniel Yoen comments:

sorry, revision

function generate_meta_keys()
{
global $wpdb;
$post_type = 'post';
$query = "
SELECT DISTINCT($wpdb->postmeta.meta_value)
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta
ON $wpdb->posts.ID = $wpdb->postmeta.post_id
WHERE $wpdb->posts.post_type = '%s'
AND $wpdb->postmeta.meta_key != ''
AND $wpdb->postmeta.meta_key NOT RegExp '(^[_0-9].+$)'
AND $wpdb->postmeta.meta_key NOT RegExp '(^[0-9]+$)'
";
$meta_keys = $wpdb->get_col($wpdb->prepare($query, $post_type));
return $meta_keys;
}
foreach (generate_meta_keys() as $metaval)
{
wp_insert_term($metaval, 'pa_popular-features'); // here
}


Daniel Yoen comments:

Note : after refreshing the page, you must remove the line(last code) from header.php or index.php


Matt comments:

I already have the taxonomy created since woocommerce creates them automatically, but it doesn't look like this worked.

I put the code in my header.php and reloaded the homepage, but none of the cp_additional_options values are showing up as terms in the Popular Features taxonomy.


Daniel Yoen comments:

replace with your own taxonomy name:

wp_insert_term($metaval, 'replace with your taxonomy name');

hope this help :-)


Matt comments:

The taxonomy name that you had in your example was correct (pa_popular-features), so I'm not sure what's going on.

I'm just supposed to copy and paste this within php tags within the header and then re-load the home page right?


Daniel Yoen comments:

The code should work (I've tried the code), unless we are in misunderstanding. lol :-)

could you add some screenshot of custom taxonomy(backend) to your question ? :-)

Best,
Daniel


Matt comments:

Alright here's a screenshot of the custom fields and where I'm trying to convert them to.

Hope this helps