PDA

View Full Version : PHP or Shell Script called based on datetime value in Mysql



karentutor
9th June 2017, 02:29 AM
Hi

I am using MySQL in Fedora and want to call a script (PHP or shell) based on a date in a time date column in MySQL.

So that when the server clock ticks a value stored in MySQL for example 17/10/2017 21:00:00 a program executes.

What is the best way to do that?

ocratato
9th June 2017, 02:44 AM
I can think of a few alternatives, but it would be good to know a little more about the problem first:

How accurate does it need to be - are minutes good enough, or do you need seconds, or finer ?
How often do you expect it to run (on average) ?
How long does the script run for - is there a possibility of it still running when it needs to be started again ?

karentutor
9th June 2017, 03:01 AM
Hi

1. Minutes are fine.

2. The datetime is being submitted to me via $_POST on a website. I will filter / sanitize and then use that datetime value to execute a PHP program at the time requested in the $_post datetime value.

I am wondering should we maybe write the post value directly my to crontab file and then have that execute? Or to MySQL and then have MySQL execute at the correct time (although I am not sure if MySQL has a schedule) like crontab.

As always there are a couple of approaches I am just not sure which one or how.

3. The $_POST schedule request datetime will occur initially probably a few times per day but could scale up from there. We then use that value to run the PHP program at the requested datetime.

4. The PHP script it calls should be quite quick - it is just generating URL for the customer and a checksum. However unfortunately, the script needs to run close to the requested $_POST time (hence the requirememnt for scheduling) or I would just store the results of the call immediately upon the schedule request and store the results to a database for later retrieval.

Thanks

ocratato
9th June 2017, 03:14 AM
After posting previously I did a little research. Can you use these?

https://dev.mysql.com/doc/refman/5.7/en/events-overview.html
https://dev.mysql.com/doc/refman/5.7/en/adding-udf.html

Another possibility is to use the "at" command. You could make a call to at and just store the time in the database for reference.

bob
9th June 2017, 03:38 AM
To the OP, please read the Posting Rules and do not double-post. Thanks.

karentutor
9th June 2017, 05:17 AM
Hi Bob

The server in your end fell over (server busy notice when attempted to post). The duplication was an inadvertent error on my part.

Thx

karentutor
9th June 2017, 05:26 AM
Hi Ocrato,

The link for the sql is most accessible for me. However, the link notes : An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in a BEGIN ... END block if desire..

I do not think I that a sql statement can call a script external to the databas, so even if we use MySQL to schedule an event, it will only be able to perform the event upon itself (unless I am mistaken)

Hence I am beginning to wonder if we can dynamically create a crontab job event scheduled based upon the $_post datetime submitted in the actual webpage (the objective). .to call a PHP script at the correct datetime - the storage of the datetime variable in MySQL is just a byproduct of that effort.

The real goal is just to call the PHP script based upon a datetime submitted in a $_post variable.

Thoughts?

Thanks

ocratato
9th June 2017, 05:58 AM
It is up to you to decide between some external process, such as crontab or at, or to keep as much as possible within the database environment. That will depend on your architectural design.

For the database alternative I would use the event scheduler to make some change to a table, and then use that to run a trigger, which would, in turn, run a user function (see the second link in my previous post) which could call system() to run a script.

I would not use crontab as it is aimed at running processes at regular intervals, whereas you need a single shot - which is what the at command is for.

While the at command might be the easiest to get going, it may not scale well. The database solution could be embellished to make it more reliable if that is an important consideration.

karentutor
9th June 2017, 06:06 AM
Very interesting- thank you for your thoughtful explanation of crontab and at. Your database option sounds like the best option and using it to trigger which in turn calls the script is interesting.

I will pursue that as an option and research that first.

Thanks!