I have searched for an answer to this, but haven't found one. I apologize if it's already been answered somewhere.
Here's my situation:
There is a table in my MySQL database that I would like to limit to 150 rows. If a new row is inserted after the limit has been reached, I would like to remove row #1, and then insert the new row.
I am using PHP and PDO, if that matters.
I feel like this is a simple question, and I apologize if it's a total noob thing to ask. I'm relatively new to this and I'm just trying to learn.
Can anyone point me in the right direction, or explain the syntax I would use for something like this?
Thank you in advance for your time!
You can count the rows in your table:
SELECT COUNT(*) FROM <table>
Then if that number has reached your limit you can delete a row:
DELETE FROM <table> ORDER BY <column> LIMIT 1;
And finally you add your new row.
Yes, that's three steps, and that's how MySQL works.
This could go pear-shaped when multiple users access the table, better use transactions and lock the table.
An other approach is needed I think. This is just not what you should do with a database. The better solution would be to limit the rows when you retrieve them:
SELECT * FROM <table> ORDER BY <column> LIMIT 150
This way you only retrieve 150 rows and this should also solve your problem. Add a harmless DELETE somewhere in your code like this:
SELECT COUNT(*) FROM <table>
DELETE FROM <table> ORDER BY <column> LIMIT <count-200>;
Or any variant of that... basically delete rows that are way to old.