WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] MySQL query to search posts and change categories? (10 posts)

  1. AndrewKantor
    Member
    Posted 4 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 4 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 4 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 4 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 4 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 4 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 4 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 4 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 4 years 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 4 years 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.

About this Topic