Hey guys n galls. Im writing a PHP CRON script that scans a MySQL Database for specific cells contents then executes an if statement and then submits emails based on PASS or FAIL, I'm sure I'm giving my self way more work than i need to though.
any comments would be gratefully received in the final CRON file there will be over 20 of these requests all be run every day at 00:0:00.
1: can i somehow call the while loop over and over on the same mysql_query? i seem to have to call it each time for each loop. can i reset the key in the associate array to 0 and re use it again and again?
2: I seem to be getting a lot of "No Database Selected errors", probably due to SQL getting hammered with all these requests.
<?php
/////////////////////////// 01 /////////////////////////////
// Email to Research Assistant (If User account not activated in 4 weeks)
// SET MYSQL REQUEST
// Get all the data from the trial tables
$result = mysql_query("SELECT * FROM trial_registration_info,
trial_survey_results,
trial_module_data,
trial_module_goals,
trial_two_month_follow_up,
trial_twelve_month_follow_up,
trial_twentyfour_month_follow_up,
trial_cron
WHERE
trial_registration_info.ID = trial_survey_results.ID AND
trial_registration_info.ID = trial_module_data.ID AND
trial_registration_info.ID = trial_module_goals.ID AND
trial_registration_info.ID = trial_two_month_follow_up.ID AND
trial_registration_info.ID = trial_twelve_month_follow_up.ID AND
trial_registration_info.ID = trial_twentyfour_month_follow_up.ID AND
trial_registration_info.ID = trial_cron.ID")
or die(mysql_error());
//$mysqlresult = mysql_fetch_array( $result );
while($row = mysql_fetch_array( $result )) {
$today = $date['0'];
$regoPlusOneWeek = mktime(0, 0, 0, date("m", $row['reg_parent_date_created']),
date("d", $row['reg_parent_date_created'])+8,
date("Y", $row['reg_parent_date_created']));
$regoPlusTwoWeek = mktime(0, 0, 0, date("m", $row['reg_parent_date_created']),
date("d", $row['reg_parent_date_created'])+15,
date("Y", $row['reg_parent_date_created']));
$regoPlusThreeWeek = mktime(0, 0, 0, date("m", $row['reg_parent_date_created']),
date("d", $row['reg_parent_date_created'])+22,
date("Y", $row['reg_parent_date_created']));
$regoPlusOneMonth = mktime(0, 0, 0, date("m", $row['reg_parent_date_created'])+1,
date("d", $row['reg_parent_date_created'])+1,
date("Y", $row['reg_parent_date_created']));
$todayHuman = date("l dS \of F Y", $today);
$regoPlusOneWeekHuman = date("l dS \of F Y", $regoPlusOneWeek);
$regoPlusTwoWeekHuman = date("l dS \of F Y", $regoPlusTwoWeek);
$regoPlusThreeWeekHuman = date("l dS \of F Y", $regoPlusThreeWeek);
$regoPlusOneMonthHuman = date("l dS \of F Y", $regoPlusOneMonth);;
$regoDate = date("l dS \of F Y", $row['reg_parent_date_created']);
if ( $today >= $regoPlusOneMonth && !$row['reg_account_status'] && !$row['email_one']) {
echo "<p class='success'>EMAIL 01 - ".$row['reg_parent_fname']."'s Email 01 was sent to the to Research Assistant on $regoPlusOneMonthHuman </p>";
// UPDATE DATABASE - email_one sent
$update_stamp = mysql_query("UPDATE trial_cron SET email_one = '1' WHERE ID = '".$row['ID']."'")
or die(mysql_error());
// SEND EMAIL 01
}
usleep(100);
?>
<p> </p>
<hr>
<p> </p>
<p>
<?
/////////////////////////// 02 /////////////////////////////
// Email to User (If Survey #1 not complete in 1 week)
// SET MYSQL REQUEST
// Get all the data from the trial tables
$result = mysql_query("SELECT * FROM trial_registration_info,
trial_survey_results,
trial_module_data,
trial_module_goals,
trial_two_month_follow_up,
trial_twelve_month_follow_up,
trial_twentyfour_month_follow_up,
trial_cron
WHERE
trial_registration_info.ID = trial_survey_results.ID AND
trial_registration_info.ID = trial_module_data.ID AND
trial_registration_info.ID = trial_module_goals.ID AND
trial_registration_info.ID = trial_two_month_follow_up.ID AND
trial_registration_info.ID = trial_twelve_month_follow_up.ID AND
trial_registration_info.ID = trial_twentyfour_month_follow_up.ID AND
trial_registration_info.ID = trial_cron.ID")
or die(mysql_error());
while($row = mysql_fetch_array( $result )) {
$today = $date['0'];
$regoPlusOneWeek = mktime(0, 0, 0, date("m", $row['reg_parent_date_created']),
date("d", $row['reg_parent_date_created'])+8,
date("Y", $row['reg_parent_date_created']));
$regoPlusTwoWeek = mktime(0, 0, 0, date("m", $row['reg_parent_date_created']),
date("d", $row['reg_parent_date_created'])+15,
date("Y", $row['reg_parent_date_created']));
$regoPlusThreeWeek = mktime(0, 0, 0, date("m", $row['reg_parent_date_created']),
date("d", $row['reg_parent_date_created'])+22,
date("Y", $row['reg_parent_date_created']));
$regoPlusOneMonth = mktime(0, 0, 0, date("m", $row['reg_parent_date_created'])+1,
date("d", $row['reg_parent_date_created'])+1,
date("Y", $row['reg_parent_date_created']));
$todayHuman = date("l dS \of F Y", $today);
$regoPlusOneWeekHuman = date("l dS \of F Y", $regoPlusOneWeek);
$regoPlusTwoWeekHuman = date("l dS \of F Y", $regoPlusTwoWeek);
$regoPlusThreeWeekHuman = date("l dS \of F Y", $regoPlusThreeWeek);
$regoPlusOneMonthHuman = date("l dS \of F Y", $regoPlusOneMonth);;
$regoDate = date("l dS \of F Y", $row['reg_parent_date_created']);
if ($today >= $regoPlusOneWeek && isset($row['reg_account_status']) && !$row['survey_one_compete'] && !$row['email_two']) {
echo "<p class='success'>EMAIL 02 - ".$row['reg_parent_fname']."'s Email 02 was sent to the to PARENT on $regoPlusOneMonthHuman </p>";
// UPDATE EMAIL TWO to SENT
$update_stamp = mysql_query("UPDATE trial_cron SET email_two = '1' WHERE ID = '".$row['ID']."'")
or die(mysql_error());
// SEND EMAIL 02
}
usleep(100);
?>
