How can I delete the oldest rows?

[dptr1988]

I made an online email system, and I want to limit the users to only 50 emails that they can store. If there mailbox already has 50 emails in it and sombody trys to send a new letter, I want to delete the oldest letter to make room for the new one. What I want to do is save the newest 50 rows and delete the rest, but I don't know how to do that. I searched on the MySQL Help forum and found a similar problem/solution. But they were saying to delete all of the rows "WHERE msgid < (lastid - 50)" but that would not work for me because there are multiple users on the same msgid.



Here is the table that stores the emails:
[code]CREATE TABLE rl_mail
(
msgid INT UNSIGNED NOT NULL AUTO_INCREMENT,
fromuid INT UNSIGNED NOT NULL,
touid INT UNSIGNED NOT NULL,
date DATE NOT NULL,
time TIME NOT NULL,
viewed BOOL NOT NULL,
subject TEXT,
msg TEXT,
PRIMARY KEY(msgid)
);[/code]

Thanks

[fenway]

Well, you can issue a DELETE with an "ORDER BY <> desc limit 50" -- btw, I don't know why you're storing date & time separately, nor why you're using reserved names -- and simply add the fromuid to the WHERE clause.  Doing this for all users would require a SP or some sort of iterative function.

[dptr1988]

This something like what I need:

[code]DELETE FROM rl_mail WHERE touid = 20 ORDER BY date DESC, time DESC LIMIT 1000 OFFSET 50[/code]

The problems is that LIMIT requires a limit and optionally an offest, but I just want an offset. That query will only delete 1000 rows and I want it to delete everything except the 50 lastest rows.

Does that explain my problem better?

Thanks

[dptr1988]

This query does what I want. ( or should )

[code]DELETE FROM rl_mail WHERE msgid IN (SELECT msgid FROM rl_mail WHERE touid = 20 ORDER BY date DESC, time DESC LIMIT 50,999999999)[/code]

When I go to run that query it says this "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' ". My MySQL version number is 5.0.22.

Is there another query that would do the same thing but work on my version of MySQL?

Thanks

[fenway]

Limit does take an offset, so even with a order by clause, you could always say LIMIT 50, 1000, and you'd be OK in the event that there were fewer than 50 records returned.

[SQL_F1]

DELETE FROM rl_mail WHERE msgid IN (SELECT msgid FROM rl_mail WHERE touid = 20 ORDER BY date DESC, time DESC LIMIT 50,999999999)

What's wrong with:
[code]
DELETE FROM rl_mail
        WHERE touid = 20
    ORDER BY date DESC, time DESC
LIMIT 50,999999999

[/code]

Curious:How can there be multiple users on the same msgid?

[dptr1988]

Your right! I finally figured out something that works. Here it is.

[code]<?php
// check if they have too many events
$query = 'SELECT COUNT(eventid) as events FROM rl_events WHERE uid = '.
          $_SESSION['rl_uid'];
$nevents = mysql_fetch_assoc(db_query($query));


if ($nevents['events'] > 50)
{       
  // delete the oldest ones if they have more than 50 events
  $query = 'SELECT eventid FROM rl_events WHERE uid = '.
          $_SESSION['rl_uid'].
          ' ORDER BY date DESC, time DESC LIMIT 50,999999999';
         
  $result = db_query($query);
  $arraystr = '';
  while($value = mysql_fetch_array($result) )
  {
  $arraystr .= $value[0].', ';
  }

  $arraystr = trim($arraystr, ', ');
 
  $query = 'DELETE FROM rl_events WHERE eventid IN ('.$arraystr.')';
         
  db_query($query);       
}
?>[/code]

If you know of a better way to do that, please tell me.


Thanks

[fenway]

In principle, you can run a multi-table delete and save a query and round-trip to and from PHP, but it's your call.