Create cron job to send email and database CRUD

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?
LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
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)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor 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.
0
Chris StanyonWebDevCommented:
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.
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Black SulfurAuthor 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.
0
Chris StanyonWebDevCommented:
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.
0
Black SulfurAuthor 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...
0
Chris StanyonWebDevCommented:
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.
0
Bernard S.CTOCommented:
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
0
Black SulfurAuthor 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?
0
Chris StanyonWebDevCommented:
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)
1
Black SulfurAuthor Commented:
Thanks Chris!
0
Bernard S.CTOCommented:
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
0
Black SulfurAuthor 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.
2
Bernard S.CTOCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.