MySQL query to search posts and change categories?

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?

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.

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

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.)

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!

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).

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!

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.

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>";
?>