Create cron job to send email and database CRUD

Black Sulfur
Black Sulfur used Ask the Experts™
on
Can anyone please point me in the direction of a good tutorial or even give examples here of how to create a cron job to send a time based email and update, edit or delete a mysql database record or records if that is possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Hey Black Sulfur,

There are effectively 2 parts to your question. The first is just a standard PHP script. The second is about scheduling the running of that script. I would suggest you write the script first and test it out manually - either by calling it from the command line, or by calling it in a browser. Once you know the script is working, then you can schedule it to run using cron.

Can't give you any specific examples at the moment becuse you haven't really given any details of what you need.

Create your file and code it to connect to your database, do whatever is needed on the DB and then send an email. Forget about it being a cron job - just write it as if you were writing a normal PHP script (less all the HTML stuff).

Once you've got that working, then you need to schedule it. Exactly how you do that will depend on your system. For example running crontab -e from the terminal will prompy you to create a new task in the current users schedule. You would add an entry such as this:

0 0 * * * php /path/to/your/script.php

That would schedule your script to run at midnight every day.

If you need to run the task as a different user, then you would edit the /etc/crontab file (and specify the user to run it under)

Author

Commented:
Thanks Chris, an example of one of the things I want to do is if a user buys something but chooses wire transfer as their method of payment, they have 24 hours in which to pay. If they don't pay within 24 hours the order must cancel.

So, I will write the query that puts the item back in stock and cancels the order but I just don't know how to execute that code automatically every 24 hours. At the same time an email should be sent to the customer to tell them their order has been cancelled. That would also be code that forms part of the database query code.

So, the code would have to loop through all orders and check the status as well as date/time of order. For each record that is pending and the 24 hours has elapsed, those records need to update and those customers emailed.

Okay, so I could just create a model and controller to run the code I want and point the cron job to that url? Would I need to create view for it or could I point directly to the controller? And how could I prevent people typing in that url manually? They would obviously need to somehow figure out the url but I don't want to leave it to chance.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
OK. The fact that you're talking about controllers, models and URLs makes me think you're talking about calling a webpage rather than script. haven't done that before, but I guess you could schedule in a curl request to the webpage.

What I was talking about was creating a standalone PHP script and scheduling that in:

<?php
// Filename: restock.php
require __DIR__ . '/vendor/autoload.php';
require_once 'db.php';

// Do your DB stuff
$results = $db->query("SELECT ...");
...

// Send your email
$mail = new PHPMailer();
....
$mail->send();

Open in new window

Once you've got your script working, then just schedule it in, deciding how often you want to run it. The following would schedule it in to run every 15 minutes:

*/15 * * * * php /path/to/restock.php

You can edit your crontab by typing crontab -e at the terminal and typing in the above schedule.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Author

Commented:
Oh, I see. So I would need to just create one php page that has everything in it like the database connection code and the email script. Where would that script live in relation to the MVC folder structure as it is more complicated with MVC (I think)? I wouldn't really want to put it in the public folder and not sure if it would run anywhere else.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
It'll just be a standard PHP script, so technically you can put it wherever you like. I would probably suggest a folder above your public_html folder (so it's not publicly accessible), called something like scripts. Drop it in there and include other files as needed (db connection / PHPMailer / models etc). When you schedule it in, you just put in the path to the script.

Author

Commented:
Ah, okay. Will give that a try in the next day or two and post back. Sorry for delay but it will take me a while to set it up...
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
No worries,

While you're testing, it probably makes sense to keep it in your public folder so that you can just call it from within your browser. You can also output (var_dump etc) data to the screen so you can see what's going on. Once you know it's working, then move it to the other folder and test it against the terminal.
Just my 2 cents: seems you are used to think to php in term of web pages.

THis is slightly unexact: php scripts are just that, programs that run on the server and which may output something. Usually this something is sent to a web client which consumes it as html at large (ie, including css and js).

But you can also write some php scripts that are not targeted at feeding some web page, but just the Linux shell (or whatever) as would do a bash script

Author

Commented:
Okay, so that was pretty fun. I created a php script to connect to the database and just insert a record into the database. I created the cron job to do that every minute and it worked, eventually. The first few times I kept getting the path incorrect and it would email me with the errors. Once I got it right it inserted rows every minute and the emails stopped coming.

Is that how it should work? It will only email me if there was an error running the job?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
I think that cron will email any output of the script, so if your script errored and produced some output, that would get send. If your script doesn't produce any output, then nothing will be sent. You could try outputting information after the successful insertion of te DB record and see if that gets sent.

Alternatively, you could just include the relevant mailer code into your script (PHPMailer for example). This way, you get to control the email that is sent on success, and allow the system to generate an email on failure (script output)

Author

Commented:
Thanks Chris!
Thx for the points.

One comment: running your script every minute is probably overkill. I would probably run every 10 or 15 minutes (unless you really have tons of traffic)

For your php programs: note that in adition to your transaction , you might amso decide at some leter stand some enhacements loke generating stats, more precisly data for stats

Author

Commented:
Hi Bernard,

I do not intend to run the script every minute, only once every 24 hours. I just set it to a minute because I wanted to test it and wanted to wait the shortest time possible to see the results for debugging. Waiting even 5 minutes just for testing would have become really tedious.
B-) Then I would probably run twice a day; that means that the average overtime would be 6 hours, not 12??? but that really relies on your business

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial