0

So I'm using WampServer with the default phpMyAdmin to store this SQL table called Typing.

Table: Typing

Now I want to set the typing column to 0 for any row that has set the typing column to 1 more than five seconds ago.

For ex. I just set the typing column to 1 for the first row and my database detects the time since this 1 has been written, then it sets a 5 second timer to revert that 1 back to a 0. If 1 is overwritten with another 1 during that time, that timer should rest.

How should I go about this? Should I have a column for a 'timestamp' of each record? How do I make my database constantly check for entries older than 5 seconds without user input? Do I need an always on PHP script or a database trigger and how would I go about that?

Jesus Christ
  • 1
  • 1
  • 2
    phpMyAdmin is just a PHP script for interacting with a MySQL database. You could create a `created` column with a timestamp or a datetime. Then run a cron job that removes old entries. 5 seconds is a rather short interval though. You might be better of just selecting items newer than 5 seconds in your scripts and purging them on larger intervals. – JimL Jan 03 '16 at 07:48
  • @JimL What program should I use on my Windows machine to run cron jobs and how do they interface with my SQL database? – Jesus Christ Jan 03 '16 at 13:43
  • Will you run this in production on windows - or are you just developing on windows? – JimL Jan 03 '16 at 13:45
  • @JimL I'm running the server on Windows. – Jesus Christ Jan 04 '16 at 01:06
  • http://stackoverflow.com/questions/7195503/setting-up-a-cron-job-in-windows – JimL Jan 04 '16 at 01:08

2 Answers2

0

As @JimL suggested, it might be a bit too ambitious to purge the records after only five seconds.

It might be helpful to have more information about what you're trying to accomplish, but I'll answer in a generic way that should answer your question

How I would handle this is that any queries should check for records that are less than five seconds old (I assume you're querying the data and only want records that are less than five seconds, otherwise I'm not really following the point of your question).

Once a day, or hourly if you have that much data, you can run a scheduled job (scheduled through MySQL itself, not through cron/Windows Scheduled Tasks) to purge the old records. You can use phpMyAdmin to set that up (the "Events" tab), although it's actually a MySQL feature that doesn't require phpMyAdmin.

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
  • @JimL I got it, I added a timestamp to each record and used this code: `mysqli_query($con,"DELETE FROM 'typing' WHERE TIMESTAMPDIFF(SECOND,recordDate, CURRENT_TIMESTAMP) > 1");` It's not a chron job though so it only runs if there is someone accessing the site, but it's good enough for what I need. Thanks for the help :) – Jesus Christ Jan 05 '16 at 03:37
0

I got it, I added a timestamp to each record and used this code:

mysqli_query($con,"DELETE FROM 'typing' WHERE TIMESTAMPDIFF(SECOND,recordDate, CURRENT_TIMESTAMP) > 1");

It's not a chron job though so it only runs if there is someone accessing the site, but it's good enough for what I need. Thanks for the help everyone :)

Jesus Christ
  • 1
  • 1