Chris Fay & Jennie Fay’s Webportal

<?php
/*
 * cleanseWordpressPosts.php
 * Script used to re-insert posts information missing in wp_term_relationships to fix category issues
 * Written by: Chris Fay
 *9/15/2008
 *
 * To Use: First backup your database!
 * Copy the contents of this script to a file called updateCat.php
 * Upload updateCat.php to your web server
 * Verify the script is set to run in test mode first to see what data would be inserted
 * Browse to the script on the web server
 * Change testmode to false to allow script to update database   
 */
 
$dbhost = 'hostname'; //usually localhost
$dbuser = 'dbUserNameHere'; //replace dbUserNameHere with your username
$dbpass = 'dbPasswordHere'; //replace dbPasswordHere with your user's password
$dbname = 'dbName'; //replace dbName with the database name you want to connect to
define('TESTMODE', true); //leave true to get information on what would be updated - the database
						  //will not be touched if set to true - change to false to allow the script to modify the datbase
define ('CATEGORYID', 1); //the category id you would like all posts missing category info to have
 
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');
mysql_select_db($dbname);
 
//loop through each record, cleanse it, then re-insert it
$query  = "SELECT id FROM wp_posts";
$query2 = "SELECT object_id FROM wp_term_relationships";
$totalUpdates = 0;
 
$result = mysql_query($query);
 
if(TESTMODE) {
	echo "**** TEST MODE: No data will be modified in your database ****<br /><br />";
}
 
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{	
	$id = $row['id']; 
	echo "Checking id: $id<br />";	
	$match = false;	
	$result2 =  mysql_query($query2);	
 
	while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC))
	{
		if($id === $row2['object_id'])					
		{
			echo "Found match for id: " . $id . " - skipping. <br /><br />";
			$match = true;
			//no need to keep looping through since an item already exists
			break;						
		}
	}  
 
	if(!$match)
	{
		echo "No match found for id: ". $id . " - processing new category information<br />";
		$totalUpdates++;
 
		if(TESTMODE) 
		{
			$query3 = "INSERT into wp_term_relationships VALUES ($id,".CATEGORYID.",0)";	  	
			echo "The insert query we'd be using:<br /> $query3<br /><br />";				
		} 
		else
		{
			//update wp_term_relationships with missing id info
			echo "Creating category info for ID: $id. <br>";
 
			$query3 = "INSERT into wp_term_relationships VALUES ($id,".CATEGORYID."5,0)";	  					
			mysql_query($query3) or die('Error, insert query failed');
			echo "Category info updated successfully<br /><br />";	    				
		}
	}
 
} //end while
 
echo "Total updates: <strong>$totalUpdates</strong> <br/>All finished...<br />";
 
mysql_close($conn);
?>