Jump to content

Archived

This topic is now archived and is closed to further replies.

dptr1988

How can I delete the oldest rows?

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.