Wordpress MYSQL Query - Search Post Titles Keywords, Change Their Category

[hayw0027]

Hi, iv found this great little php script posted Andrew Kantor at http://wordpress.org/support/topic/300069?replies=8 that allows you to search your wordpress posts for certain keywords and then inturn places those posts in a category you specify.

 

E.g. Search for posts that have the phrase "Article Marketing" in the title and place them in the "Article Marketing" category.

 

This is perfect for me to use on my article directory that contains 170,000 article posts, posted by people that don't know how to select a targeted category.

 

The only little problem with this script is that it doesn't replace the existing category, it just adds the category you choose to the post and the existing category also remains. Thus having the posts appear in 2 categories each.

 

I know very little about php and mysql queries, iv tried to work it out myself but have wasted about 3 hours doing so.

 

Can someone please take a look at this code and make adjustments so it removes the existing category and replaces it with the category that you desire.

 

Who ever solves this issue will be a God in my eyes for ever.

 

   1.
      <?php
   2.
      $username="MY_MYSQL_USERNAME";
   3.
      $password="MY_MYSQL_PASSWORD";
   4.
      $database="MY_DATABASE_NAME";
   5.
      $my_text = "/images";  // What I'm searching for
   6.
      $my_category = '8';    // The category to change it to
   7.
       
   8.
      // Connect to MySQL and the database and verify:
   9.
      mysql_connect(localhost,$username,$password) or die(mysql_error());
  10.
       
  11.
      echo "<p>Connected to MySQL.";
  12.
      mysql_select_db($database) or die(mysql_error());
  13.
      echo "<br />Connected to " . $database . "</p>";
  14.
       
  15.
      // Verify what we're looking for, for troubleshooting:
  16.
      echo "<p><b>Looking for " . $my_text . "</b></p>";
  17.
       
  18.
      // Get the ID field (which is WordPress's post
  19.
      // number) from any posts that have your text:
  20.
      $query = "SELECT ID FROM wp_posts WHERE post_title LIKE '%$my_text%'";
  21.
       
  22.
      // Take those results and go through them:
  23.
      $result = mysql_query($query) or die(mysql_error());
  24.
       
  25.
      // While there are results...
  26.
      while($row = mysql_fetch_array($result))
  27.
      {
  28.
      // Verify what we're doing -- changing post
  29.
      // number such-and-such...
  30.
      $thisPostHasIt = $row['ID'];
  31.
      echo "<p>Row " . $row['ID'] . " contains it, so...<br />";
  32.
       
  33.
      // In the wp_term_relationships table,
  34.
      // update the category number ("term_taxonomy_id")
  35.
      // with the category number you specified -- but only
  36.
      // in one of the "result" rows.
  37.
      // We look for "object_id" to equal one of those
  38.
      // rows. (The object_id field refers to the WordPress
  39.
      // post number, just as the ID field did. Why two
  40.
      // different names? Who knows?)
  41.
       
  42.
      mysql_query("UPDATE wp_term_relationships SET term_taxonomy_id='$my_category' WHERE object_id = '$thisPostHasIt'");
  43.
       
  44.
      // And tell us about it:
  45.
      echo "Changing post number " . $thisPostHasIt . " to category number ". $my_category . "</p>";
  46.
      }
  47.
      echo "<p><b>All done!</b></p>";
  48.
      ?>

[2levelsabove]

The code does appear to replace the categories. Hmmm what are you seeing?

[hayw0027]

The code does appear to replace the categories. Hmmm what are you seeing?

 

Hi mate,

 

Yes the script appears to do the right thing but when i used it on my article directory, It adds the new category but it also leaves the old category. So some of the articles are now associated with 2 categories which isn't what I want.

 

I think the query

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

may need some tweaking. Is it possible to make this a find and REPLACE query, would that solve my problem?