2

I have a site with 900+ posts, each with about 3-5 categories. I would like to copy each of the categories to be a tag and am looking for the SQL code to do just that. I do not want to delete or overwrite the categories. (I assume that's the best thing to do for SEO purposes?)

I am having a hard time figuring out exactly what needs to be INSERTed.

What I have so far... It's the comments at the end I need help with.

global $wpdb;
$query = "
SELECT $wpdb->posts.ID, $wpdb->term_relationships.object_id, $wpdb->term_relationships.term_taxonomy_id, $wpdb->term_taxonomy.term_taxonomy_id, $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug
FROM $wpdb->posts
LEFT JOIN $wpdb->term_relationships ON
($wpdb_posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON
($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON
($wpdb->term_taxonomy.term_taxonomy_id = $wpdb->terms.term_id)
WHERE $wpdb->posts.post_status = 'publish'
AND $wpdb->posts.post_type = 'post'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->terms.name NOT LIKE '*%'
ORDER BY post_date DESC
";

$results = $wpdb->get_results($query);

$insert = "";

$wpdb->query($insert);

-- Next steps:
-- LOOP? {
--  INSERT INTO $wpdb->terms (name, slug)
--  VALUES (terms.name, terms.slug)
-- }
-- SELECT new term_id's for the above inserted names and slugs
-- LOOP? {
--  INSERT INTO $wpdb->term_taxonomy (term_id, taxonomy)
--  VALUES (terms.term_id, post_tag)
-- }
-- SELECT new term_taxonomy_id's for the above inserted term_id's
-- LOOP? {
--  INSERT INTO $wpdb->term_relationships (term_taxonomy_id, object_id)
--  VALUES (term_taxonomy.term_taxonomy_id, post_id)
-- }
6
  • I really do think it is a bad idea to use a custom SQL query to change terms from hierarchical to non hierarchical. There are a few issues which can cause you a couple of really bad headaches Commented Dec 16, 2015 at 17:11
  • OK - what exactly is hierarchical vs. non-hierarchical, in this situation? Also, do you have a better solution?
    – gram
    Commented Dec 16, 2015 at 17:14
  • I think I misunderstood something here, do you want to change categories to tags, or simply take each category, duplicate it but instead of making the duplicate a category, you make it a tag Commented Dec 16, 2015 at 17:18
  • BTW, hierarchical taxonomies like category have terms which can have parents and children, non hierarchical taxonomies like post_tag have terms that does not have parents or children Commented Dec 16, 2015 at 17:19
  • I want to duplicated the categories, and make them tags. I want to keep the categories, as is, for SEO purposes. But I also want to have them as tags.
    – gram
    Commented Dec 16, 2015 at 17:21

2 Answers 2

5

As I said, we would rather use native functions here, which is safer and already does all the hard work for you. We need to be very careful here as this is a very expensive operation to run. Not doing this correctly can crash your site due to timing out

Your worksflow is also wrong here. I would first create all the tags, and after that, insert the tags into the posts. You are trying to do all at once.

Lets us first grab all the categories and use that info to create tags. Just note:

  • post_tag being a non hierarchical taxonomy cannot have any duplicate names, hierarchical taxonomies like category can have duplicate term names within different hierarchies. If you have categories with same names, you will get a WP_Error object back if you are going to try to insert tags with duplicate names. To avoid that, we will simply check if a tag exists before we try to insert it

  • Tags cannot have parent/child relationships, categories can. So when we duplicate a category to be a term, we should set all parents explicitly to 0

Here is the code: (NOTE: We would require PHP 5.4+)

// Lets create our tags. We will use wp_insert_term hooked to init
add_action( 'init', function ()
{
    // Get all categories
    $categories = get_categories( ['hide_empty' => 0] );

    // Probably totally unnecessary, but check for empty $categories
    if ( !$categories )
        return;

    // Loop through the categories and create our tags
    foreach ( $categories as $category ) {
        // First make sure that there is no tag that exist with the same name
        if ( term_exists( $category->name, 'post_tag' ) )
            continue;

        // Set our arguments for our terms from $category
        $args = [
            'description' => $category->description,
            'parent'      => 0, // We can drop this, default is 0
            'slug'        => $category->slug,
        ];
        wp_insert_term( $category->name, 'post_tag', $args );
    } //endforeach
}, PHP_INT_MAX );

You should now see these tags in your Tag admin page. You can also remove the code as it is no more necessary.

Now that we have our tags created, we need to add them to our posts. Here we would need to be careful as you are talking about 900+ posts, so we do not want to break the bank.

What we will do is, we will query all of those posts, but only the post ID's which saves a lot on resources. Also, we will use get_the_category which are cached, so it does not require extra db calls, wp_get_post_categories are not cached, so it is really expensive

Due to wp_set_post_terms being expensive, you would maybe need to split the following into a couple of queries, but I believe one query should be enough

Lets attach the tags to our posts

add_action( 'template_redirect', function ()
{
    // Get all our posts
    $args = [
        'posts_per_page' => -1,
        'fields'         => 'ids', // Make the query lean
        // Add any additional query args here
    ];
    $q = new WP_Query( $args );

    if ( !$q->have_posts() )
        return;

    while ( $q->have_posts() ) {
    $q->the_post();

        // Get all the categories attached to the post
        $categories = get_the_category();
        if ( !$categories )
            continue;

        // Get the names from the categories into an array
        $names = wp_list_pluck( $categories, 'name' );
        // Loop through the names and make sure that the post does not already has the tag attached
        foreach ( $names as $key=>$name ) {
            if ( has_tag( $name ) )
                unset ( $names[$key] );
        }
        // Make sure we still have a valid $names array
        if ( !$names )
            continue; 

        // Finally, attach our tags to the posts
        wp_set_post_terms( 
            get_the_ID(), // Post ID 
            $names, // Array of tag names to attach
            'post_tag',
            true // Only add the tags, do not override
        );
    }
    wp_reset_postdata();
});

Your posts should now have tags attached to them matching the categories. You can also now remove the function as we are done

1
  • Just for interest sake, do you have any feedback on this issue. Would be interesting to know what you have used to solve this Commented Dec 20, 2015 at 18:24
1

You requested SQL solution but SQL is not (yet) my speciality but I might still help you. SQL also seems pointless in your case because WordPress provides all nessecary tools to achieve this.

I don't recommend to run this more than once if you remove comments from the end of code. You'll understand when you'll get there.

This code "copies" all the categories of all your posts and saves them as tags. It doesn't delete categories and it also doesn't set new tags to posts. If you also want to set tags to posts, let me know.

PS! It only works correctly if categories are not hierarchical aka without parents!

Please let me know if there are any problems. This code should not do any harm as far as you don't remove comments from the end of the code.


<?php
global $post;

//Get ALL the posts with query
$args = array(
              'post_type' => 'post',
              'post_status' => 'any'   //Change to 'publish', 'draft' or 'pending' if you don't want to get ALL post STATUSES
            );

$post_query = new WP_Query( $args );

//Declare new array of our categories which eventually become tags
$new_tags = array();

while ($post_query->have_posts()) : $post_query->the_post();

     //Get all the categories names of the post and save as an array variable
     $post_categories = wp_get_post_categories($post->ID, array('fields' => 'names') );

     //Loop through all categories
     foreach ( $post_categories as $category ) {

            //Check if category is already in array
            if ( ! in_array($category , $new_tags ) ) {

                 //Push category to array if it isn't in array yet
                 array_push( $new_tags, $category );
            }
     }

endwhile;
wp_reset_postdata();

//VERY IMPORTANT - test first without making new tags and check if everything seems to be correct 
//PS! Compare the count of items in array (starts with 0 - it's 1 less because of that) with category count in WP admin area
print_r($new_tags);


//Add new tags programmatically - don't do it if you're sure that array is correct!!
/*
foreach ( $new_tags as $tag ) {
      wp_insert_term( $tag, 'post_tag' );
} 
*/
?>
9
  • Thank you! I was actually just reading chapter 6 in Professional Wordpress Design and Development and it is all about using PHP and built-in WP functions to query and update the database.
    – gram
    Commented Dec 16, 2015 at 17:24
  • This is extremely extremely expensive Commented Dec 16, 2015 at 17:27
  • expensive in time or in memory? Also, this appears to only create a single instance of each category as a tag, whereas I want each post to have each category as a tag, even if there are multiple posts with each category to begin with.
    – gram
    Commented Dec 16, 2015 at 17:29
  • It is but unfortunately I can't think of other automated way to "copy" all categories and make tags out of them. This should only be run once and you should choose a time when most people/visitors are asleep.
    – N00b
    Commented Dec 16, 2015 at 17:31
  • Also note to @gram - you should not have that many different categories or even tags or the purpose of them is depleted. Every post should not have it's own unique tags. These are just tools of grouping posts in order to make navigation for visitors easier, sorting for you easier and a little boost in SEO.
    – N00b
    Commented Dec 16, 2015 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.