Hi,
I have report form created that consist of reject_type per process and rejects per compound_type.
Now, I have a problem in displaying the total rejects per reject_type.
I ued array_key_exists and I’m not too familiar on that syntax.
here is my code:
<?php
ob_start();
include "connection.php";
/*
Assuming that $id is called by other means but
defined here for testing. So instead of checking
IF $id is a particular number, we check that it is set
and query for specific week without adjustment in query
*/
$id = "30";
//$id = $_POST['id'];
if(isset($id)){
/*
We are building an array of all information with a single query.
*/
$Reports = array();
$dates = array();
$process_names = array();
$reject_types = array();
$compound_types = array();
$rejects = array();
$reject_total = array();
$sql = "SELECT
r.reject_date,
r.reject,
r.process_id,
r.reject_type,
r.compound_type,
p.process_name
FROM op_reject AS r
JOIN process_list AS p
ON (p.process_id = r.process_id)
WHERE WEEK(reject_date)+1= '$id'
ORDER BY p.process_id, r.reject_date, r.compound_type ASC";
$res = mysql_query($sql);
while($row = mysql_fetch_assoc($res)){
//Would have been nice to work off this single array, but found it difficult so I built sub-arrays below.
$Reports[$row['reject_date']][$row['process_name']][$row['reject_type']][$row['compound_type']][] = $row['reject'];
//build some unique sub arrays for displaying data
if (!in_array($row['reject_date'],$dates)){
$dates[] = $row['reject_date'];
}
if (!in_array($row['process_name'],$process_names)){
$process_names[] = $row['process_name'];
}
if (array_key_exists($row['process_name'],$compound_types) && !in_array($row['compound_type'],$compound_types[$row['process_name']]) ||
!array_key_exists($row['process_name'],$compound_types)){
$compound_types[$row['process_name']][] = $row['compound_type'];
}
$reject_types[$row['reject_date']][$row['process_name']][] = $row['reject_type'];
$rejects[$row['reject_date']][$row['process_name']][$row['reject_type']][$row['compound_type']] = $row['reject'];
}
//----query for total rejec
$sql_total = "SELECT
r.reject_date,
SUM(r.reject) AS reject_total,
r.process_id,
r.reject_type,
p.process_name
FROM op_reject AS r
JOIN process_list AS p
ON (p.process_id = r.process_id)
WHERE WEEK(reject_date)+1= '$id'
GROUP BY r.reject_type
ORDER BY r.reject_date, reject_type, p.process_id ASC";
$res_total = mysql_query($sql_total);
while($row_total = mysql_fetch_assoc($res_total)){
$Reports[$row_total['reject_date']][$row_total['process_name']][$row_total['reject_type']] = $row_total['reject_total'];
//build some unique sub arrays for displaying data
$rejects_total[$row_total['reject_date']][$row_total['process_name']][$row_total['reject_type']] = $row_total['reject_total'];
}
//echo "<pre>";
//print_r($Reports);
//print_r($dates);
//print_r($process_names);
//print_r($compound_types);
//print_r($reject_types);
//print_r($rejects);
//echo "</pre>";
////Build display data BEFORE output to browser////
$display = "<table border=1 cellpadding=2 cellspacing=0>
<thead>
<tr>
<th>Compound</th>\\r";
//I'll keep your id identifier in place
$i=1;
foreach($dates as $date){
$display .= "<th id=col".$i." colspan=\\"8\\">$date</th>\\r";
$i++;
}
$display .= "</tr>
</thead>";
$datecnts = array();
foreach($process_names as $process_name){
$display .= "<tr>\\r";
$display .= "<td>$process_name</td>\\r";
foreach($dates as $date){
if (array_key_exists($process_name,$Reports[$date])){
$datecnts[$date] = count($Reports[$date][$process_name]);
$d=0;
foreach($Reports[$date][$process_name] as $reject_type => $arry){
$display .= "<td>$reject_type</td>\\r";
$d++;
}
}else{
for($c=0;$c<=$d;$c++){
$display .= "<td> </td>\\r";
}
}
}
$display .= "</tr>\\r";
// Even with sub-arrays it get quite messy checking for all the keys and attempting to fill empty table cells
foreach($compound_types[$process_name] as $compound_type){
$display .= "<tr>\\r";
$display .= "<td>$compound_type</td>\\r";
foreach($dates as $date){
if (array_key_exists($date,$rejects) && array_key_exists($process_name,$rejects[$date])){
foreach($rejects[$date][$process_name] as $reject_type => $arry){
if (array_key_exists($date,$rejects) && array_key_exists($process_name,$rejects[$date]) && !array_key_exists($reject_type,$rejects[$date][$process_name])){
$display .= "<td> </td>\\r";
}elseif (array_key_exists($date,$rejects) && array_key_exists($process_name,$rejects[$date]) && array_key_exists($reject_type,$rejects[$date][$process_name]) && !array_key_exists($compound_type,$rejects[$date][$process_name][$reject_type])){
$display .= "<td> </td>\\r";
}elseif (array_key_exists($date,$rejects) && array_key_exists($process_name,$rejects[$date]) && array_key_exists($reject_type,$rejects[$date][$process_name]) && array_key_exists($compound_type,$rejects[$date][$process_name][$reject_type])){
$display .= "<td>{$rejects[$date][$process_name][$reject_type][$compound_type]}</td>\\r";
}else{
$display .= "<td> </td>\\r";
}
}
}else{
$display .= "<td colspan=\\"{$datecnts[$date]}\\"> </td>\\r";
}
}
}
//---total rejects per process and per reject type---//
$display .= "<tr>";
$display .= "<td>Total</td>";
foreach($dates as $date1){
if (array_key_exists($date1,$rejects_total) && array_key_exists($process_name,$rejects_total[$date1])){
foreach($rejects_total[$date1][$process_name] as $reject_type => $arry){
if (array_key_exists($date1,$rejects_total) && array_key_exists($process_name,$rejects_total[$date1]) && !array_key_exists($reject_type,$rejects_total[$date1][$process_name])){
$display .= "<td> </td>\\r";
}elseif (array_key_exists($date1,$rejects_total) && array_key_exists($process_name,$rejects_total[$date1]) && !array_key_exists($reject_type,$rejects_total[$date1][$process_name])){
$display .= "<td> </td>\\r";
}elseif (array_key_exists($date1,$rejects_total) && array_key_exists($process_name,$rejects_total[$date1]) && array_key_exists($reject_type,$rejects_total[$date1][$process_name])){
$display .= "<td>{$rejects_total[$date1][$process_name][$reject_type][$compound_type]}</td>\\r";
}else{
$display .= "<td> </td>\\r";
}
}
}else{
$display .= "<td colspan=\\"{$datecnts[$date1]}\\"> </td>\\r";
}
}
$display .= "</tr>";
}
$display .= "</table>";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Operator's Output and Reject</title>
</head>
<body>
<?php
if(isset($display)){
echo "$display";
}
?>
</body>
</html>
I attached my database and also the screenshots of the output display of this code.
As you can see the display total is wrong…it should be sum all reject per reject_type.
The reject type display list is based on what is the reject_date.
I hope somebody can help me. Thank you so much.