Professional Web Applications Themes

How do I keep a fixed list of rows in a table? - MySQL

Probably not making myself clear with that subject. Here is what I want to do. I want to basically implement a FIFO (FirstInFirstOut) queue in SQL. So I want to keep adding to a given table, but only have it keep the last say 25 items. Anything older is removed. I have been trying to do some stuff with the DELETE statement, but I am going no where. So say a table is setup for multiple users: Column_User Column_Item Column_Date (? probably need this column) I want there ever to be only 25 rows stored for a given user; so ...

  1. #1

    Default How do I keep a fixed list of rows in a table?

    Probably not making myself clear with that subject. Here is what I
    want to do.

    I want to basically implement a FIFO (FirstInFirstOut) queue in SQL.
    So I want to keep adding to a given table, but only have it keep the
    last say 25 items. Anything older is removed. I have been trying to
    do some stuff with the DELETE statement, but I am going no where.

    So say a table is setup for multiple users:

    Column_User
    Column_Item
    Column_Date (? probably need this column)

    I want there ever to be only 25 rows stored for a given user; so when I
    INSERT a new item, I would need to remove/trim the items. Its this
    removing of the other items that is causing me a bit grief, as I can't
    seem to do it without doing another SELECT.

    Thoughts? Suggestions?

    Thanks

    Alan Williamson Guest

  2. #2

    Default Re: How do I keep a fixed list of rows in a table?

    Alan Williamson wrote:
    > I want there ever to be only 25 rows stored for a given user; so when I
    > INSERT a new item, I would need to remove/trim the items.
    MySQL implements as an extension to the SQL standard ORDER BY and LIMIT
    clauses for the DELETE statement:

    1. Start transaction
    2. SELECT COUNT(*) FROM tablename WHERE column_user = 'Alan';
    3. If count >= 25, DELETE FROM tablename
    WHERE column_user = 'Alan'
    ORDER BY column_date ASC
    LIMIT ($count-24);
    You must do the $count-24 calculation before you create the SQL
    statement, because LIMIT accepts only an integer, not an expression.
    4. INSERT INTO tablename <one new row for user 'Alan'>;
    5. COMMIT;

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: How do I keep a fixed list of rows in a table?

    Alan Williamson <alanblog-city.com> schrieb:
    > I want there ever to be only 25 rows stored for a given user; so when I
    > INSERT a new item, I would need to remove/trim the items. Its this
    > removing of the other items that is causing me a bit grief, as I can't
    > seem to do it without doing another SELECT.
    In mySQL 5 it might be possible to create an update trigger, which
    deletes old rows.

    Cheers,
    --
    Philipp Tölke
    PGP: 0x96A1FE7A
    Philipp Tölke Guest

Similar Threads

  1. Make certain datagrid rows fixed
    By Soshan in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: June 14th, 06:21 PM
  2. Replies: 0
    Last Post: September 16th, 04:37 PM
  3. Replies: 0
    Last Post: September 15th, 05:39 AM
  4. Replies: 0
    Last Post: September 10th, 05:33 PM
  5. Replies: 0
    Last Post: September 10th, 05:29 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139