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: 410
Location: United Kingdom

PostPosted: Mon Nov 08, 2010 1:30 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
richardyork
-


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

PostPosted: Wed Nov 24, 2010 11:20 am    Post subject: SORTED Reply with quote

Just a quick update. I finally found the problem which has nothing to do with my post above :(

Referrals are not recorded with a sanctionDate! I felt soo silly LOL

So the WHERE clause was the problem:

Code:
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 <= ?


Referrals dont need a sanction date as they are just recorded to keep a sort of formal digtal note of bad behaviour. I have just made it insert a sanctionDate of when it was recorded/submitted and it works fine now :D

Rich
_________________
Please SEARCH the forums BEFORE asking questions!
Back to top View user's profile Send private message
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