Clearing old messages

[theworld]

I've seen this issue pop up around here a few times - where the older chat messages will be visible. Last night a created a solution that works for me, so I thought I would share it here.

First, I am using version 1.x and I using the MySQL container, so my messages are stored in a database.

Create a php script - I called mine purge.php

The script uses a function to return the elapsed time from NOW and the timestamp on the data record. You may want to modify this function to suit yourself. A connection is established to the database, and each record is read. If the elapsed time is greater than 20 minutes the the record is deleted. The script ends by Optimizing the table. You can set up the elapsed time interval at this line in the script: if ($result > 20 ) {
I have set mine for 20 minutes

You can run this script interactively, but why? Run it as a cron if possible. Here is the command that I used to run as a cron job. Set a time interval that you like.

/usr/local/bin/php -q /home/domain/public_html/path/to/purge.php


PURGE.PHP
<?php
/**Function when($ts) takes timestamp on input and returns
* human readable time difference. Example output:
* I am running my script in 20 minute intervals to clear the
* database and remove overhead.
**/

function kiedy($ts) {
$ts=time()-$ts;
if ($ts<60)
// <1 minute
return $ts; //." seconds ago";
elseif ($ts<60*60)
// <1 hour
return floor($ts/60); //." minutes ago";
elseif ($ts<60*60*2)
// <2 hour
return "60"; //"1 hour ago";
elseif ($ts<60*60*24)
// <24 hours = 1 day
return floor($ts/60*60); //." hours ago";
elseif ($ts<60*60*24*2)
// <2 days
return "1 day ago";
elseif ($ts<60*60*24*7)
// <7 days = 1 week
return floor($ts/60*60*24); //." days ago";
elseif ($ts<60*60*24*30.5)
// <30.5 days ~ 1 month
return floor($ts/60*60*24*7); //." weeks ago";
elseif ($ts<60*60*24*365)
// <365 days = 1 year
return floor($ts/60*60*24*30.5); //." months ago";
else
// more than 1 year
return floor($ts/60*60*24*365); //." years ago";
};

$link = mysql_connect("localhost", "database", "pwd");
mysql_select_db("database");
$res = mysql_query("SELECT timestamp from chattable ORDER by timestamp DESC");
while ($line = mysql_fetch_array($res, MYSQL_ASSOC)) {
$ts = $line['timestamp'];
$result=kiedy($ts);
if ($result > 20 ) { // ADJUST THIS TO CLEAR X MINUTES
$SQL = "DELETE from chattable WHERE timestamp = '$ts'";
$resultD = mysql_query($SQL);
}
}

$SQL = 'OPTIMIZE TABLE chattable';
$resultO = mysql_query($SQL);

?>

Good Luck

[theworld]

My apologies for posting twice. Here are some observations.
If the user does not clear the chat window, then older messages will continue to scroll, even if they have been removed from the database. This is nice because you can easily read back. If disconnected, the reconnect, the screen will only refresh the most recent messages.

The importance of running this as a cron job may have been previously understated. It must be run as a cron to achieve the regular flushing of the database messages. I know that C Panel will allow you to set up a crontab.

I have cleaned up the script and removed the superfluous code.

purge.php
<?php
/**Function when($ts) takes timestamp on input and returns human readable time difference. Example output:
*
**/
function kiedy($ts) {
$ts=time()-$ts;
if ($ts<60*60)
return floor($ts/60); //." minutes ago";
else
return floor($ts/60*60*24*365); //." years ago";
};
$link = mysql_connect("localhost", "user", "pwd");
mysql_select_db("database");
$res = mysql_query("SELECT timestamp from chattable ORDER by timestamp DESC");
while ($line = mysql_fetch_array($res, MYSQL_ASSOC)) {
$ts = $line['timestamp'];
$result=kiedy($ts);
if ($result > 20 ) {
$SQL = "DELETE from chattable WHERE timestamp = '$ts'";
$resultD = mysql_query($SQL); // or die("DELETE FAILED FOR: $ts");
}
}
$SQL = 'OPTIMIZE TABLE chattable';
$resultO = mysql_query($SQL);
?>

The job runs as localhost. You can add an .htaccess file to the directory of this script and only allow localhost privileges.

order deny,allow
deny from all
allow from localhost

Cheers

[theworld]

In case you cannot set up a crontab, there is another, less elegant method, which relies on a cookie. Again, this is ONLY applicable if you are using the MySQL container. I use a cookie, set from the index file. Each new visitor then will clear out messages older than the set time limit (I use 20 minutes). The cookie is set for 20 minutes, so if the user should quit or refresh within that time they will not clear the messages again.

The following code goes into the top of index.php

session_start();
function kiedy($ts) {
$ts=time()-$ts;
if ($ts<60*60)
return floor($ts/60) ." minutes ago";
else
return floor($ts/60*60*24*365) ." years ago";
};
if (isset($_COOKIE['cleanup'])) {
// DO NOTHING
}
else {
$link = mysql_connect("localhost", "user", "pwd");
mysql_select_db("database");
$res = mysql_query("SELECT timestamp from chattable ORDER by timestamp DESC");
while ($line = mysql_fetch_array($res, MYSQL_ASSOC)) {
$ts = $line['timestamp'];
$result=kiedy($ts);
if ($result > 20 ) {
$SQL = "DELETE from chattable WHERE timestamp = '$ts'";
$resultD = mysql_query($SQL);
}
}
$SQL = 'OPTIMIZE TABLE chattable';
$resultO = mysql_query($SQL);
setcookie ("cleanup", "cleanup", time()+1200);
}



This method is less desirable than a crontab. First, because if you have a busy chat system you will have a lot of extra overhead on the database. Mine is not so busy, so it works well. Your mileage may vary.

Good Luck

P.S. - I am sure that either method can be integrated into the source in a more refined way. At this time I am simply trying to see what will work. Feel free to comment or post additional information.

[theworld]

If you have read this thread to this point, then I have something special for you.

I have modified mysql.class.php so that you can set a value, in minutes, to automatically remove older messages from the database. I suspect many others will need to set up a MyISAM type database, as I have, so there is an optimization process after the removal. Is this required on MEMORY tables? If not, comment out. For now the time-to-delete value is set in the class, but it appears to be easily configurable. Someone..?

Error handling is processed.


<edit>
This only runs during the init() function. So far I know that /rehash will call this function. What else? ... or am I back to a manual process, albeit cleaner, still, with mud in my eye!? I better keep the crontab running.
</edit>


I'll go ahead and post two new functions for mysql.class.php in here, maybe you -> yes you -) can look them over. These functions have been placed immedaitely after the _connect() function.


function Emptydb()
{
$db = $this->_connect();
$sql_select = "SELECT timestamp from chattable ORDER by timestamp DESC";

$res = mysql_query($sql_select, $db);
while ($row = mysql_fetch_array($res, MYSQL_ASSOC)) {
$ts = $row['timestamp'];
$result=$this->calcTime($ts);
if ($result > 3 ) { // set a time value here, in minutes
$del = "DELETE from chattable WHERE timestamp = '$ts'";
$res2 = mysql_query($del);
if (!$res2) return 0;
}
}
$opt = 'OPTIMIZE TABLE chattable';
$res = mysql_query($opt);
if (!$res) return 0;
return 1; // success
}

function calcTime($ts) {
$ts=time()-$ts;
if ($ts<60*60)
return floor($ts/60); // minutes
else
return floor($ts/60*60*24*365); // years
}

Now, to execute. In the init() function add the following test. I added this immediately before the final return in the function.

// clear out messages over N minutes from the database
$result = $this->Emptydb();
if ($result == 0) {
$errors[] = _pfc("Mysql container: delete older mysql messages error '%s'",mysql_error($db));
return $errors;
}


You cannot set the time value to more than 60, at least not in this version.


I honestly have not yet considered performance factors for this modification, except to leave open the possibility of a dynamic system value where the time value will be set. Then you can check against that value beforehand. pfcglobalconfig class looks like the correct place to do that.


There is more that I have done in mysql.class.php with regards to the open issue of visible mysql password. So far I have had success in preventing the display and maintaining db connect, but at some cost to overall design params. Still working on it.

[DarkOmega]

I like this keep up the good work

[btfans]

hi,

nice work, what about if I am not usig mysql ?

Thanks

[arianmaps]

How to Delete Old Messages
--------------------------------
(Without using MySQL)

Have you noticed that all old messages appear each time you open the chat? If you want a clean chat each time you enter, then do this:

1) Add this line of code to the parameters of the chat (usually at the top of the page, and of course before to the constructor of the chat):

$params["max_msg"] = 0;

2) Open the chat in your web browser and type this message in the input field of the chat (this will rebuild the configuration of the chat):

/rehash

3) Refresh your web browser and you will find a clean new chat.

Every time someone enters the chat, it will be clean. This can be used for security or privacy reasons.

I hope this is useful to you.

(Arian Acosta's Blog)

--------------------------------
If you want to keep a specific number of old messages, you can use:

$params["max_msg"] = 5;

This will show only the last 5 old messages. Remember to use "/rehash" in the chat to rebuild the configuration files.

[lnevo]

For some reason, even with max_msg set to 0 on my system, it is always showing me the last line that was sent. Any ideas how to correct this?

[dieq41]

just a little asking..
why I can not rehash?
thx

[Chasingu]

just a little asking..
why I can not rehash?
thx

You need to be identified as admin.

[dieq41]

Thanx Chasingu