Jump to content


Photo

Limiting A Table Size


  • Please log in to reply
2 replies to this topic

#1 Leesy

Leesy
  • Members
  • Pip
  • Newbie
  • 2 posts
  • LocationCambs, UK

Posted 24 October 2005 - 02:33 PM

Hi all,

I tried to search for an answer to this but the worls "limit", "table", "size" etc. come up a lot :)

I'm creating a shoutbox for my site. I've got a simple table where I store the shouts (contains 4 fields: ID, Name, Shout & Date. ID auto increments). I want to ensure that this table sticks to a certain size (say 25 rows). Obviously I want the least recent shouts to be deleted.

What's the best way to do this? A trigger; limiting the table to a set amount of rows and it'll hopefully remove the oldest rows to make room (couldn't see anything to do this in phpMyAdmin. SQL knowledge doesn't go past the basics); or simply deleting a row everytime I insert a new one past the count of 25?

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 26 October 2005 - 06:36 AM

After inserting a new row, get the id of the new row

$newid = mysql_insert_id();
$keep = $newid - 25;

then

mysql_query ("DELETE from shoutTable WHERE id < '$keep' ");

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Leesy

Leesy
  • Members
  • Pip
  • Newbie
  • 2 posts
  • LocationCambs, UK

Posted 28 October 2005 - 09:20 AM

Cheers.

That'll work as I am using a table with auto_increment ID's. Als o Isee that the new version of MySQL has triggers implemented (or so the picture on their website was showing). So when/if my host starts to use the new MySQL, I can always try a trigger later on.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users