0

How is it possible to store data in the database for a limited time (like for example 1 hour)?

A user searches a certain thing, the logic is executed server-side and the search result is loaded. As the result is loaded, I want to store it in MySQL and keep it only x hours. After x hours this data should be deleted from the database.

How is it possible to do this in Laravel or in PHP (It does not matter for me. I just mentioned about Laravel as it may have some libraries for this)? Is it about writing logic in SQL or it's a PHP task?

How to do this? Any suggestions/links/solutions?

Thank you very much!

Andrew T
  • 1,098
  • 2
  • 17
  • 37
  • 1
    Can you just give each row a timestamp? Then query by rows that were added less than `X` hours ago? Does that data really need to be removed? – AJ X. Sep 11 '17 at 22:08
  • Yes. I need this data removed. This means that I have asynchronously to make every second request to the database and to check the diff in time? – Andrew T Sep 11 '17 at 22:15
  • 3
    consider a dedicated cache, like redis or memcached, rather than a temporary table. They have expire properties that will automatically do this. But as noted by @axlj you can just use a timestamp field and a procedure to delete rows on a schedule. – Rafael Sep 11 '17 at 22:17
  • It is my task to keep the data in the database, and I need it to be only for some hours – Andrew T Sep 11 '17 at 22:17
  • @Rafael do you mean this https://dev.mysql.com/doc/refman/5.7/en/events-overview.html? – Andrew T Sep 11 '17 at 22:18
  • Yes, that is it. – Rafael Sep 11 '17 at 22:20
  • thank you all very much! post it as an answer and I will mark it as accepted – Andrew T Sep 11 '17 at 22:20
  • 1
    @AndrewT you should answer your question in a couple days with the solution you arrived to. – Rafael Sep 11 '17 at 22:22
  • Thank you! I will continue the work tomorrow and I will post the solution here when I arrive to it! – Andrew T Sep 11 '17 at 22:24
  • Don't use MySQL as a caching system, there are plenty of *actual* caching systems that are far better. eg: Redis. – Sammitch Sep 11 '17 at 23:39
  • Just for curiosity for one hour you can store data in Redis instead of mysql why do you even bother? – Anar Bayramov Sep 12 '17 at 02:45
  • Seems a duplicate of https://stackoverflow.com/questions/19197486/mysql-record-that-would-subject-to-ttl – Dileep Kumar Sep 12 '17 at 07:44

3 Answers3

1

Is it about writing logic in SQL or it's a PHP task?

Doing this with php would be a hassle because you would always need to send requests to check if a record is expired and then send another to delete. This can be achieved using events in mysql. I came across this blog post which might be helpful.

Also dont forget to check the mysql documentation page

Norris Oduro
  • 1,009
  • 7
  • 17
1

Tag records with an expiration timestamp, and have a cron job run every minute (or whatever frequency you prefer) to delete expired records.

kmoser
  • 7,449
  • 2
  • 20
  • 36
0

The best solution for this task would be to use the Redis database. There are 2 drivers for PHP: pRedis and phpRedis.

The pRedis is a implementation written in PHP which works relatively very slow for a big number of users.

The phpRedis driver is written in C, and it works much faster.

Use the $redis->psetex($key, $destroy_time, $value) function to create self-destroying records. Hope it helps anyone in future

Andrew T
  • 1,098
  • 2
  • 17
  • 37