Hi fellow programmers, how do I run automated updates on a MySQL row? eg Check the row after 5 minutes, if the timestamp has been exceeded, it updates that particular row from pending to expired. I wanted to include a global php file that would be included/called on each page load to check the timestamp after every 5 minutes. Next, I read up on MYSQL Triggers and Event Scheduling but I found out that this might accrue overhead server issues as traffic increases.

What would you suggest as an effective measure to fix this?

Thanks.

You need to setup a Cron job/task scheduler script that runs at an interval (every 5 minutes)

spikesC:
You need to setup a Cron job/task scheduler script that runs at an interval (every 5 minutes)

Thank you very much, I appreciate. But is there any difference between setting up a Cron Job and using a MYSQL event scheduler?
Just asking, thanks a lot.

There is a technique you can use too - to make the task run every 5 minutes so far as the site is accessed (by anyone). This will depend on site traffick rather than cron jobs. There are servers that cannot run cron jobs.
In drupal, we call this poor man cron!

dhtml18:
There is a technique you can use too - to make the task run every 5 minutes so far as the site is accessed (by anyone). This will depend on site traffick rather than cron jobs. There are servers that cannot run cron jobs.
In drupal, we call this poor man cron!

Boss dhtml, how does this work? How do I go implementing it? I'm migrating to DigitalOcean, do I think I should be able to run CRON based tasks (My first time ever using DO or a VPS).

Which method would you suggest I go for? Thanks

Since you are going for a VPS, well, I guess you should have cron on that, or the ability to setup a cron. And also you might be able to run CLI via SSH in which case you have the following options:
1. Setup cron there via something like - $ crontab -e
2. Setup cron using your control panel
3. Setup a task schedular DAEMON via CLI interface (SSH)

More generic ways:
4. Use remote services to setup cron jobs
5. Use poor man's cron

If you look at this link:
http://stackoverflow.com/questions/1857741/how-to-run-a-php-script-in-every-5-10-minutes-without-cron-perl-etc
One dude with solution #15 suggested a poor man's cron (my solution #5 which i described earlier and can work in every known programming languages with/without a scheduler - that is what i normally use, cant remember last time i setup cron job sef)

On the same link another guy suggested a DAEMON(that is my 4th solution):
ini_set('max_execution_time', 'sometime');
while(1){
//do something
sleep(sometime);
}


While yet someone suggested my solution #4:
I'm sure this tools can help you with your request:

SetCronJob - www.setcronjob.com

Cronless - cronless.com

EasyCron - www.easycron.com

Online Cron Jobs - www.onlinecronjobs.com

Corntab - www.corntab.com

getCron - www.getcron.com



For the record, I have used all the above method for many jobs that I have done in the past, and all of them have their advantages and disadvantages.
For sites that have traffick, I always use the poor man's cron technique - because it can never fail, and it is easy to test it on your developmental and live server. And you do not need to start worrying yourself about cron jobs or not.
If you use this technique, and you just want to be sure that it runs, you can use cron job as a backup to make sure the site loads every 5 minutes
in that case, you will need a cron command like:
*/5 * * * * wget http://example.com/check

You can see that here - http://www.thegeekstuff.com/2011/07/cron-every-5-minutes/

Again, everything I have mentioned on this thread are what I have actually used in time past.

dhtml18:
Since you are going for a VPS, well, I guess you should have cron on that, or the ability to setup a cron. And also you might be able to run CLI via SSH in which case you have the following options:
1. Setup cron there via something like - $ crontab -e
2. Setup cron using your control panel
3. Setup a task schedular DAEMON via CLI interface (SSH)

More generic ways:
4. Use remote services to setup cron jobs
5. Use poor man's cron

If you look at this link:
http://stackoverflow.com/questions/1857741/how-to-run-a-php-script-in-every-5-10-minutes-without-cron-perl-etc
One dude with solution #15 suggested a poor man's cron (my solution #5 which i described earlier and can work in every known programming languages with/without a scheduler - that is what i normally use, cant remember last time i setup cron job sef)

On the same link another guy suggested a DAEMON(that is my 4th solution):
ini_set('max_execution_time', 'sometime');
while(1){
//do something
sleep(sometime);
}


While yet someone suggested my solution #4:
I'm sure this tools can help you with your request:

SetCronJob - www.setcronjob.com

Cronless - cronless.com

