Leesy Posted October 24, 2005 Share Posted October 24, 2005 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? Link to comment https://forums.phpfreaks.com/topic/2724-limiting-a-table-size/ Share on other sites More sharing options...
Barand Posted October 26, 2005 Share Posted October 26, 2005 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' "); Link to comment https://forums.phpfreaks.com/topic/2724-limiting-a-table-size/#findComment-9149 Share on other sites More sharing options...
Leesy Posted October 28, 2005 Author Share Posted October 28, 2005 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. Link to comment https://forums.phpfreaks.com/topic/2724-limiting-a-table-size/#findComment-9207 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.