UBB.threads PHP Forum Software Community Forums UBB.threads Version 7/8 Support mass delete Topics that do not contain specified keywords

<?
$hostname = "localhost";
$database = "mydatabase";
$username = "myusername";
$password = "mypassword";
$jc = mysql_connect($hostname, $username, $password) or trigger_error(mysql_error(), E_USER_ERROR);
mysql_select_db($database, $jc); 

// get a list of unique topic numbers that contain the keywords
// your keywords will of course be different. hopefully.
$query = "SELECT DISTINCT TOPIC_ID FROM ubbt_POSTS WHERE 
					POST_BODY LIKE '%batam%' OR 
					POST_BODY LIKE '%bintan %' OR 
					POST_BODY LIKE '%karimun%'  OR 
					POST_BODY LIKE '%sentosa%' OR 
					POST_BODY LIKE '%nagoya%'  OR 
					POST_BODY LIKE '%tanjung pinang%'  OR 
					POST_BODY LIKE '%free trade zone%'  
					order by TOPIC_ID";
					
$list = mysql_query($query, $jc) or die(mysql_error($jc));

// stuff all the "good" topics into an array.
$tp = array();
while (($data = mysql_fetch_assoc($list))) $tp[] = $data['TOPIC_ID'];

// select all the post ids from the POSTS table
$query = "SELECT TOPIC_ID FROM ubbt_POSTS WHERE 1 ORDER BY TOPIC_ID";
$list = mysql_query($query, $jc) or die(mysql_error($jc));
$data = mysql_fetch_assoc($list);

$count = 0;
$keepcount = 0;
do { 
	$count++;
	$topic = $data['TOPIC_ID'];
	// if the topic number is not found in the list of "good" topics, then delete all posts with that topic id
	if (in_array($topic, $tp)) $keepcount++;
	else {
		mysql_query("DELETE FROM ubbt_POSTS WHERE TOPIC_ID=$topic", $jc) or die(mysql_error($jc));
	}  
} while ($data = mysql_fetch_assoc($list)); 

echo "<b><br />\nPosts: $count Posts Processed\n<br />\n$keepcount Posts Preserved\n<br /></b>\n";

// now we have to fix up the TOPICS table, which will contain lots of orphan topic ids; code is very similar to above
$query = "SELECT TOPIC_ID FROM ubbt_TOPICS WHERE 1 ORDER BY TOPIC_ID";
$list = mysql_query($query, $jc) or die(mysql_error($jc));
$data = mysql_fetch_assoc($list);

$count = 0;
$keepcount = 0;
do { 
	$count++;
	$topic = $data['TOPIC_ID'];
	if (in_array($topic, $tp)) $keepcount++;
	else {
		mysql_query("DELETE FROM ubbt_TOPICS WHERE TOPIC_ID=$topic", $jc) or die(mysql_error($jc));
	}  
} while ($data = mysql_fetch_assoc($list)); 

echo "<b><br />\nTopics $count Records Processed\n<br />\n$keepcount Topics Preserved\n<br /></b>\n";
?>