Automatically resetting typing status in MySQL after 5 seconds

Data and Databases
1

I’m working on a project that uses MySQL with phpMyAdmin through WampServer. I have a table called ‘UserActivity’ with a column named ‘isTyping’.

Here’s what I want to do:

  1. When a user starts typing, I set ‘isTyping’ to 1.
  2. If the user stops typing for 5 seconds, I need to automatically set ‘isTyping’ back to 0.
  3. If the user types again within those 5 seconds, the timer should reset.

I’m not sure how to make this happen automatically. Should I add a timestamp column? Do I need to run a script that checks the database constantly? Or is there a way to do this with MySQL triggers?

Any advice on the best approach would be really helpful. I’m new to database management and I’m not sure which method would be most efficient for this task.

CREATE TABLE UserActivity (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  isTyping TINYINT(1),
  last_activity TIMESTAMP
);

Thanks in advance for any suggestions!

2

hmm, interesting problem! :thinking: have u considered using websockets? they could help with real-time updates. You could send a ‘typing’ signal when the user starts, then use a setTimeout on the server to reset after 5 secs. if another signal comes in, clear the timeout. what do u think? any downsides to this approach?

3

For your scenario, I recommend implementing a server-side solution using a combination of MySQL events and a timestamp column. Add a ‘last_typing_time’ TIMESTAMP column to your UserActivity table. When a user starts typing, update both ‘isTyping’ and ‘last_typing_time’. Create a MySQL event that runs every few seconds to check and reset ‘isTyping’ for users whose ‘last_typing_time’ is more than 5 seconds old. This approach minimizes the need for constant external scripts and leverages MySQL’s built-in scheduling capabilities, making it efficient and easier to maintain in the long run.

4

hey there! for this, i’d suggest using a combination of PHP and JavaScript. on the client side, use JS to detect typing and send AJAX requests. on the server, update the ‘isTyping’ and ‘last_activity’ columns. then, create a cron job that runs every minute to reset ‘isTyping’ for users inactive for 5+ seconds. this way, you dont need constant db checks. hope this helps!