Creating an alert based on a date in a table: PHP MySQL Apache

by: cxrPosted on 2009-01-13 at 09:01:55ID: 23364795

You need a (PHP) script to connect to the database, collect all relevant records, and send emails. Then you need a way to execute this script once every day.

The script could be something like below. You can use windows task scheduler to make this script run once per day, or you can include it in some other php-based application that you know executes every day. In that case you need to store the timestamp for each execution, and make sure it only executes once per day.

<?php 
mysql_connect('localhost','user','pass');
mysql_select_db('database');
$res = mysql_query("select * from tablename 
  where contact_date = curdate() + interval 1 day");
if(!$res) {
  mail('admin@company.com','Error',
    'Error in alert script: '.mysql_error());
  exit;
}
while($row = mysql_fetch_assoc($res)) {
  mail($row['user_email'],'Alert: '.$row['customer'],
    'You need to attend to this customer today or tomorrow.'."\n\n".
    'Phone number: '.$row['customer_phone']."\n".
    'Email: '.$row['customer_email']);
}
?>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

Select all Open in new window

by: DiskoSiskoPosted on 2009-01-13 at 09:13:33ID: 23364910

I don't think it is a windows server so the task scheduler is out of the question... As for a program that i know that executes everyday, i'm not sure that there is one.

I have considered writing a java application that has a thread that will check once a day and run the relevant php script if returns a success, but then this will have to be restarted everytime the server is restarted....

any more ideas??? Is there a linux version of the task scheduler? i just found something called "crontab", anyone know how would i go about using that?

by: Ray_PaseurPosted on 2009-01-13 at 09:27:25ID: 23365058

Yes, the linux version of the task scheduler is called "cron" - for Chronlogical. Cron and Crontab are the same. Who hosts you server?

by: DiskoSiskoPosted on 2009-01-13 at 09:31:04ID: 23365100

I am jus reading a crontab tutorial and it seems to be what i need, i would say. It can run a script for me every day and should restart when the server restarts...

our office admin, the servers are in his office, so should not be a problem gettin access. :D

by: Ray_PaseurPosted on 2009-01-13 at 09:31:51ID: 23365113

Hmm... Another way you might consider dealing with this is via RSS. But I think a cron-triggered job would be the easiest way to do it.

Cron jobs do not produce output to the browser, since they are triggered without a browser. That would make debugging tough, but do not fear. You can have echo, var_dump and things like that in the cron job and you can just run the job from your browser to test it. Once you're satisfied it's OK, you store the script in a permanent place on the server and set up the crontab to run it on schedule. I find that having the cron script send me an email when it runs makes for a reassuring message in the early days of implementation.

How many emails do you think you'll be sending? If it is a lot, you may want to speak with your hosting company about their limits on these things.

Best regards, ~Ray

by: Ray_PaseurPosted on 2009-01-13 at 09:36:10ID: 23365148

Great - if you run your own servers, you are probably nearly home free. Your admin will know about crontab.

Put this into the cron job to see it in action.

<?php // RAY_cron_example.php
 
// SEE THIS ON YOUR BROWSER - IT IS DISCARDED WHEN CRON RUNS THIS SCRIPT
echo "Cron Job Running";
 
// MAIL TO YOU
$email = "you@your.org";
$subj  = "Cron Job Ran";
$text = date('Y-m-d\TH:i:s') . ' TIME OF REQUEST';
 
// SEND MAIL
if (!mail($email, $subj, $text)) die('EMAIL FAILED');
?>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all Open in new window

by: cxrPosted on 2009-01-13 at 09:48:56ID: 23365243

I did not include any output in my suggested script, because automated scripts can not have "normal" output. That is why any potential SQL error is sendt to the admin user as an email in that script.

When developing the script, however, it is usefull to have "normal" output to see what goes wrong. Just include simple echo statements for debugging, and remove these when the script is working.

If you use php or java for this script does not matter, as long as you can communicate with the mysql server and send emails.

by: jbauer22Posted on 2009-01-13 at 11:58:08ID: 23366561

My experience has shown crontab does not like running scripts at times because it uses its own environment. You will need to use absolute paths so cron knows exactly where the script is.

You need to locate your php binary so it can run your script: find / -name php

A sample shell script will need to be saved and called by crontab. Below is a sample script assuming your php script is in /etc/alert.php

Remember if you dont specify the paths, crontab may not process your php script. All files need to have the full path.

#!/bin/bash
/bin/php /etc/alert.php

1:
2:

Select all Open in new window

by: Ray_PaseurPosted on 2009-01-13 at 12:05:12ID: 23366648

jbauer22 makes a good point. Sometimes these things are simplified by a server control panel. Your admin shhould know about that, too.

by: HoggZillaPosted on 2009-01-13 at 12:11:36ID: 23366727

jbauer22 makes a good point. Sometimes these things are simplified by a server control panel. Your admin shhould know about that, too.

Dude, that is exactly what I was going to say .. LOL

by: Ray_PaseurPosted on 2009-01-13 at 12:22:24ID: 23366848

Great minds often think alike!

;-)

Ray

by: DiskoSiskoPosted on 2009-01-14 at 01:43:00ID: 23371077

Right so what do i do now??? This was my first Q.

Who do i award the points to? I think that the "cron" job is the best solution. CXR came close by suggesting the windows task scheduler, he was not to know it was a linux server....

I don't want to break etiquette here, so who do ye think i should award the points to???

by: cxrPosted on 2009-01-14 at 02:16:38ID: 23371244

You can split the points between multiple answers/experts. Choose "Accept Multiple Solutions".

http://www.experts-exchange.com/help.jsp#hi407

by: DiskoSiskoPosted on 2009-01-14 at 02:25:15ID: 23371289

I don't think everybody contributed the same either.....

I think it was very noble of you to suggest that we split the points, therefore I will award the points to you if nobody has any objections, as you put me on the right track.

by: cxrPosted on 2009-01-14 at 05:27:59ID: 23372296

I don't object to that, but you should know that you don't have to split the points equally, you can give some answers/experts more points than others. The total can of course not be more than the number of points assigned to the question (500 in this case).

Remember that these questions/answers go into an archive for others to learn from later. You should assign points to those answers that contributed to the resolution of the question. That way it is easier for other users to find the "best" answers in a question with many answers.

by: Ray_PaseurPosted on 2009-01-14 at 08:47:14ID: 23374599

@DiskoSisko: When you close the question you can split points and it should be obvious to you how to do that from the dialog the site will present. But if it's not, and you find you've given the points out wrong, no need to fret - just click the "request attention" button and ask a moderator to guide you. They will be helpful.

It's nice to see your thoughtfulness about the points, too!

Best to all, ~Ray

by: Ray_PaseurPosted on 2009-01-16 at 07:29:05ID: 23393795

Why did you mark down the grade to a "B" ?? You got very clear assistance and fully accurate answers.

Please see the grading guidelines here, and reconsider:
http://www.experts-exchange.com/help.jsp#hi403

Thank you. ~Ray

Question

Creating an alert based on a date in a table

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

PHP MySQL Apache

MySQL Server

PHP Frameworks

PHP Scripting Language

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press