How to make category for word post_content

Question

I have a problem! I have my website with wordpress. I only have one category and I want to have 5 categories. The problem is that I have published more than 7000 post. How can I use a word of the content of the post and relationship to one of the new categories.

Example cat (word of the Post_content) ----> cat (new category)

Please, How is the "SQL queries"? Thanks

Answer 1

I personally find it easier to use built-in WP functions rather than running straight MySQL queries, so here's one option:

<?php
global $wpdb;
// replace 'yoursearchword' with whatever you're looking for.
// the two % symbols are to find the word no matter what comes before or after.
$query = "SELECT * FROM wp_posts WHERE post_content LIKE %yoursearchword%";
$posts = $wpdb->get_results($query);
foreach($posts as $post) {
    // replace with the desired category ID number.
    wp_set_post_categories($post->ID, array(1));
}
?>

For SEO purposes it's best to only assign one category per post, which is what this code achieves. Each time you run it, if your search word is found, all categories will be overwritten by just the one you are assigning. If instead you would like posts to be able to live in multiple categories, add true and the function will append (add additional) categories instead of overwriting.

wp_set_post_categories($post->ID, array(1), true);

Answer 2

I would not advise you to use a direct SQL approach as you expose yourself to all kinds of data corruption.

Your best bet would be to create a PHP function that would only be run once and do the job. You might run into some timeout issues, but those can be overcome by temporarily setting a really high PHP timeout value.

First of all, you should make sure that the new categories are properly created in WP admin. Then remember their IDs in an array.

To get you started, this is the function that I would use, hooked into WP admin dashboard so it only fires when you go to that page.

function wp1234_attach_posts_to_new_categories() {
    $categories_mapping = array(
        'targetword1' => '101', //this is a category ID
        'targetword2' => '102', //this is another category ID
        'targetword3' => '103', //this is yet another category ID
    );

    // Grab all the posts and cycle through them
    $args = array(
        'post_type' => 'post',
        'posts_per_page' => -1,
        'post_status' => 'publish',
        'suppress_filters' => true,
        'ignore_sticky_posts' => true,
        'no_found_rows' => true,
        'cache_results' => false, // there is no need to cache this
    );
    $new_query = new WP_Query();
    foreach ( $new_query->query( $args ) as $post ) {
        if ( ! empty( $post->post_content ) ) {
            foreach ( $categories_mapping as $word => $category_id ) {
                if ( false !== strpos( $post->post_content, $word ) ) {
                    // We have found the word in the content
                    // Attach the post to the category, by appending to existing categories - the last parameter
                    wp_set_post_categories( $post->ID, array( absint( $category_id ) ), true );

                    // We will stop at the first found word
                    break;
                }
            }
        }
    }

    // Leave a sign that we have done something
    print( PHP_EOL . '### ATTACHED POSTS TO CATEGORIES ###' . PHP_EOL );
}
// This action gets run only on the WP admin dashboard page
add_action( 'wp_dashboard_setup', 'wp1234_attach_posts_to_new_categories' );

After you are done, remove the add_action at least so you don't hammer your site.

Let me know if this helps.