richardyork -
Joined: 22 Jun 2004 Posts: 410 Location: United Kingdom
|
Posted: Mon Nov 08, 2010 1:29 pm Post subject: PHP/MYSQL Query Missing Results |
|
|
Hi Folks,
Its been a while since posting for a bit of help and thanks in advance!
I work at a high school in the UK and one of my jobs is to maintain a Behaviour Log system, which was designed to keep a central record of detentons and sanctions etc. Its all web based, developed with php and mysql.
Its continually in development with features been addded all the time.
My latest task was to send out weekly reports to Heads Of Year of the previous weeks detentions/sanctions for their year group via email.
I have managed to do this via Scheduled Tasks and the following script:
Code: | <?php
$timeNow = time();
$timeLastSevenDays = strtotime("-7 days");
$year_num = 7;
// Loop through each year
while($year_num < 14){
logTask("year_alert_weeklysummary", "Searching for students in year ".$year_num);
$year_name = "%".$year_num;
// Fteach the past seven days sanctions
$stmt = prep_stmt("
SELECT
".DB_BL_SANCTIONS.".id,
".DB_BL_SANCTIONS.".upn,
".DB_BL_SANCTIONS.".incidentSubject,
".DB_DETAIL_STUDENTS.".forename,
".DB_DETAIL_STUDENTS.".surname,
".DB_DETAIL_STUDENTS.".year,
".DB_DETAIL_STUDENTS.".reg,
".DB_DETAIL_REG.".initials,
".DB_BL_SANCTIONS.".status,
".DB_BL_SANCTIONS.".level
FROM
".DB_BL_SANCTIONS.",
".DB_DETAIL_STUDENTS.",
".DB_DETAIL_REG."
WHERE
".DB_BL_SANCTIONS.".upn = ".DB_DETAIL_STUDENTS.".upn AND
".DB_DETAIL_STUDENTS.".reg = ".DB_DETAIL_REG.".id AND
".DB_DETAIL_STUDENTS.".year LIKE ? AND
".DB_BL_SANCTIONS.".sanctionDate >= ? AND
".DB_BL_SANCTIONS.".sanctionDate <= ?
ORDER BY ".DB_BL_SANCTIONS.".level DESC, ".DB_BL_SANCTIONS.".status DESC, ".DB_DETAIL_STUDENTS.".forename DESC");
$stmt->bind_param("sss", $year_name, $timeLastSevenDays, $timeNow);
$stmt->execute();
$stmt->bind_result($id, $upn, $subject, $forename, $surname, $year, $reg, $formtutor, $status, $level);
$email_message_subject_ssm = "SSM: Weekly Summary of Behaviour Log";
$email_message_subject_ylm = "YLM: Weekly Summary of Behaviour Log";
$email_for_ylmssm = "Below is a summary from the Behaviour log for the past week<br /><br />";
$email_message_body = "";
// Genearte list for emaiul
while($stmt->fetch()){
//$email_message_body .= '<a href="">DN'.$id.'</A> - '.blLevelName($level).' = '.$forename.' '.$surname.' ('.$reg.") ".blStatusName($status)."<br />";
$email_message_body .= '<a href="'.URL_BEHAVIOUR.'/report_sanctionDetails?id='.$id.'" target="_blank">DN'.$id.'</a> - '.blLevelName($level).' - '.$forename.' '.$surname.' ('.$reg.") ".blStatusName($status)."<br />";
}
// If list generated , send email
if($email_message_body != "")
{
$year = trim(str_replace("Year ", "", $year));
sendMail("SSM".$year.EMAIL_HOST, EMAIL_ADMIN, $email_message_subject_ssm, $email_for_ylmssm.$email_message_body);
sendMail("YLM".$year.EMAIL_HOST, EMAIL_ADMIN, $email_message_subject_ylm, $email_for_ylmssm.$email_message_body);
}
$stmt->close();
$year_num++;
}
logTask("year_alert_weeklysummary", "Completed");
?> |
My Problem:
We have 5 different sanction levels as follows:
Code: | define("BL_LEVEL_NAME_0", "Referral"); // 0 in database - Referral
define("BL_LEVEL_NAME_1", "Level 1"); // 1 in database - Personal
define("BL_LEVEL_NAME_2", "Level 2"); // 2 in database - Group Subject/SSM
define("BL_LEVEL_NAME_3", "Level 3"); // 3 in database - SLT After School
define("BL_LEVEL_NAME_4", "Isolation"); // 4 in database - Isolation (Failure to 3 or Abusive) |
Level 1, 2, 3 and 4 are all included in the summary email if there have been any issued in the past 7 days but Referrals are NOT.
Is this because they are stored as a "0" in the database and not being included in the results because PHP thinks "0" is NULL or something?
Sorry about the lenghthy post and I hope you understand what I mean.
Any aditional info then just ask.
Thank you very much!
Kind Regards,
Richard York _________________ Please SEARCH the forums BEFORE asking questions! |
|