SQL: change post custom field to CPT post taxonomy

  • Hello,

    the setup is as follow:
    I have regular posts with a custom field: actor = ‘tom hanks’
    for advanced management I now created a custom post type (actors) and added “tom hanks” as a post within that actors taxonomy.
    Via plugin ‘Advanced custom fields’ I connected the custom taxonomy (actors) to the regular posts so I can select ‘tom hanks’ on the regular post page.

    This can be done manually (check the custom field value and click the according actors category) but there are way more than 1500 posts and many other similar custom field values.

    Do you have any idea how that can be solved via SQL query or function?

    in one line:
    get customfield ‘actors’ and move to taxonomy(‘actors’) for that post.

    If somebody can please help me out with the query for a mysql or similar you would definitely make my day ??

    Thanks for taking the time.
    gerald

Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator bcworkz

    (@bcworkz)

    I’m unsure how to write a query, but here’s what needs to happen. Find the term in the terms table and get it’s term_id. Find the term_id corresponding to the correct taxonomy in the term_taxonomy table and get the term_taxonomy_id. Enter the post id as object id and the term_taxonomy_id in the term_relationships table. Increment the count column in term_taxonomy by the number of objects added to term_relationships for any particular term_taxonomy_id.

    Because it’s critical that the count is correct, you might consider looping through a custom PHP script and add terms one by one to ensure all the relationships are correctly maintained. If you do go the query route, be sure you backup your DB beforehand as there’s a fair chance of breaking something.

    What’s not clear to me is how does a query or script know which term to add to which post?

    Thread Starter gerald@WPcustoms

    (@geeman12030)

    wp_update_post is the solution.
    We wrote some lines over at stackexchange. I couldn`t test it yet but it looks promising.
    if anybody is interested (take care with the delete_post_meta field – not reversable)

    // get all post IDs
$post_ids = get_posts(
  array(
    'post_type' => 'post',
    'post_status' => 'publish',
    'numberposts'   => -1, // get all posts.
    'fields'     => 'ids', // Only get post IDs
  )
);

// info: custom field 'actor' = 'Tom Hanks'
$customfield_value = get_post_meta($post_ids, 'actor', true);
// change the custom field values to slug format:  'Tom Hanks' > 'tom-hanks'
$customfield_value = sanitize_title_with_dashes($customfield_value);

// Update all posts
foreach ($post_ids as $id) {
  $post = array(
    'ID' => $id,      // select all posts - not sure if that works with the array from $post_ids.
    'tax_input'      => array(
      'actor' => array(
        $customfield_value
      )
    )
  );
  // Update the post into the database
  wp_update_post( $post );

  // delete the old post meta after we moved it to the taxononmy
  delete_post_meta($id, 'actor');
}

    credits to ‘s_ha_dum’

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘SQL: change post custom field to CPT post taxonomy’ is closed to new replies.