Limiting rows in MySQL table to last 100 entries from form submission

Hi,

I am designing a form that will send data to a MySQL table.

I only want the last 100 form entries (latest 100 rows) however.

How do I delete and replace the oldest entry with the latest one?

Thanks.

So you don’t want to just restrict it to only displaying the 100 most recent entries using the SQL LIMIT clause, but actually delete them from the database as a new entry is stored?

yes - thats correct.

I also need the oldest deleted BUT NOT REPLACED BY THE NEWEST.

The latest would appear as the latest (bottom) of the table.

To get rid of them from the table is really an sql thing more than PHP, I’d imagine you could do something like this pseudo-code:

delete * from table where id not in (select id from table order by sequence_field desc limit 100)

but I’m not 100% sure. sequence_field in this case is whatever you have in the table that determines what order the rows were added.

Then you’d need some JavaScript to delete the top row of the HTML table when you add one on the bottom. But if there’s a chance that more than one user is adding rows that could get quite messy.

Also keep in mind when you’re talking about tables it’s good to clarify where you mean a database table, and where you mean a HTML table. I think I’ve made the right guess above but not totally certain.

It sounds like the best method would be to not limit the rows.

I think I will create a huge MySQL Table with all entries and only show the last 100 rows.

Is there an easy way to manually delete unused rows which I can do manually for example once a month?

It’s just down to what selection criteria you want to use - you can delete by date if your table stores a suitable date, for example.

You might want to consider using transactions so that the correct number of entries get deleted whilst the correct number of entries get added

1 Like

With that few of rows does it really matter unless they are running this as a cron job.

Actually I think I mispoke. If this isn’t a one off script you should be using transactions.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.