WordPress.org

1. AndrewKantor
   Member
   Posted 2 years ago #

   Hey, folks -- I'm hoping there's a MySQL guru who can help me with this. I want to create a query that will look for a string in my posts, and -- if that string appears -- put that post in a particular category.

   I thought I could figure it out, but because of the way WP stores categories among the terms, term_relationships, and term_taxonomy tables, I couldn't.

   To be more specific, I'd like to search my posts for any occurrence of the word "embed" and put those posts in the "video" category.

   Is this possible? If someone can offer a MySQL query to do it, or point me in the direction to figure it out, I'd be grateful!

2. AndrewKantor
   Member
   Posted 2 years ago #

   I think this is what I need to do, but I don't know SQL nearly well enough:

   Search through the wp_posts table for any entry where post_content contains "search_string."

   For each entry where "search_string" occurs, get the entry's ID, then find that ID in the wp_term_relationships table (where it's called "object_id"). Then change that entry in wp_term_relationships so that its term_taxonomy_id is, for example, 4.

   Does that make sense to anyone but me?

3. hedonplay
   Member
   Posted 2 years ago #

   Hello, AndrewKantor, I am currently working on the WP database.

   From the the change log of version 2.8, we can see that the post_category filed is deleted from the wp_posts table. So it makes your problem a little more complicated. But in theory, it's completely doable.

   I suppose that the video category already exists.

   Firstly you have to retrieve all the posts that contain the given string, "embed", in your case. The code looks like:
   

   SELECT ID
   FROM 'wp_posts'
   WHERE 'post_content' LIKE '%embed%'

   Secondly, create the new relationship between posts and category. In this step, a loop and an update query in it are needed. The term_taxonomy_id in the wp_term_relationships should be updated depending on the object_id which you could treat as your post_id.

   Voila, hopefully you can find the solution.

4. shirgans
   Member
   Posted 2 years ago #

   hi,

   I would think of (a bit long but understood):
   

   $my_text = 'blah blah'; //put text here
   $desired_category = 'video'; //name of the new category you wish to add
   
   $result = mysql_query("SELECT term_id FROM terms WHERE name='$desired_category' LIMIT 1");
   while ($row = mysql_fetch_array($result)){
   $my_category = $row['term_id']; //selects the "category id"
   }
   
   $result = mysql_query("SELECT ID FROM posts WHERE post_content LIKE '%$my_text%' ");
   // select all posts that have your text
   
   while ($row = mysql_fetch_array($result)){
   $my_post_id = $row['ID'];
   
   $result = mysql_query("REPLACE INTO term_relationships (object_id, term_taxonomy_id) VALUES ($my_post_id, $my_category)");
   // add category to that post
   
   }//while have posts

   I havn't try it of course, but i guess it's kind of a good start
   Good luck

5. AndrewKantor
   Member
   Posted 2 years ago #

   Wonderful! Between the two of you, I should have enough to go on -- and if it's not, it's enough for me to figure it out with a MySQL/PHP reference. Thank you!

   (Hmm... wonder if it's worth making this into a plugin if it works. Could be useful if you could choose where to search, what to search for, and what to do with entries that match.)

6. AndrewKantor
   Member
   Posted 2 years ago #

   I'm so close to getting it to work, but I'm not quite there. The script runs, and appears to update, but it doesn't actually change the values. I'm guessing it's something small I didn't code right.

   Here's what I have:

   <?php
   $username="myname";
   $password="mypass";
   $database="mydatabase";
   $my_text = "needle";        //what I'm searching for
   $my_category = '8';        //whatever category number it is
   
   mysql_connect(localhost,$username,$password) or die(mysql_error());
   mysql_select_db($database) or die(mysql_error());
   
   $result = mysql_query("SELECT ID FROM wp_posts WHERE post_content LIKE '%$my_text%' ");
   // select all posts that have your text
   
   while ($row = mysql_fetch_array($result))
   {
   mysql_query("UPDATE wp_term_relationships SET term_taxonomy_id=$my_category");
   // add category to that post
   }
      //while have posts
   ?>

   Any ideas? Do I need a FOR loop? Thanks!

7. AndrewKantor
   Member
   Posted 2 years ago #

   GOT IT!

   Here's how I was able to go through all my posts and look for specific text -- "/images" -- in them. When I found a post containing "/images", I changed the category number to "8" (which corresponds to my category "Photos").

   <?php
   $username="MY_MYSQL_USERNAME";
   $password="MY_MYSQL_PASSWORD";
   $database="MY_DATABASE_NAME";
   $my_text = "/images";  // What I'm searching for
   $my_category = '8';    // The category to change it to
   
   // Connect to MySQL and the database and verify:
   mysql_connect(localhost,$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>";
   ?>

   Thanks for all your help! Next step: Adding a clean HTML form interface. Allowing multiple changes (if this OR that). Changing more than just category (add a tag, for example).

8. hedonplay
   Member
   Posted 2 years ago #

   Well Done, Andrew!

   I have also archived my goal which is to move the posts in a different CMS's database into the WP's.

   Cheers!

9. smacavelly
   Member
   Posted 1 year ago #

   AndrewKantor,
   How has this effected permalinks? Do you have to regenerate them after you update the categories? I have found your post very useful & thank you for your efforts.

10. smacavelly
    Member
    Posted 1 year ago #

    AndrewKantor, Did you make this a plug-in yet? I can see where it would very helpful. I added hostname for easy copy & paste use.
    I set this up using a cron job because I wanted post that I post with pictures to place into a separate cat then posts without pictures. This works like a charm thanks again...

    <?php
    $username="MY_MYSQL_USERNAME";
    $password="MY_MYSQL_PASSWORD";
    $hostname="MY_DATABASE_HOSTNAME"; //Your Database hostname usually
    $database="MY_DATABASE_NAME";
    $my_text = "/images";  // What I'm searching for
    $my_category = '8';    // The category to change 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>";
    ?>

Topic Closed

This topic has been closed to new replies.