FAQ  |  Search  |  Register  |  Profile  |  Log in to check your private messages  |  Log in

PHP/MYSQL Query Missing Results

 
Post new topic   Reply to topic    Aprelium Forum Index -> PHP
View previous topic :: View next topic  
Author Message
richardyork
-


Joined: 22 Jun 2004
Posts: 383
Location: United Kingdom

PostPosted: Mon Nov 08, 2010 1:29 pm    Post subject: PHP/MYSQL Query Missing Results Reply with quote

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!
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    Aprelium Forum Index -> PHP All times are GMT + 1 Hour
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB phpBB Group