Jump to content

How can I delete the oldest rows?


dptr1988

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.