When you delete a user in CB the messages are still stored in your database.
I have already posted a script which allows to purge all messages from a certain user, this script checks all non-existing user ids and deletes all messages found (strictly speaking they are sent to the trash folder).
[code:1]<?php
$hostname = "localhost";
$database = "xxxxxxxxxxxx";
$username = "xxxxxxxxxxxx";
$password = "xxxxxxxxxxxx";
$link = mysql_pconnect($hostname, $username, $password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database, $link);
?>
<html>
<head>
<title>Delete messages from orphaned users</title>
</head>
<body>
<h2>Delete messages from orphaned users</h2>
<form action="<?php echo $_SERVER;?>" method="post" enctype="multipart/form-data">
<input type="submit" name="check" value="Check" />
<input type="submit" name="delete" value="Delete" /><br/>
</p>
</form>
<?php
if (!isset($_POST["delete"]) && !isset($_POST["check"])) {
exit;
}
if (isset($_POST["delete"]) || isset($_POST["check"])) {
if (isset($_POST["check"])) {
echo("Checking...
");
}
$query = "SELECT min(id) FROM jos_users";
$result = mysql_query($query, $link) or die(mysql_error());
$mmin = (int)mysql_result($result, 0, 0);
$query = "SELECT max(id) FROM jos_users";
$result = mysql_query($query, $link) or die(mysql_error());
$mmax = (int)mysql_result($result, 0, 0);
for ($i=$mmin;$i<=$mmax;$i++) {
$query = "SELECT count(id) FROM jos_users WHERE id=".(int)$i;
$result = mysql_query($query, $link) or die(mysql_error());
$value = (int)mysql_result($result, 0, 0);
if ($value==0) {
$query = 'SELECT COUNT(id) FROM `jos_uddeim` WHERE fromid='.$i;
$result = mysql_query($query, $link) or die(mysql_error());
$mvon = (int)mysql_result($result, 0, 0);
$query = 'SELECT COUNT(id) FROM `jos_uddeim` WHERE toid='.$i;
$result = mysql_query($query, $link) or die(mysql_error());
$man = (int)mysql_result($result, 0, 0);
if ($mvon>0 || $man>0)
echo("<b>ID=$i not found. Messages (from/to): $mvon/$man</b><br>");
else
echo("ID=$i not found. Messages (from/to): $mvon/$man<br>"«»);
if (isset($_POST["delete"]) && ($mvon>0 || $man>0)) {
echo(" delete all preferences from $i<br>");
$query = "DELETE FROM `jos_uddeim_emn` WHERE userid=".$i;
$result = mysql_query($query, $link) or die(mysql_error());
echo(" delete blocking of $i<br>");
$query = "DELETE FROM `jos_uddeim_blocks` WHERE blocker=".$i." OR blocked=".$i;
$result = mysql_query($query, $link) or die(mysql_error());
$deletetime=time();
echo(" trash all messages sent to $user in sender's outbox and $i's inbox<br>");
$query = "UPDATE `jos_uddeim` SET totrashoutbox=1, totrashdateoutbox=".$deletetime.", totrash=1, totrashdate=".$deletetime." WHERE toid=".$i;
$result = mysql_query($query, $link) or die(mysql_error());
echo(" trash all messages sent from $user in $i's outbox and receiver's inbox<br>");
$query = "UPDATE `jos_uddeim` SET totrashoutbox=1, totrashdateoutbox=".$deletetime." WHERE fromid=".$i;
$result = mysql_query($query, $link) or die(mysql_error());
}
}
}
}
?>
</body>
</html>
[/code:1]
Post edited by: slabbi, at: 2007/09/28 13:15
uddeIM & uddePF Development
CB Language Workgroup
CB 3rd Party Developer
