SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Almeaty Member
    Join Date
    Jul 2000
    Location
    Nowhere
    Posts
    278
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Delete all rows but leave 10

    In mysql, is it possible to delete all rows but leaving the last (newest) 10 (or any other number)? maybe something with LIMIT?

    TIA

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Location
    Wolverhampton, UK
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okey dokey....

    if they are oraganised by id with auto_increment primary keys should be easy enough:

    delete from table limit 10,*(insert largish number here to clear older rows) order by id desc;

    should do the trick

    or:

    the php way:
    <?php
    $result = mysql_query("select * from table order by id desc");
    $rows = mysql_num_rows($result);
    $cutoff = $rows - 10; #or how ever many u want to keep
    $delete = mysql_query("delete * from table where id <= " . $cutoff);

    #this however relies entirely on id goin' up in consec numbers
    #too tired to make it better, hange it tomoora
    ?>

    sorted,
    beachball


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •