hayw0027 Posted April 2, 2010 Share Posted April 2, 2010 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. ?> Quote Link to comment https://forums.phpfreaks.com/topic/197344-wordpress-mysql-query-search-post-titles-keywords-change-their-category/ Share on other sites More sharing options...
2levelsabove Posted April 2, 2010 Share Posted April 2, 2010 The code does appear to replace the categories. Hmmm what are you seeing? Quote Link to comment https://forums.phpfreaks.com/topic/197344-wordpress-mysql-query-search-post-titles-keywords-change-their-category/#findComment-1035966 Share on other sites More sharing options...
hayw0027 Posted April 2, 2010 Author Share Posted April 2, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/197344-wordpress-mysql-query-search-post-titles-keywords-change-their-category/#findComment-1036197 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.