EasyCron - www.easycron.com

Online Cron Jobs - www.onlinecronjobs.com

Corntab - www.corntab.com

getCron - www.getcron.com



For the record, I have used all the above method for many jobs that I have done in the past, and all of them have their advantages and disadvantages.
For sites that have traffick, I always use the poor man's cron technique - because it can never fail, and it is easy to test it on your developmental and live server. And you do not need to start worrying yourself about cron jobs or not.
If you use this technique, and you just want to be sure that it runs, you can use cron job as a backup to make sure the site loads every 5 minutes
in that case, you will need a cron command like:
*/5 * * * * wget http://example.com/check

You can see that here - http://www.thegeekstuff.com/2011/07/cron-every-5-minutes/

Again, everything I have mentioned on this thread are what I have actually used in time past.

Why I'd always respect you. A lot to read up on and learn, thanks for coming through boss. Respect.

^^^You are welcome bro, your post is a very reasonable one, and I am proud to be of some assistance. Now I need a thread where I can fight or break someone's head - trolls off to find trouble!

DharkPoet:

Thank you very much, I appreciate. But is there any difference between setting up a Cron Job and using a MYSQL event scheduler?
Just asking, thanks a lot.

since you are working at the database level, there is no need using cron. Mysql have Events Scheduler and Triggers to help u update rows and columns at intervals or based on a trigger.

Using database objects entirely is better as you will avoid the complexity that comes with cron. Moreover cron and php scripts will require connecting to the database which may cost performance and server overhead.

All you have to do is create DB event that the MYSQL server will run at the interval you need.

check the latest mysql manual for the syntax and structuring

^^^All the points above are correct - however, you should keep in mind that not every MySQL server enables trigger by default. But since you are talking of a VPS, if it does not, then you can enable it or get your admin to do that.

And yes, if all you want to do is to update a row at interval - not that you are fetching information from a remote url or doing some serious hard-programming stuffs, then you should not consider cron, your first option should be MySQL TRRRRRIGER!

DharkPoet:

Thank you very much, I appreciate. But is there any difference between setting up a Cron Job and using a MYSQL event scheduler?
Just asking, thanks a lot.

DharkPoet:

Boss dhtml, how does this work? How do I go implementing it? I'm migrating to DigitalOcean, do I think I should be able to run CRON based tasks (My first time ever using DO or a VPS).

Which method would you suggest I go for? Thanks

Welcome to DO. As you're migrating to VMs, I suppose you also want to do things the 'right way'. dhtml18 has provided enough information for your options so there's no need for me to do that.
CRON jobs are very easy to setup. It is just one line of code in your user cron file and everything is done. CRON is the defacto way to run scheduled tasks on Linux. It scales very well and because the task is not tied to your database, there is no single point of failure. But then, I suppose you really don't care about that for now.

This is the line to run your php script every 5 minutes.

*/5 * * * * php /path/to/script.php

I wouldn't use mysql to run scheduled tasks for so many reasons. The most important ones for me being scalability, migration and seperation of responsibilities. Today you just want to update a row, tomorrow you want to send an email notification after you update the row. What would you do then?
Triggers are also a nightmare to work with, a simple search on google will explain much.

Since you have root access to this VM, I don't see why you shouldn't make use of the power it offers. Why offload a critical part of your operation to a third party when you can handle it internally without any downside?

^^^Truth be said, I have always had issues with pulling triggers on live servers. I always find it disabled, and when I try explaining to server admins, they look at me like I am crazy, and some will even tell you that you are the ONLY CUSTOMER asking for such a feature.
So most times, I do a simple cron, or a poor man's cron (which is very scalable and works everytime)

dhtml18:
^^^Truth be said, I have always had issues with pulling triggers on live servers. I always find it disabled, and when I try explaining to server admins, they look at me like I am crazy, and some will even tell you that you are the ONLY CUSTOMER asking for such a feature.
So most times, I do a simple cron, or a poor man's cron (which is very scalable and works everytime)

Yes, You need to setup a cron job. write your script in a file and set the time interval the server should visit the script and run it.

the dude above me has nailed it

If you are a leader at heart, skilled with programming and having knowledge of Content development (videos)... I would like to invite you to co-found a very promising business with me and our team. More details upon contact... reach me via ndukaude@gmail.com or whatsapp at 08166459353. Cheers!

This is basically what does automatic birthday emails and texts..