Reset a column's value in every 24 hours

Question

I am using PHP and I wish to reset one of the column's value in my database at 12 AM everyday. How could I do that? Any help will be deeply appreciated, thanks!

Answer 1

There are few ways to this,

One is to schedule event @ database level - MySql

Quoting from the reference

Enable the event scheduler

SET GLOBAL event_scheduler = ON;

and create an event like this:

CREATE EVENT 
ON SCHEDULE EVERY 1 DAY
STARTS '2014-01-18 00:00:00'
DO
<Your SQL query here>;

and that's it.

Read more about the syntax here and here is more general information about it. reference

Another is to create event @ server level

You can then add the following to your cron job

mysql --user=[username] --password=[password] --database=[db name] --execute="<Your Query Here>"

reference from SO

Answer 2

What you do is make a php script that does the task and save it as cronjob1.php (or any name of your choice)

There is a cron jobs module in almost all the control panels. You just click on it enter the fields about the the time the job will be executed.

And in the command to be executed field type the code to execute the php file. As I don't know which control panel you are using, you can ask the question about 'using the cron jobs module' in your control panels support section.

Hope this helps!

Answer 3

You can do that in several ways.

The most simple way will be creating a bash script and schedule a job with crond.

If you're using a Unix based system you can create a simple bash script with an update command.

#!/bin/bash

mysql -h <server> -u <user> -p<password> [database_name] -e "UPDATE table SET column = 'value'";

On Windows you can create a batch script with the exactly same command and add to your "Task Scheduler" in Administration Tools.