Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,555
    Thanks
    57
    Thanked 148 Times in 147 Posts

    truncate through limited table entries.

    Hi I was wondering if there was a simple solution in mysql to truncate mysql entries.

    i.e.

    if i want only a maximum of 100 entries in one table if a new entry is submitted, (101st entry) then it would delete lets say the last entry ( ordered by a time stamp ) and insert the new one (still only having 100 entries).

    Any help much appreciated,

    Regards, Phil

    p.s. I have heard I may be able to use 'triggers'??? anyone know how to use triggers in this example or a GOOD tutorial on 'triggers'??
    Website Design Mansfield
    PHP Code:
    function I_LOVE(){function b(&$b='P'){$b.='P';}function a($_){return $_++;}$b='P';define("B",'H');b($b=implode('',array($b=a($b),$b=a(B))));b($b);return $b;}
    echo 
    I_LOVE(); 

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Why not simply use a timestamp column and then retrieve the 100 newest rows?

  • #3
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,555
    Thanks
    57
    Thanked 148 Times in 147 Posts
    because each user has a table storing actions that they do on the site. If a really active user, there could be 100 entries a day.
    Website Design Mansfield
    PHP Code:
    function I_LOVE(){function b(&$b='P'){$b.='P';}function a($_){return $_++;}$b='P';define("B",'H');b($b=implode('',array($b=a($b),$b=a(B))));b($b);return $b;}
    echo 
    I_LOVE(); 

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    1) you don't need a new table for each user
    2) properly indexed tables, you aren't worrying about performance problems until you get into the 10s of millions of records per table and even then they aren't all that large.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    I'm going to second GuelphDad but go *much* stronger:

    If you have one table per user, you have a really screwed up database design. You are surely causing yourself much more grief by doing this than you could possibly be saving.

    Anyway, the answer to your original question is simple: Use a Stored Procedure to add the entries.

    I do agree with GuelphDad that there's no real reason to remove the entries to limit people to 100. MySQL will quite handily keep track of millions of records and if you just do LIMIT to get the latest 100, ordered by datetime posted perhaps, you really will keep it as simple and as efficient as possible.

    But if you have some other odd reason to limit to 100, then a Stored Proc is clearly the way to go.
    Code:
    DELIMITER $$
    CREATE PROCEDURE add_post
    (
        i_userid INT,
        i_post TEXT
    )
    BEGIN
        DELETE FROM posts WHERE userid = i_userid AND postid NOT IN (
            SELECT postid FROM posts WHERE userid = i_userid 
            ORDER BY whenposted DESC LIMIT 100 );
        INSERT INTO posts ( userid, whenposted, message )
        VALUES( i_userid, NOW(), i_post );
    END
    $$
    DELIMITER ;
    And notice how easy this is, with all the posts in a single table instead of using one table per user. If you did use one table per user, the code would be MUCH more complex.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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