Jump to content


Photo

How can I delete the oldest rows?


  • Please log in to reply
7 replies to this topic

#1 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 10 July 2006 - 05:44 PM

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:
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)
);

Thanks
Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 July 2006 - 06:10 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 10 July 2006 - 06:42 PM

This something like what I need:

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

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
Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#4 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 10 July 2006 - 07:34 PM

This query does what I want. ( or should )

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

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
Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 July 2006 - 08:20 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 SQL_F1

SQL_F1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 13 July 2006 - 08:01 AM

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:
DELETE FROM rl_mail 
         WHERE touid = 20 
     ORDER BY date DESC, time DESC 
LIMIT 50,999999999


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

#7 dptr1988

dptr1988
  • Members
  • PipPipPip
  • Advanced Member
  • 372 posts

Posted 13 July 2006 - 06:19 PM

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

<?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);         
 }
?>

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


Thanks
Need more help with your project? One of the thousands of programmers, web designers or artists at <a href="http://www.rentacode...d_6764522">Rent A Coder</a> would be happy to help.

Disclaimer: Free advice is usually worth what you paid for it. ( or at least when it's coming from me! )

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 July 2006 - 10:14 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users