Page 1 of 1
Automate Alerts
Posted: Wed Jun 09, 2010 3:19 pm
by rklockner
I was just asked to build a notification system to alert our users of upcoming actions. I have a function built that will send out an email and/or a text message depending on a user's settings, and I can call this function to send notifications that certain actions were made (i.e. a new client signed up through their website), but this requires someone to click a button (or some other action).
My first thought was, have a table that is used for "Pending Future Messages" that on each paged accessed by any user in the software there is a query that checks if any notifications need to be sent (and send them if they are there).
Obviously that is not a good option, there are too many issues. The biggest being, what if no one is logged into the software? Can anyone point me in a direction to run SQL queries with no one on the software?
Re: Automate Alerts
Posted: Wed Jun 09, 2010 3:26 pm
by John Cartwright
If you are on Linux, then use a cron job to execute a PHP script, and on Windows, use TaskManager.
Re: Automate Alerts
Posted: Wed Jun 09, 2010 4:15 pm
by rklockner
I've never worked with cron jobs, but there seem to be a lot of tutorials out there. Seems easy enough...
Re: Automate Alerts
Posted: Thu Jun 10, 2010 4:22 pm
by rklockner
I have played with this for a while, and I have hit a wall. After reading a few tutorials, it would seem you create a php page that does the required action, then have the cron job "run" that page. It is finding the page, but is not recognizing the php. It is configured to send me an email to let me know the result. This is what is in the email:
"
/var/www/vhosts/site.com/httpdocs/cron_send_alerts.php: line 1: ?php
: No such file or directory
/var/www/vhosts/site.com/httpdocs/cron_send_alerts.php: line 2:
: command not found
/var/www/vhosts/site.com/httpdocs/cron_send_alerts.php: line 3: syntax error near unexpected token `{ '
/var/www/vhosts/site.com/httpdocs/cron_send_alerts.php: line 3: `function mysqlconn() { '
"
NOTE: This page runs fine if I access it through the website, just now when the cron job accesses the page.
Code: Select all
<?php
function mysqlconn() {
$conn = @mysql_connect('localhost', 'user', 'pass') or die("Could not login to the MySQL server.");
mysql_select_db('db', $conn) or die("Could not find the database name $dbname.");
return $conn;
}
$conn = mysqlconn();
$todays_date = date('Y-m-d');
$current_hour = date('H');
$current_minute = date('i');
$current_seconds = date('s');
$current_minute = $current_minute+30;
if ($current_minute > 59){
$current_hour = $current_hour + 1;
$current_minute = $current_minute - 59;
}
if ($current_hour > 24){
$current_hour = $current_hour - 24;
}
$search_time = $current_hour.":".$current_minute.":".$current_seconds;
$sql = "SELECT * FROM send_alerts WHERE processed = '0' AND send_date <= '$todays_date' AND send_time <= '$search_time'";
$i=0;
$selectresult = mysql_query($sql,$conn) or die(mysql_error());
if (mysql_num_rows($selectresult) >0) {
while ($row=mysql_fetch_array($selectresult)){
$alertid[] = $row['alertid'];
$toid[] = $row['toid'];
$fromid[] = $row['fromid'];
$subject[] = $row['subject'];
$message[] = $row['message'];
$send_date[] = $row['send_date'];
$send_time[] = $row['send_time'];
}
}
for ($i=0; $i < count($alertid); $i++){
send_alert($toid[$i], $message[$i], $subject[$i], $fromid[$i]);
//Need to write a script to set successfully sent alerts to processed
$sql = "UPDATE send_alerts SET processed='1' WHERE alertid='$alertid[$i]'";
mysql_query($sql,$conn) or die(mysql_error());
}
?>
Re: Automate Alerts
Posted: Thu Jun 10, 2010 4:33 pm
by AbraCadaver
You need to tell the shell to run the script using PHP. The two ways are to specify it in the script or I would specify it in the cron line.
In script (cron_send_alerts.php would also need to be set executable 'chmod +x cron_send_alerts.php'):
Code: Select all
#!/usr/bin/php
<?php
function mysqlconn() {
//etc, etc, etc
In cron:
[text]* * * * * /usr/bin/php /var/www/vhosts/epartnersoftware.com/httpdocs/cron_send_alerts.php[/text]
You can tell where php is located by doing a 'which php' at the command line.
Re: Automate Alerts
Posted: Thu Jun 10, 2010 4:43 pm
by rklockner
/usr/bin/php
is the response I got from SSH.
would I set this as follows?
/usr/bin/php -q /var/www/vhosts/domain.com/httpdocs/cron_send_alerts.php
Re: Automate Alerts
Posted: Thu Jun 10, 2010 4:53 pm
by rklockner
Got it. Just successfully ran the script. Thanks for your help!!!