I have a mysql table and I want it to be "emptied" every night at midnight. I have searched for an answer on the web and came across nothing that seemed to help me. I had this idea of using javascript to get the current time and then run an if statement and see if it is equal to midnight and if it was to execute a php script that deleted the information.

Javascript:

var myVar=setInterval(function(){myTimer()},1000);

function myTimer()
{
var d=new Date();
var t=d.toLocaleTimeString();
    if(t == 12:00:00 AM){
            $.ajax({
                URL: 'delete.php';
            });
    };
};

delete.php:

<?php
require 'connect.php';
mysql_query("DELETE * FROM messages;");
?>

I have tested this by setting the time in the if statement to a time a few minutes ahead of my actual time and it does not work.

I have a mysql table and I want it to be "emptied" every night at midnight. I have searched for an answer on the web and came across nothing that seemed to help me. I had this idea of using javascript to get the current time and then run an if statement and see if it is equal to midnight and if it was to execute a php script that deleted the information.

Javascript:

var myVar=setInterval(function(){myTimer()},1000);

function myTimer()
{
var d=new Date();
var t=d.toLocaleTimeString();
    if(t == 12:00:00 AM){
            $.ajax({
                URL: 'delete.php';
            });
    };
};

delete.php:

<?php
require 'connect.php';
mysql_query("DELETE * FROM messages;");
?>

I have tested this by setting the time in the if statement to a time a few minutes ahead of my actual time and it does not work.

• javascript
• php
• mysql
• sql

• What webhost are you using? You should probably setup a linux cronjob to call the a PHP file at midnight. – benathon Commented Dec 12, 2013 at 2:27
• 000webhost. I'm not exactly sure what a cronjob is? I am sorry, I am quite new to programming. – user3084715 Commented Dec 12, 2013 at 2:28
• 3 you certainly don't want to do it in javascript. If you had many users, all with different time zones, your database would be cleared at most every 15 minutes (yes there are weird timezones like that)! – Patrick James McDougle Commented Dec 12, 2013 at 2:38
• 1 Also if you had hundreds of users all in one timezone, suddenly your server would get hundreds of requests as soon as midnight rolled around. – Patrick James McDougle Commented Dec 12, 2013 at 2:41
• A cron job is a "chronic job" or something that happens on a schedule. It can be evert 5 mins or once a day at midnight like you want. See this example for your specific web host: free-installations.info/threads/… – benathon Commented Dec 12, 2013 at 6:30

1 Answer 1

Implementing your own event scheduler, especially as a web page using JavaScript is a bad idea. Use for that either

• a cron job to run DELETE statement through the mysql mand line interface

    /path/to/mysql -u<user> -p"<password>" <db_name> -e "delete from messages"

• or a MySQL event, e.g.

     CREATE EVENT delete_messages_at_midnight 
     ON SCHEDULE EVERY 1 DAY STARTS CURDATE() + INTERVAL 1 DAY
     DO DELETE FROM messages;

If you go with MySQL event approach:

• use SHOW PROCESSLIST to check if the event scheduler is enabled. If it's ON you should see a process "Daemon" by user "event_scheduler".
• use SET GLOBAL event_scheduler = ON;to enable the scheduler if it's currently not enabled.
• More on configuring event scheduler read here