PHP Report Help
Posted: Wed Dec 03, 2008 12:08 pm
Hello,
I am the newest of new to PHP. I have a script that creates a report and within this report there is a field "sugar"
Currently it just reports avg sugar. I would like to add Min and Max to the report for sugar. As you can see there is already a min, max, avg for "rind thickness".
I would like the data to report in the same format as the rind thickness.
Can anyone help me out with this please?
Thank you in advance.
Here is the current report:
<?php
ini_set("display_errors",0);
##########################################################################
# VarietyReport4.php
# This will be a variety report that sends emails every morning
# regarding the average brix and sugar for different varieties
# adding seperation by location hopefully this works.
# This incorporates locations
# This build incorporates average brix as well
#
# Adding Avg rind thickness for mini's
# RW 5-31-06
###########################################################################
// making a nice time stamp for yesterday
//$yesterday = mktime (date ("H"), date ("i"), date ("s"), date("m"), date ("d") - 1, date("Y"));
$yesterday = mktime (0, 0, 0, date("m"), date ("d") - 1, date("Y"));
//yesterday_nice will be used for the reports
$yesterday_nice = date("m-d-Y",$yesterday);
$yesterday = date ("Y-m-d H:i:s", $yesterday);
//echo $yesterday;
#connect to the database server
$db = mysql_connect("localhost","timcowor_dbuser","radiohead");
#select database we want to use
mysql_select_db("timcowor_qa");
//get all locations that reports originated from yesterday.
$sql = "select distinct reports.location,locations.loc_desc,growers.grower_description from reports,locations,growers where reports.location = locations.location_id and locations.grower_id = growers.grower_id and created_date > '$yesterday'";
//echo $sql;
//all dates
//$sql = "select distinct reports.location,locations.loc_desc,growers.grower_description from reports,locations,growers where reports.location = locations.location_id and locations.grower_id = growers.grower_id";
$result = mysql_query($sql);
//if we have no results we don't need to share them with anyone.
if (mysql_num_rows($result) == 0){
exit();
}
echo mysql_error();
//making a local variable that has all my results
while ($data = mysql_fetch_array($result))
{
$locations[] = $data;
}
//
mysql_free_result($result);
unset($data);
$msg_body = "Variety Performance Report for $yesterday_nice \n\n";
foreach($locations as $location){
//this gives us all the fruit varieties reported yesterday
$sql = " select distinct varieties.type_id, varieties.variety_description, reports.variety_id from reports, varieties
where reports.variety_id = varieties.variety_id and created_date > '$yesterday' and location = ".$location["location"];
#echo $sql;
//this gives is all the varieties ever posted.
//$sql = " select distinct varieties.type_id, varieties.variety_description, reports.variety_id from reports, varieties
// where reports.variety_id = varieties.variety_id and location = ".$location["location"];
$result = mysql_query($sql);
//making a local variable that has all my results
while ($data = mysql_fetch_array($result))
{
$varieties[] = $data;
}
//
mysql_free_result($result);
unset($data);
$msg_body .= "\n----------".$location["grower_description"]." * ".$location["loc_desc"]."-------------\n";
foreach ($varieties as $variety){
//echo $variety["variety_description"]."<br />";
//count of all fruit tested by a specific variety and get their average pressure and average sugar
$sql = "select count(sugar) as count, avg(average_pressure) as pressure, avg(sugar) as average_sugar from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and location = ".$location["location"];
#echo $sql;
//all dates
//$sql = "select count(sugar) as count, avg(average_pressure) as pressure, avg(sugar) as average_sugar from reports where variety_id = ".$variety["variety_id"] ." and location = ".$location["location"];
$result = mysql_query($sql);
//this is the total number of records collected for that fruit
$data = mysql_fetch_array($result);
$rows_total = $data["count"];
$average_sugar = round($data["average_sugar"],2);
$pressure = round($data["pressure"],2);
mysql_free_result($result);
//sugar below 10 for yesterday
$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and sugar < 10 and location = ".$location["location"];
//sugar below 10 for all fruits
//$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and sugar < 10 and location = ".$location["location"];
$result = mysql_query($sql);
$data = mysql_fetch_array($result);
$rows_sugar = $data["count"];
mysql_free_result($result);
//setting minimum pressure perameters for each fruit type
//could add this to the database under each fruit type variety
switch ($variety["type_id"])
{
case 1://seeded
$min_pressure = 2.5;
break;
case 2://seedless
$min_pressure = 2.5;
break;
case 3://mini
$min_pressure = 2.5;
break;
case 4://honeydew
$min_pressure = 5;
break;
case 5://canteloup
$min_pressure = 4.5;
break;
}//end switch
//here we get the amount of fruit that failed to pass pressure qualiy standards
$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and average_pressure < $min_pressure and location = ".$location["location"];
//all dates
//$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and average_pressure < $min_pressure and location = ".$location["location"];
$result = mysql_query($sql);
$data = mysql_fetch_array($result);
$rows_pressure = $data["count"];
mysql_free_result($result);
//adding rind_thickness for mini's
//here we get the amount of fruit that failed to pass pressure qualiy standards
if ($variety["type_id"] == 3){
$sql = "select max(rind_thickness) as max_rind, min(rind_thickness) as min_rind,avg(rind_thickness) as avg_rind from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and location = ".$location["location"];
$result = mysql_query($sql);
$data = mysql_fetch_array($result);
$max_rind = $data["max_rind"];
$min_rind = $data["min_rind"];
$avg_rind = round($data["avg_rind"],2);
mysql_free_result($result);
}
$percentbad_sugar = ($rows_sugar/$rows_total)*100;
$percentbad_sugar = round($percentbad_sugar,2);
$percentbad_pressure = ($rows_pressure/$rows_total)*100;
$percentbad_pressure = round($percentbad_pressure,2);
$msg = " Variety: ".$variety["variety_description"]. "\n";
$msg .= " Total Samples: $rows_total\n";
$msg .= " Average Pressure: $pressure psi\n";
$msg .= " Average Brix: $average_sugar %\n\n";
$msg .= " Samples with brix less than ten: $rows_sugar\n";
$msg .= " Percent below ten brix: $percentbad_sugar %\n\n";
$msg .= " Samples with pressure below $min_pressure: $rows_pressure\n";
$msg .= " Percent below $min_pressure pressure: $percentbad_pressure %\n\n";
if ($variety["type_id"] == 3){
$msg .= " Rind Thickness:\n";
$msg .= " Max: $max_rind mm\n";
$msg .= " Min: $min_rind mm\n";
$msg .= " Avg: $avg_rind mm\n";
}
switch ($variety["type_id"])
{
case 1:
$seeded_msg .= $msg."\n";
break;
case 2:
$seedless_msg .= $msg."\n";
break;
case 3:
$mini_msg .= $msg."\n";
break;
case 4:
$honeydew_msg .= $msg."\n";
break;
case 5:
$canteloupe_msg .= $msg."\n";
break;
}//end switch
}//end foreach variety
unset($varieties);
if (($seeded_msg) != "")
{
$msg_body .= "\n Seeded Watermelons:\n\n" . $seeded_msg;
}
if (($seedless_msg) != "")
{
$msg_body .= "\n Seedless Watermelons:\n\n" . $seedless_msg;
}
if (($mini_msg) != "")
{
$msg_body .= "\n Mini Watermelons:\n\n" . $mini_msg;
}
if (($honeydew_msg) != "")
{
$msg_body .= "\n Honeydews:\n\n" . $honeydew_msg;
}
if (($canteloupe_msg) != "")
{
$msg_body .= "\n Cantaloupe:\n\n" . $canteloupe_msg;
}
unset($seeded_msg);
unset($seedless_msg);
unset($mini_msg);
unset($honeydew_msg);
unset($canteloupe_msg);
I am the newest of new to PHP. I have a script that creates a report and within this report there is a field "sugar"
Currently it just reports avg sugar. I would like to add Min and Max to the report for sugar. As you can see there is already a min, max, avg for "rind thickness".
I would like the data to report in the same format as the rind thickness.
Can anyone help me out with this please?
Thank you in advance.
Here is the current report:
<?php
ini_set("display_errors",0);
##########################################################################
# VarietyReport4.php
# This will be a variety report that sends emails every morning
# regarding the average brix and sugar for different varieties
# adding seperation by location hopefully this works.
# This incorporates locations
# This build incorporates average brix as well
#
# Adding Avg rind thickness for mini's
# RW 5-31-06
###########################################################################
// making a nice time stamp for yesterday
//$yesterday = mktime (date ("H"), date ("i"), date ("s"), date("m"), date ("d") - 1, date("Y"));
$yesterday = mktime (0, 0, 0, date("m"), date ("d") - 1, date("Y"));
//yesterday_nice will be used for the reports
$yesterday_nice = date("m-d-Y",$yesterday);
$yesterday = date ("Y-m-d H:i:s", $yesterday);
//echo $yesterday;
#connect to the database server
$db = mysql_connect("localhost","timcowor_dbuser","radiohead");
#select database we want to use
mysql_select_db("timcowor_qa");
//get all locations that reports originated from yesterday.
$sql = "select distinct reports.location,locations.loc_desc,growers.grower_description from reports,locations,growers where reports.location = locations.location_id and locations.grower_id = growers.grower_id and created_date > '$yesterday'";
//echo $sql;
//all dates
//$sql = "select distinct reports.location,locations.loc_desc,growers.grower_description from reports,locations,growers where reports.location = locations.location_id and locations.grower_id = growers.grower_id";
$result = mysql_query($sql);
//if we have no results we don't need to share them with anyone.
if (mysql_num_rows($result) == 0){
exit();
}
echo mysql_error();
//making a local variable that has all my results
while ($data = mysql_fetch_array($result))
{
$locations[] = $data;
}
//
mysql_free_result($result);
unset($data);
$msg_body = "Variety Performance Report for $yesterday_nice \n\n";
foreach($locations as $location){
//this gives us all the fruit varieties reported yesterday
$sql = " select distinct varieties.type_id, varieties.variety_description, reports.variety_id from reports, varieties
where reports.variety_id = varieties.variety_id and created_date > '$yesterday' and location = ".$location["location"];
#echo $sql;
//this gives is all the varieties ever posted.
//$sql = " select distinct varieties.type_id, varieties.variety_description, reports.variety_id from reports, varieties
// where reports.variety_id = varieties.variety_id and location = ".$location["location"];
$result = mysql_query($sql);
//making a local variable that has all my results
while ($data = mysql_fetch_array($result))
{
$varieties[] = $data;
}
//
mysql_free_result($result);
unset($data);
$msg_body .= "\n----------".$location["grower_description"]." * ".$location["loc_desc"]."-------------\n";
foreach ($varieties as $variety){
//echo $variety["variety_description"]."<br />";
//count of all fruit tested by a specific variety and get their average pressure and average sugar
$sql = "select count(sugar) as count, avg(average_pressure) as pressure, avg(sugar) as average_sugar from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and location = ".$location["location"];
#echo $sql;
//all dates
//$sql = "select count(sugar) as count, avg(average_pressure) as pressure, avg(sugar) as average_sugar from reports where variety_id = ".$variety["variety_id"] ." and location = ".$location["location"];
$result = mysql_query($sql);
//this is the total number of records collected for that fruit
$data = mysql_fetch_array($result);
$rows_total = $data["count"];
$average_sugar = round($data["average_sugar"],2);
$pressure = round($data["pressure"],2);
mysql_free_result($result);
//sugar below 10 for yesterday
$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and sugar < 10 and location = ".$location["location"];
//sugar below 10 for all fruits
//$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and sugar < 10 and location = ".$location["location"];
$result = mysql_query($sql);
$data = mysql_fetch_array($result);
$rows_sugar = $data["count"];
mysql_free_result($result);
//setting minimum pressure perameters for each fruit type
//could add this to the database under each fruit type variety
switch ($variety["type_id"])
{
case 1://seeded
$min_pressure = 2.5;
break;
case 2://seedless
$min_pressure = 2.5;
break;
case 3://mini
$min_pressure = 2.5;
break;
case 4://honeydew
$min_pressure = 5;
break;
case 5://canteloup
$min_pressure = 4.5;
break;
}//end switch
//here we get the amount of fruit that failed to pass pressure qualiy standards
$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and average_pressure < $min_pressure and location = ".$location["location"];
//all dates
//$sql = "select count(sugar) as count from reports where variety_id = ".$variety["variety_id"] ." and average_pressure < $min_pressure and location = ".$location["location"];
$result = mysql_query($sql);
$data = mysql_fetch_array($result);
$rows_pressure = $data["count"];
mysql_free_result($result);
//adding rind_thickness for mini's
//here we get the amount of fruit that failed to pass pressure qualiy standards
if ($variety["type_id"] == 3){
$sql = "select max(rind_thickness) as max_rind, min(rind_thickness) as min_rind,avg(rind_thickness) as avg_rind from reports where variety_id = ".$variety["variety_id"] ." and created_date > '$yesterday' and location = ".$location["location"];
$result = mysql_query($sql);
$data = mysql_fetch_array($result);
$max_rind = $data["max_rind"];
$min_rind = $data["min_rind"];
$avg_rind = round($data["avg_rind"],2);
mysql_free_result($result);
}
$percentbad_sugar = ($rows_sugar/$rows_total)*100;
$percentbad_sugar = round($percentbad_sugar,2);
$percentbad_pressure = ($rows_pressure/$rows_total)*100;
$percentbad_pressure = round($percentbad_pressure,2);
$msg = " Variety: ".$variety["variety_description"]. "\n";
$msg .= " Total Samples: $rows_total\n";
$msg .= " Average Pressure: $pressure psi\n";
$msg .= " Average Brix: $average_sugar %\n\n";
$msg .= " Samples with brix less than ten: $rows_sugar\n";
$msg .= " Percent below ten brix: $percentbad_sugar %\n\n";
$msg .= " Samples with pressure below $min_pressure: $rows_pressure\n";
$msg .= " Percent below $min_pressure pressure: $percentbad_pressure %\n\n";
if ($variety["type_id"] == 3){
$msg .= " Rind Thickness:\n";
$msg .= " Max: $max_rind mm\n";
$msg .= " Min: $min_rind mm\n";
$msg .= " Avg: $avg_rind mm\n";
}
switch ($variety["type_id"])
{
case 1:
$seeded_msg .= $msg."\n";
break;
case 2:
$seedless_msg .= $msg."\n";
break;
case 3:
$mini_msg .= $msg."\n";
break;
case 4:
$honeydew_msg .= $msg."\n";
break;
case 5:
$canteloupe_msg .= $msg."\n";
break;
}//end switch
}//end foreach variety
unset($varieties);
if (($seeded_msg) != "")
{
$msg_body .= "\n Seeded Watermelons:\n\n" . $seeded_msg;
}
if (($seedless_msg) != "")
{
$msg_body .= "\n Seedless Watermelons:\n\n" . $seedless_msg;
}
if (($mini_msg) != "")
{
$msg_body .= "\n Mini Watermelons:\n\n" . $mini_msg;
}
if (($honeydew_msg) != "")
{
$msg_body .= "\n Honeydews:\n\n" . $honeydew_msg;
}
if (($canteloupe_msg) != "")
{
$msg_body .= "\n Cantaloupe:\n\n" . $canteloupe_msg;
}
unset($seeded_msg);
unset($seedless_msg);
unset($mini_msg);
unset($honeydew_msg);
unset($canteloupe_msg);