SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Wanna-be Apple nut silver trophy M. Johansson's Avatar
    Join Date
    Sep 2000
    Location
    Halmstad, Sweden
    Posts
    7,400
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Keeping a mySQL table at 100 entries?

    Well, I have a mySQL table that constantly gets added new entries. The id of each entry is auto_incremented.

    How would I go about writing a script that removes the old entries, so that the number of entries in the table becomes 100? I.e. if the table has 145 entries, I would like the script to remove entry 1-45.

    It seems so simple, but I simply can't figure it out, for some reason! Can someone please help?
    Mattias Johansson
    Short, Swedish, Web Developer

    Buttons and Dog Tags with your custom design:
    FatStatement.com

  2. #2
    SitePoint Zealot Paul_M's Avatar
    Join Date
    Mar 2001
    Location
    London
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    I can give you an algorithm.... if it still doesn't work tell me and i'll try and write it myself.

    1. Connent to the db
    2. SELECT * from TABLE
    3. see how many records you get.
    4. assign the result of step 3 to a variable
    5. if variable >100 then step 6 else quit
    6. subtract 100 from that variable
    7. DELETE * from TABLE where id less than variable
    8. quit

    That should work...


  3. #3
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Feb 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    more robust meathod....

    Code:
    sql = (SELECT Count(id) AS NumOfRows, Min(id) AS MinRowVal FROM table)
    
    if NumOfRows is greater than 100
    {
    delnum = ((NumOfRows - 100) + MinRowVal)
    
    sql = (DELETE * FROM table WHERE id < delnum)
    }
    something along those lines. this will work no matter how many times you run it but if there are values missing from id in the middle of the column (eg: 1,2,3,4,6,7,8,9,...,120) it will probly break, depending on where the missing value is.

    this would be easy to get around with a loop but dont bother if you can garontee that there wont be missing values in the middle of the column. that would be a waste of server resources.

    btw, sorry i could write this up in php for you but im not very familliar with it yet.


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
  •