I have a website that is a chat room network where you can join and create chat rooms.

I just need a basic php script on how deleting a room from the rooms table when there hasn't been any messages posted in it for .. lets say.. an hour.

the tables involved are:

Chat (table for the messages)
- posted (stores NOW() as datetime)
- room (the ID of the room its posted in)

Rooms
- id
- created

With that information could someone please help me conjure a script that cleans old rooms thanks

bump::

PHP Code:

$timestamp = time();
$timeout = $timestamp-3600;

$query = mysql_query("SELECT room FROM Chat WHERE posted<$timeout");
$row = mysql_fetch_array($query);

if (!$row) {
//do nothing 
} else {
mysql_query("DELETE FROM Rooms WHERE id='".$row["room"]."'");
mysql_query("DELETE FROM Chat WHERE room='".$row["room"]."'");
} 


I think that should work...

Thank you very much for your response.

Though the code seems to have no effect and doesn't delete expired rooms.

There's no really efficient way, but you could use something liek this:

PHP Code:

$timesnip = time() - 3600;
$q = mysql_query("SELECT DISTINCT room, UNIX_TIMESTAMP(posted) AS posted FROM Chat ORDER BY posted DESC");

$ids = array();
while($msg = mysql_fetch_assoc($q)) {
    
    if ($msg['posted'] < $timesnip) {
        $ids[] = $msg['room'];
    }
}

if ($ids) {
    $ids = implode(',', $ids);

    mysql_query("DELETE FROM Chat WHERE room IN($ids)");
    mysql_query("DELETE FROM Rooms WHERE id IN($ids)");
} 


Untested, but may get you on the right track.

Thank you very much, I think that one works a little better.

However, it seems to be naughty and just deleted the chat much too early.. wierd lol

Also is there a way to add in a way to stop it from deleting rooms, and their messages, which have their setting "permanent" set to "1"?

I have commented out the code since it wasn't working efficiently.

bump::