MySQL Query that looks for post with Custom Field, then changes Category

Question

I'm looking for a MySQL Query that will look for all the posts with custom field "meta_key_I_want_to_find", then if the custom field is detected, add a category to that post. The category has already been created, so I just need to find all of the posts with that Meta_key and then add the category to them.

I was thinking something along the lines of this:

    <?php
    $username="MY_MYSQL_USERNAME";
    $password="MY_MYSQL_PASSWORD";
    $hostname="MY_DATABASE_HOSTNAME"; //Your Database hostname usually
    $database="MY_DATABASE_NAME";
    $my_text = "meta_key_I_want_to_find";  // What I'm searching for
    $my_category = '8';    // The category to add it to

    // Connect to MySQL and the database and verify:
    mysql_connect($hostname,$username,$password) or die(mysql_error());

    echo "<p>Connected to MySQL.";
    mysql_select_db($database) or die(mysql_error());
    echo "<br />Connected to " . $database . "</p>";

    // Verify what we're looking for, for troubleshooting:
    echo "<p><b>Looking for " . $my_text . "</b></p>";

    // Get the ID field (which is WordPress's post
    // number) from any posts that have your text:
    $query = "SELECT ID FROM wp_posts WHERE post_content LIKE '%$my_text%'"; 

    // Take those results and go through them:
    $result = mysql_query($query) or die(mysql_error());

    // While there are results...
    while($row = mysql_fetch_array($result))
    {
    // Verify what we're doing -- changing post
    // number such-and-such...
    $thisPostHasIt = $row['ID'];
    echo "<p>Row " . $row['ID'] . " contains it, so...<br />";

    // In the wp_term_relationships table,
    // update the category number ("term_taxonomy_id")
    // with the category number you specified -- but only
    // in one of the "result" rows.
    // We look for "object_id" to equal one of those
    // rows. (The object_id field refers to the WordPress
    // post number, just as the ID field did. Why two
    // different names? Who knows?)

    mysql_query("UPDATE wp_term_relationships SET term_taxonomy_id='$my_category' WHERE object_id = '$thisPostHasIt'");

    // And tell us about it:
    echo "Changing post number " . $thisPostHasIt . " to category number ". $my_category . "</p>";
    }
    echo "<p><b>All done!</b></p>";

//found on http://wordpress.org/support/topic/mysql-query-to-search-posts-and-change-categories
?>

...only instead of looking for a string and replacing it, I need to be able to find all posts with a certain meta_key and then add a category to that.

Any suggestions are much appreciated!

Answer 1

I can't tell here whether or not you have the WordPress API available to you. (Is this code that is being run from within an instance of WordPress, or is it being run by an external script on the WordPress database, without bootstrapping WordPress itself?)

If you have the WordPress API, it is easy to get a list of all posts that have a postmeta value with a given key. Thus:

<?php
$q = new WP_Query(array(
    "meta_key" => "meta_key_i_want_to_find"
));
while ($q->have_posts()) : $q->the_post();
     // Do something here. E.g.: $posts[] = get_the_ID(); to collect IDs
endwhile;
wp_reset_query();

// If you just collected above, now do something with it...

Again, if you have the WordPress API available to you, you can add a category to a post using wp_set_post_terms().

If you don't have the WordPress API available to you, you can get a representation of the post data you need using:

-- Modify to SELECT all and only the fields you intend to use.
SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON 
wp_posts.ID=wp_postmeta.post_ID WHERE (meta_key='meta_key_i_want_to_find');

Loop through the IDs, adding category relationships manually as you go along.