PHP/MYSQL Query Missing Results

[richardyork]

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:

 

 

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

 

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

 

[PFMaBiSmAd]

blLevelName($level)

 

^^^ You have a function that changes the $level value into a name. You probably have logic error in that function that is causing the symptom.

[richardyork]

Thanks for the reply!

 

As far as I can see, the function you mentioned hasnt got any issues.

 

blLevelName Function:

// Convert level id into text value
function blLevelName($value){
if($value == 0){
	return BL_LEVEL_NAME_0;
} else if($value == 1){
	return BL_LEVEL_NAME_1;
} else if($value == 2){
	return BL_LEVEL_NAME_2;
} else if($value == 3){
	return BL_LEVEL_NAME_3;
} else if($value == 4){
	return BL_LEVEL_NAME_4;
} else if($value == 5){
	return BL_LEVEL_NAME_5;
}
 else {
	return "Unknown: ".$value;
}
}

 

Im at an absolute loss! I have no idea why its not working.

 

Its just the Referrals that are not being included in the email thats generated, which have a value of 0 in the  database. I think this is part of the problem??

 

Thanks again,

 

Rich

[PFMaBiSmAd]

I recommend that you form your query in a variable, echo the query after it has been formed, put in the actual values for the parameters, and copy/paste the query into your favorite database management tool, such as phpmyadmin, and execute the query directly against your data base to see if it returns the excepted results.

 

I assume for the date range you are using, that you actually have data with that value that should be returned?

 

What does show up in the 'view source' of the mail where your missing values should be?

[PFMaBiSmAd]

This has nothing to do with your problem, but I recommend using an array to map values to be converted so that you can simply modify the data instead of editing conditional logic every time you change any of the mapping -

function blLevelName($value){
$lookup = array();
$lookup[0] = BL_LEVEL_NAME_0;
$lookup[1] = BL_LEVEL_NAME_1;
$lookup[2] = BL_LEVEL_NAME_2;
$lookup[3] = BL_LEVEL_NAME_3;
$lookup[4] = BL_LEVEL_NAME_4;

if(isset($lookup[$value])){
	return $lookup[$value];
} else {
	return "Unknown: ".$value;
}
}

[DavidAM]

This has nothing to do with your problem, but I recommend using an array to map values to be converted so that you can simply modify the data instead of editing conditional logic every time you change any of the mapping -

 

Why build the array every time you call the function, why not just use the constant() function:

function blLevelName($value){
$const = 'BL_LEVEL_NAME_' . $value;
if (defined($const)) return constant($const);
else                 return 'Unknown: ' . $value;
}

 

At the very least use a switch!

 

 

OP:  Your post indicates you are not getting the Referrals. Do they NOT show up at all? or Are you getting the "Unknown" value?  If they are not showing up, I would take a look at the INSERT statement that is putting them into the database. This SELECT query should return them if they are there.

 

Is the Status column ever zero? and Do you have similar problems with it?