SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 42
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem in displaying data inside table format

    Hi,

    I created report form for reject type.

    Now I need help in displaying the rejects amount per reject type.

    here is my code:

    PHP Code:
    <?php
    ob_start
    ();
    include 
    "connection.php";

    $id "30";
    if(
    $id == '30')
    {    

       
    //----code for date only----//
       
    $sql "SELECT DISTINCT r.reject_date, r.process_id
        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_date ORDER BY r.reject_date  ASC"

        
        
        
    $res mysql_query($sql);
     
        echo 
    "<table>";
        echo 
    "<tr>";
        echo 
    "<thead>";
        echo 
    "<th>Compound</th>";

        while(
    $row mysql_fetch_assoc($res))
        {
            
    $report_date[]  = $row['reject_date'];
            
    $process_[]   = $row['process_id'];
        }
        
            
    $i 1;
            foreach(
    $report_date AS $report_date)
            {
                echo 
    "<th id=col".$i." colspan=\"3\">$report_date</th>";
                
    $i++;
            }
            echo 
    "</thead>";
        echo 
    "</tr>";  
        echo 
    '
    <script type="text/javascript">
        document.getElementById("col2").colSpan='
    .$i.';
    </script>'

            
    $sql_comp "SELECT DISTINCT p.process_name , r.process_id, r.reject_date
            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 process_name ORDER BY p.process_id ASC";     
            
    $res_comp mysql_query($sql_comp);
            
            
                 echo 
    "<tr>";
                 while(
    $row_comp mysql_fetch_assoc($res_comp))
                 {
                                    
                    
    $process      $row_comp['process_name'];
                    
    $process_id   $row_comp['process_id'];
                    
    $reject_date  $row_comp['reject_date'];
                  
                    echo 
    "<td>$process</td>";
        
                
    $sql_date "SELECT DISTINCT r.reject_date
                                                        FROM op_reject AS r 
                                                        JOIN process_list AS p ON (p.process_id = r.process_id)
                                                        WHERE WEEK(reject_date) = '
    $id'
                                                        GROUP BY r.reject_date ORDER BY r.reject_date  ASC"

                                                        
    $res_date mysql_query($sql);
                                    
                                                        while(
    $row_date mysql_fetch_assoc($res_date))
                                                        {
                                                            
    $report_date_  $row_date['reject_date'];                                         

                                                            
                                                            
    $sql_rej "SELECT DISTINCT reject_type FROM op_reject WHERE reject_date = '$report_date_' and                                                                  process_id = '$process_id' ORDER BY reject_type";
                                                            
    $res_rej mysql_query($sql_rej);
                                                            
    $cnt_rej mysql_num_rows($res_rej);
                                                            
                                                            
                                                            if(
    $cnt_rej 0)
                                                            {
                                                            while(
    $row_rej=mysql_fetch_assoc($res_rej))
                                                            {
                                                                
    $reject_type $row_rej['reject_type'];
                                                                
                                                                echo 
    "<td>$reject_type</td>";
                                                            }
                                                            }   
                                                        }
        
                                    
    $comp "
                                    (SElECT DISTINCt r.compound_type FROM op_reject AS r WHERE r.process_id = '
    $process_id'  AND WEEK(r.reject_date) + 1 = '$id')
                                    ORDER BY compound_type ASC"
    ;

                                        
    $c mysql_query($comp);
                                        
                                        
    $cnt mysql_num_rows($c);
                                                
                                                echo 
    "<tr>";
                                                while(
    $co mysql_fetch_assoc($c))
                                                {
                                                        
    $compound_type   $co['compound_type'];
                                                        
    $process_i       $co['process_id'];
                                                        
    $shift_date      $co['shift_date'];  
                                                     
                                                        echo 
    "<td>$compound_type</td>"
                                                     
                                                        
    $sql_date "SELECT DISTINCT r.reject_date
                                                        FROM op_reject AS r 
                                                        JOIN process_list AS p ON (p.process_id = r.process_id)
                                                        WHERE WEEK(reject_date) = '
    $id'
                                                        GROUP BY r.reject_date ORDER BY r.reject_date  ASC"

                                                        
    $res_date mysql_query($sql);
                                    
                                                        while(
    $row_date mysql_fetch_assoc($res_date))
                                                        {
                                                            
    $report_date_  $row_date['reject_date'];                                         
                                    
                                                        
    //-----
                                                 
                                                 
                                                        //-----
                                                        
    $sql_reject "SELECT compound_type, SUM(reject) AS reject, reject_type FROM op_reject 
    WHERE compound_type = '
    $compound_type' and process_id = '$process_id' AND reject_date = '$report_date_' GROUP BY  compound_type, process_id, reject_TYPE";
                                                        
    $res_reject mysql_query($sql_reject);
                                                        
                                                        
    $cnt_rej mysql_num_rows($res_reject);
                                                       
                                                       if(
    $cnt_rej 0
                                                       {
                                                        
                                                        while(
    $row_reject mysql_fetch_assoc($res_reject))
                                                        {
                                                            
    $reject $row_reject['reject'];   
                                                          
                                                            echo 
    "<td>$reject</td>";         
                                                            
                                                       }
                                                       }
                                                       else
                                                       {
                                                           echo 
    "<td></td>";
                                                       }  
                                                        }

                                                       echo 
    "</tr>";
                                              

                } 

         }   
      echo 
    "</table>"
     }
    ?>
    for better understanding I attached my updated database, the screenshot display for this code and also the correct output i need to display.


    thank you.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Your code is so messy it's extremely hard to read.

    Some advice:

    You shouldn't be echoing html out of php. It just makes your code harder to read and harder to modify. This is especially true if you have a bunch of if/else logic in the middle of pumping out html in a table.



    PHP Code:
    <?PHP
    //Don't do this:
      
    echo "<table>";
        echo 
    "<tr>";
        echo 
    "<thead>";
        echo 
    "<th>Compound</th>";

    ?>

    <?PHP

    //do this instead:
    ?>

    <table>
      <tr>
        <thead>
          <th>Compound</th>
          <th>Something Else</th>
        </thead>
      </tr>

      <tbody>

    <?PHP foreach($phpData as $row){?>
        <tr>
          <td><?PHP echo $row['someData'];?></td>
          <td><?PHP echo $row['someOtherData'];?></td>
        </tr>

    <?PHP ?>

    //more html goes here
    You shouldn't be using mysql_ functions. As has been explained approximately 400 billion times on these forums already - they are DEPRECATED, INSECURE AND YOU SHOULD STOP USING THEM. Use PDO instead.

    What does this mean?

    PHP Code:
    $id "30";
    if(
    $id == '30'
    I have no idea what the hell you're doing there?

    You have hardcoded old school style javascript hard coded into your html that is messily echoed out of your php. Just no. Don't do this. You should have NO javascript in your html code whatsoever. None. Not one single line at all. You should be including a separate javascript file that is completely detached from your html that does what you need to do. This will make your code easier to deal with.

    You should not be writing pure sql queries in the middle of outputting html, as you'll end up with messy code like this that you can't even understand yourself. You're only trying to output a html table here, so it really shouldn't be hard to do, but this code makes it virtually impossible to understand, never mind build upon.

    Have a look at MVC (google it), and learn about objects.

    You also have variables with confusing names: stuff like: $cnt_rej

    What's $cnt_rej? It's not useful. You might know what that means right now, but come back to this script in 6 months and I'll bet even you can't remember what that is.

    It gets even more confusing when you look at how this is used:

    PHP Code:
                                                       //-----
                                                        
    $sql_reject "SELECT compound_type, SUM(reject) AS reject, reject_type FROM op_reject 
    WHERE compound_type = '
    $compound_type' and process_id = '$process_id' AND reject_date = '$report_date_' GROUP BY  compound_type, process_id, reject_TYPE";
                                                        
    $res_reject mysql_query($sql_reject);
                                                        
                                                        
    $cnt_rej mysql_num_rows($res_reject);
                                                       
                                                       if(
    $cnt_rej 0
                                                       {
                                                        
                                                        while(
    $row_reject mysql_fetch_assoc($res_reject))
                                                        {
                                                            
    $reject $row_reject['reject'];   
                                                          
                                                            echo 
    "<td>$reject</td>";         
                                                            
                                                       } 
    So what is the different between sql_reject, $res_reject and $cnt_rej??

    Why are we doing something if the $cnt_rej variable is greater than 0? How would I know what this was for if I inherited this script from you?

    If you have a variable called $numberOfRejectedPatients, you know that is a count for the number of rejected patients. Be explicit. Who cares if the variable name is longer - which variable would be easier to use and understand?

    I would recommend you re-write this script. Start by doing all your sql queries separately and storing the results in some arrays. You can then build the html table by simply looping through your array results. Also make sure you don't spit out html in the php.

    Just try doing that and see how much easier your script becomes to understand almost instantly...

  3. #3
    Non-Member
    Join Date
    Jul 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that may opinion on you shouldn't be echoing html out of php. It just makes your code harder to read and harder to modify. This is especially true if you have a bunch of if/else logic in the middle of pumping out html in a table.

  4. #4
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Hey newphpcoder,

    I would think after the few examples I made in other treads you might have picked up a few things. I made a copy of this page with a single query and built a single array of data of all information. However when it came time to build the display, working with only this array became difficult so I added some sub-arrays. Even with these, with all the checking for array keys, the code is rather messy attempting to fill empty table cells etc. Personally I would redo and take a different approach more like I did in the other examples, matching key values across the grid. In any case, I'll post what I made.

    PHP 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 "29";
    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();
        
    $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) = '
    $id'
        ORDER BY r.reject_date  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'];
             
        }
        
    //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=\"3\">$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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</td>\r";
                                        }
                                    }
                                }else{
                                
    $display .= "<td colspan=\"{$datecnts[$date]}\">&nbsp;</td>\r";
                                }                                                        
                            }                                                
                        }                    
                    }
        
    $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>

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you.

  6. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Hey newphpcoder,

    I would think after the few examples I made in other treads you might have picked up a few things. I made a copy of this page with a single query and built a single array of data of all information. However when it came time to build the display, working with only this array became difficult so I added some sub-arrays. Even with these, with all the checking for array keys, the code is rather messy attempting to fill empty table cells etc. Personally I would redo and take a different approach more like I did in the other examples, matching key values across the grid. In any case, I'll post what I made.

    PHP 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 "29";
    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();
        
    $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) = '
    $id'
        ORDER BY r.reject_date  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'];
             
        }
        
    //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=\"3\">$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";
                                }                                                        
                            }                                                
                        }                    
                    }
        
    $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>
    Hello Drummin ,

    I tried this code and it works. Now I tried to add total after the list of compound type.

    but honestly I felt difficulty in array_key_exists I'm not familiar with that syntax.


    here is my new code:

    PHP 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 r.reject_date, 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,  
        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';
       GROUP BY reject_type 
        ORDER BY r.reject_date, reject_type  ASC"
    ;
        
    $res_total mysql_query($sql_total);
        
         while(
    $row_total mysql_fetch_assoc($res_total)){ 
            
    //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_total']; 
             
            
    //build some unique sub arrays for displaying data  
         
              
    $rejects_total[$row['reject_date']][$row['process_name']][$row['reject_type']][$row['compound_type']] = $row['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=\"7\">$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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</td>\r"
                                        } 
                                    } 
                                }else{ 
                                
    $display .= "<td colspan=\"{$datecnts[$date]}\">&nbsp;</td>\r"
                                }                                                         
                            }
                            
                                                                                                                      
                        }  
               
    //---total rejects per process and per reject type---//
                        
                            
    $display .= "<tr>"
                            
    $display .= "<td>Total</td>";
                            
    $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 already create the query and also table row for the total, my problem is how can I display the reject_total per process/reject_type.
    I also tried to solve my problem on displaying reject_total.

    Also do you have an idea on how can add color on the lines of td per date.

    Please see attached files for your reference.

    Thank you so much.. I really appreciate your help.
    Attached Images Attached Images

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    this is my updated code:

    PHP 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 r.reject_date, 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,  
        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'
       GROUP BY r.reject_type 
        ORDER BY r.reject_date, reject_type  ASC"
    ;
        
        
    $res_total mysql_query($sql_total);
        
         while(
    $row_total mysql_fetch_assoc($res_total)){ 
            
    //Would have been nice to work off this single array, but found it difficult so I built sub-arrays below.                     
            
    $Reports[$row_total['reject_date']][$row_total['process_name']][$row_total['reject_type']][$row_total['compound_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['compound_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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</td>\r"
                                        } 
                                    } 
                                }else{ 
                                
    $display .= "<td colspan=\"{$datecnts[$date]}\">&nbsp;</td>\r"
                                }                                                         
                            }
                            
                                                                                                                      
                        }  
               
    //---total rejects per process and per reject type---//
                        
                            
    $display .= "<tr>"
                            
    $display .= "<td>Total</td>";
                            
                            foreach(
    $dates as $date){ 
                                if (
    array_key_exists($date,$rejects_total) && array_key_exists($process_name,$rejects_total[$date])){ 
                                    foreach(
    $rejects_total[$date][$process_name] as $reject_type => $arry){ 
                                        if (
    array_key_exists($date,$rejects_total) && array_key_exists($process_name,$rejects_total[$date]) && !array_key_exists($reject_type,$rejects_total[$date][$process_name])){ 
                                            
    $display .= "<td>&nbsp;</td>\r"
                                        }elseif (
    array_key_exists($date,$rejects_total) && array_key_exists($process_name,$rejects_total[$date]) && array_key_exists($reject_type,$rejects_total[$date][$process_name]) && !array_key_exists($compound_type,$rejects_total[$date][$process_name][$reject_type])){ 
                                            
    $display .= "<td>&nbsp;</td>\r"
                                        }elseif (
    array_key_exists($date,$rejects_total) && array_key_exists($process_name,$rejects_total[$date]) && array_key_exists($reject_type,$rejects_total[$date][$process_name]) && array_key_exists($compound_type,$rejects_total[$date][$process_name][$reject_type])){ 
                                            
    $display .= "<td>{$rejects_total[$date][$process_name][$reject_type][$compound_type]}</td>\r"
                                        }else{ 
                                            
    $display .= "<td>&nbsp;</td>\r"
                                        } 
                                    } 
                                }else{ 
                                
    $display .= "<td colspan=\"{$datecnts[$date]}\">&nbsp;</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>
    and i attached the output of this code. As you can see only the last has a total. but the correct is all has total.

    Thank you so much
    Attached Images Attached Images

  8. #8
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Well let's redo this with an array that's easier to use and some functions to give us solid information to work with.
    PHP 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 "29";

    //$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) = '
    $id
        ORDER BY r.reject_date, compound_type  ASC"
    ;  
        
    $res mysql_query($sql);
        
    $k=0;             
        while(
    $row mysql_fetch_assoc($res)){                 
            
    $Reports[$k]['reject_date'] = $row['reject_date'];
            
    $Reports[$k]['process_name'] = $row['process_name'];
            
    $Reports[$k]['reject_type'] = $row['reject_type'];
            
    $Reports[$k]['compound_type'] = $row['compound_type'];
            
    $Reports[$k]['reject'] = $row['reject']; 
           
    $k++; 
        }
        
        
    //| *************************************************** |\\
        //| Build functions to return specific values or arrays |\\
        //| *************************************************** |\\
        
        // Returns dates array \\
        
    function dates($array){
            
    $dates = array();    
            foreach(
    $array as $k => $v){
                
    $dates[] = $v['reject_date'];
            }
            
    $dates array_unique($dates);
            
    sort($dates);
            return 
    $dates
        }
        
        
    // Returns process_names array \\
        
    function process_names($array){    
            
    $process_names = array();
            foreach(
    $array as $k => $v){
                
    $process_names[] = $v['process_name'];
            }
            
    $process_names array_unique($process_names);
            
    sort($process_names);
            return 
    $process_names
        }
        
        
    //*****************************************//    
           // Returns number of columns by date \\
        //*****************************************//    
        
    function columns($array,$date){
            
    //process names    
            
    $process_names = array();
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $date){
                    
    $process_names[] = $v['process_name'];
                }
            }
            
    $process_names array_unique($process_names);
            
    sort($process_names);                             
            
    // get reject_types by process name
            
    $reject_types = array();
            foreach(
    $process_names as $process_name){
                foreach(
    $array as $k => $v){
                    if (
    $v['reject_date'] == $date &&  
                        
    $v['process_name'] == $process_name){
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type'];
                    }    
                }
            }    
            
    //Convert reject_type to count        
            
    $counts = array();
            foreach(
    $reject_types as $reject_type){
                
    $counts[] = count($reject_type);
            }
            
    //return largest value
            
    $max_count max($counts);
            return 
    $max_count;  
        }
        
    //*****************************************//    
         // END Returns number of columns by date \\
        //*****************************************//    
        
        // Returns compound_type array for process_name \\
        
    function compound_types($array,$process_name){    
            
    $compound_types = array();
            foreach(
    $array as $k => $v){
                if (
    $v['process_name'] == $process_name){
                    
    $compound_types[] = $v['compound_type'];
                }    
            }
            
    $compound_types array_unique($compound_types);
            
    sort($compound_types);
            return 
    $compound_types
        }
        
        
    // Returns reject_type array for date and process_name \\
        
    function reject_types($array,$reject_date,$process_name){    
            
    $reject_types = array();
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $reject_date &&  
                    
    $v['process_name'] == $process_name){
                    
    $reject_types[] = $v['reject_type'];
                }    
            }
            
    $reject_types array_unique($reject_types);
            
    sort($reject_types);
            return 
    $reject_types
        }
         
        
    // Returns reject amount for cell \\
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){
            
    $result "";    
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == "{$reject_date}&&  
                    
    $v['process_name'] == "{$process_name}&&
                    
    $v['reject_type'] == $reject_type &&
                    
    $v['compound_type'] == $compound_type){
                    
    $result $v['reject'];
                }
            }
            return 
    $result
        }
        
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){
            
    $total=0;
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $reject_date &&  
                    
    $v['process_name'] == $process_name &&
                    
    $v['reject_type'] == $reject_type){
                    
    $total += $v['reject'];
                }    
            }
            
    $total = ($total!=number_format($total2'.''') : '');
            return 
    $total
        }
        
    //echo "<pre>"; 
    //print_r($Reports); 
    //echo "</pre>"; 

    ////Build display data BEFORE output to browser//// 
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\"> 
            <thead> 
                <tr> 
                    <th>Compound</th>\r"

                    
    //I'll keep your id identifier in place                 
                    
    $i=1;
                    
    $dates dates($Reports);                 
                    foreach(
    $dates as $date){ 
                        
    //We'll run our columns function in our date loop to get column count
                        
    $cols columns($Reports,$date); 
                        
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r"
                        
                        
    $i++;
                    } 
                
    $display .= "</tr> 
                </thead>"
    ;      
                
                    
    $process_names process_names($Reports); 
                    foreach(
    $process_names as $process_name){                 
                        
    $display .= "<tr>\r";     
                            
    $display .= "<td>$process_name</td>\r";    
                            
                        foreach(
    $dates as $date){      
                               
    $bor=0;                               
                             
    $reject_types reject_types($Reports,$date,$process_name);
                            
    $reject_type_count count($reject_types);
                            
    $type_for_date = (!empty($reject_types) ? "true" "false");
                            
    $cols columns($Reports,$date);
                            
                            if (
    $type_for_date == "false"){
                                for(
    $c=0;$c<$cols;$c++){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>&nbsp;</td>\r";    
                                    
                                    
    $bor++;
                                }
                            }else{
                                foreach(
    $reject_types as $reject_type){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>$reject_type$bor</td>\r"
                                    
                                    
    $bor++; 
                                }                            
                                for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>&nbsp;</td>\r";    
                                    
                                    
    $bor++;
                                }
                            }                         
                        }                      
                
    $display .= "</tr>\r";
                
                    
    $compound_types compound_types($Reports,$process_name);            
                            foreach(
    $compound_types as $compound_type){                 
                                
    $display .= "<tr>\r";     
                                    
    $display .= "<td>$compound_type</td>\r";
                                                        
                                foreach(
    $dates as $date){       
                                    
    $bor=0;              
                                    
    $reject_types reject_types($Reports,$date,$process_name);
                                    
    $reject_type_count count($reject_types);
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false");
                                    
    $cols columns($Reports,$date);
                                     if (
    $type_for_date == "false"){
                                        for(
    $c=0;$c<$cols;$c++){
                                            
    $border = ($bor==" class=\"border\"" ''); 
                                                         
                                            
    $display .= "<td$border>&nbsp;</td>\r";
                                            
                                            
    $bor++;
                                        }
                                    }else{
                                        foreach(
    $reject_types as $reject_type){    
                                            
    $border = ($bor==" class=\"border\"" ''); 
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type);
                                            
    $reject = (!empty($reject) ? $reject "&nbsp;");
                                                                                
                                            
    $display .= "<td$border>$reject</td>\r";
                                                
                                            
    $bor++;                                    
                                                                        
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                                
    $border = ($bor==" class=\"border\"" '');
                                                      
                                                
    $display .= "<td$border>&nbsp;</td>\r";
                                                
                                                
    $bor++;
                                            }
                                        }
                                    }                                                
                                }                         
                
    $display .= "</tr>\r";                                                                                                                  
                            } 
                         
               
    //---total rejects per process and per reject type---//
                            
    $display .= "<tr>\r"
                            
    $display .= "<td class=\"total\">Total</td>\r";                       
                                                        
                                foreach(
    $dates as $date){       
                                    
    $bor=0;              
                                    
    $reject_types reject_types($Reports,$date,$process_name);
                                    
    $reject_type_count count($reject_types);
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false");
                                    
    $cols columns($Reports,$date);
                                     if (
    $type_for_date == "false"){
                                        for(
    $c=0;$c<$cols;$c++){
                                            
    $border = ($bor==" border" ''); 
                                                                 
                                            
    $display .= "<td class=\"total$border\">&nbsp;</td>\r";
                                            
                                            
    $bor++;
                                        }
                                    }else{
                                        foreach(
    $reject_types as $reject_type){    
                                            
    $border = ($bor==" border" '');  
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type);
                                            
    $rejectTotal = (!empty($reject) ? $reject "&nbsp;");
                                                                                
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r";
                                                
                                            
    $bor++;                                    
                                                                        
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                                
    $border = ($bor==" border" '');
                                                              
                                                
    $display .= "<td class=\"total$border\">&nbsp;</td>\r";
                                                
                                                
    $bor++;
                                            }
                                        }
                                    }                                                
                                }
                            }
                            
    $display .= "</tr>\r";                
        
    $display .= "</table>\r";

    ?> 
    <!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>
        <style type="text/css">
        .display {
        background-color:#9D9D9D;
        color:#00000;  
        font-family: Arial;
        font-size: 13px;
        }
        .display th{
        background-color:#4F8E38;
        color:#FFF;
        font-size: 14px;
        font-weight:bold;
        text-align:center;
        padding: 1px 3px;
        }
        .display td{
        background-color:#FFF;
        color:#00000;
        font-weight:bold;
        padding: 2px 4px;
        border:1px solid #E1E1E1;
        }
        .display .total{
        background-color:#F7FFF1;
        color:#000000;
        font-size: 13px; 
        font-weight:bold;
        padding: 2px 4px;
        border-top:1px solid #609A2C;
        border-bottom:1px solid #609A2C;
        }
        .display .border {
        border-left:2px solid #385320;
        }
        </style> 
        </head> 
        <body> 
            <?php 
            
    if(isset($display)){ 
                echo 
    "$display"
            } 
            
    ?> 
        </body> 
    </html>

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Well let's redo this with an array that's easier to use and some functions to give us solid information to work with.
    PHP 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 "29";

    //$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) = '
    $id
        ORDER BY r.reject_date, compound_type  ASC"
    ;  
        
    $res mysql_query($sql);
        
    $k=0;             
        while(
    $row mysql_fetch_assoc($res)){                 
            
    $Reports[$k]['reject_date'] = $row['reject_date'];
            
    $Reports[$k]['process_name'] = $row['process_name'];
            
    $Reports[$k]['reject_type'] = $row['reject_type'];
            
    $Reports[$k]['compound_type'] = $row['compound_type'];
            
    $Reports[$k]['reject'] = $row['reject']; 
           
    $k++; 
        }
        
        
    //| *************************************************** |\\
        //| Build functions to return specific values or arrays |\\
        //| *************************************************** |\\
        
        // Returns dates array \\
        
    function dates($array){
            
    $dates = array();    
            foreach(
    $array as $k => $v){
                
    $dates[] = $v['reject_date'];
            }
            
    $dates array_unique($dates);
            
    sort($dates);
            return 
    $dates
        }
        
        
    // Returns process_names array \\
        
    function process_names($array){    
            
    $process_names = array();
            foreach(
    $array as $k => $v){
                
    $process_names[] = $v['process_name'];
            }
            
    $process_names array_unique($process_names);
            
    sort($process_names);
            return 
    $process_names
        }
        
        
    //*****************************************//    
           // Returns number of columns by date \\
        //*****************************************//    
        
    function columns($array,$date){
            
    //process names    
            
    $process_names = array();
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $date){
                    
    $process_names[] = $v['process_name'];
                }
            }
            
    $process_names array_unique($process_names);
            
    sort($process_names);                             
            
    // get reject_types by process name
            
    $reject_types = array();
            foreach(
    $process_names as $process_name){
                foreach(
    $array as $k => $v){
                    if (
    $v['reject_date'] == $date &&  
                        
    $v['process_name'] == $process_name){
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type'];
                    }    
                }
            }    
            
    //Convert reject_type to count        
            
    $counts = array();
            foreach(
    $reject_types as $reject_type){
                
    $counts[] = count($reject_type);
            }
            
    //return largest value
            
    $max_count max($counts);
            return 
    $max_count;  
        }
        
    //*****************************************//    
         // END Returns number of columns by date \\
        //*****************************************//    
        
        // Returns compound_type array for process_name \\
        
    function compound_types($array,$process_name){    
            
    $compound_types = array();
            foreach(
    $array as $k => $v){
                if (
    $v['process_name'] == $process_name){
                    
    $compound_types[] = $v['compound_type'];
                }    
            }
            
    $compound_types array_unique($compound_types);
            
    sort($compound_types);
            return 
    $compound_types
        }
        
        
    // Returns reject_type array for date and process_name \\
        
    function reject_types($array,$reject_date,$process_name){    
            
    $reject_types = array();
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $reject_date &&  
                    
    $v['process_name'] == $process_name){
                    
    $reject_types[] = $v['reject_type'];
                }    
            }
            
    $reject_types array_unique($reject_types);
            
    sort($reject_types);
            return 
    $reject_types
        }
         
        
    // Returns reject amount for cell \\
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){
            
    $result "";    
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == "{$reject_date}&&  
                    
    $v['process_name'] == "{$process_name}&&
                    
    $v['reject_type'] == $reject_type &&
                    
    $v['compound_type'] == $compound_type){
                    
    $result $v['reject'];
                }
            }
            return 
    $result
        }
        
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){
            
    $total=0;
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $reject_date &&  
                    
    $v['process_name'] == $process_name &&
                    
    $v['reject_type'] == $reject_type){
                    
    $total += $v['reject'];
                }    
            }
            
    $total = ($total!=number_format($total2'.''') : '');
            return 
    $total
        }
        
    //echo "<pre>"; 
    //print_r($Reports); 
    //echo "</pre>"; 

    ////Build display data BEFORE output to browser//// 
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\"> 
            <thead> 
                <tr> 
                    <th>Compound</th>\r"

                    
    //I'll keep your id identifier in place                 
                    
    $i=1;
                    
    $dates dates($Reports);                 
                    foreach(
    $dates as $date){ 
                        
    //We'll run our columns function in our date loop to get column count
                        
    $cols columns($Reports,$date); 
                        
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r"
                        
                        
    $i++;
                    } 
                
    $display .= "</tr> 
                </thead>"
    ;      
                
                    
    $process_names process_names($Reports); 
                    foreach(
    $process_names as $process_name){                 
                        
    $display .= "<tr>\r";     
                            
    $display .= "<td>$process_name</td>\r";    
                            
                        foreach(
    $dates as $date){      
                               
    $bor=0;                               
                             
    $reject_types reject_types($Reports,$date,$process_name);
                            
    $reject_type_count count($reject_types);
                            
    $type_for_date = (!empty($reject_types) ? "true" "false");
                            
    $cols columns($Reports,$date);
                            
                            if (
    $type_for_date == "false"){
                                for(
    $c=0;$c<$cols;$c++){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>*</td>\r";    
                                    
                                    
    $bor++;
                                }
                            }else{
                                foreach(
    $reject_types as $reject_type){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>$reject_type$bor</td>\r"
                                    
                                    
    $bor++; 
                                }                            
                                for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>*</td>\r";    
                                    
                                    
    $bor++;
                                }
                            }                         
                        }                      
                
    $display .= "</tr>\r";
                
                    
    $compound_types compound_types($Reports,$process_name);            
                            foreach(
    $compound_types as $compound_type){                 
                                
    $display .= "<tr>\r";     
                                    
    $display .= "<td>$compound_type</td>\r";
                                                        
                                foreach(
    $dates as $date){       
                                    
    $bor=0;              
                                    
    $reject_types reject_types($Reports,$date,$process_name);
                                    
    $reject_type_count count($reject_types);
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false");
                                    
    $cols columns($Reports,$date);
                                     if (
    $type_for_date == "false"){
                                        for(
    $c=0;$c<$cols;$c++){
                                            
    $border = ($bor==" class=\"border\"" ''); 
                                                         
                                            
    $display .= "<td$border>*</td>\r";
                                            
                                            
    $bor++;
                                        }
                                    }else{
                                        foreach(
    $reject_types as $reject_type){    
                                            
    $border = ($bor==" class=\"border\"" ''); 
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type);
                                            
    $reject = (!empty($reject) ? $reject "*");
                                                                                
                                            
    $display .= "<td$border>$reject</td>\r";
                                                
                                            
    $bor++;                                    
                                                                        
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                                
    $border = ($bor==" class=\"border\"" '');
                                                      
                                                
    $display .= "<td$border>*</td>\r";
                                                
                                                
    $bor++;
                                            }
                                        }
                                    }                                                
                                }                         
                
    $display .= "</tr>\r";                                                                                                                  
                            } 
                         
               
    //---total rejects per process and per reject type---//
                            
    $display .= "<tr>\r"
                            
    $display .= "<td class=\"total\">Total</td>\r";                       
                                                        
                                foreach(
    $dates as $date){       
                                    
    $bor=0;              
                                    
    $reject_types reject_types($Reports,$date,$process_name);
                                    
    $reject_type_count count($reject_types);
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false");
                                    
    $cols columns($Reports,$date);
                                     if (
    $type_for_date == "false"){
                                        for(
    $c=0;$c<$cols;$c++){
                                            
    $border = ($bor==" border" ''); 
                                                                 
                                            
    $display .= "<td class=\"total$border\">*</td>\r";
                                            
                                            
    $bor++;
                                        }
                                    }else{
                                        foreach(
    $reject_types as $reject_type){    
                                            
    $border = ($bor==" border" '');  
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type);
                                            
    $rejectTotal = (!empty($reject) ? $reject "*");
                                                                                
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r";
                                                
                                            
    $bor++;                                    
                                                                        
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                                
    $border = ($bor==" border" '');
                                                              
                                                
    $display .= "<td class=\"total$border\">*</td>\r";
                                                
                                                
    $bor++;
                                            }
                                        }
                                    }                                                
                                }
                            }
                            
    $display .= "</tr>\r";                
        
    $display .= "</table>\r";

    ?> 
    <!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>
        <style type="text/css">
        .display {
        background-color:#9D9D9D;
        color:#00000;  
        font-family: Arial;
        font-size: 13px;
        }
        .display th{
        background-color:#4F8E38;
        color:#FFF;
        font-size: 14px;
        font-weight:bold;
        text-align:center;
        padding: 1px 3px;
        }
        .display td{
        background-color:#FFF;
        color:#00000;
        font-weight:bold;
        padding: 2px 4px;
        border:1px solid #E1E1E1;
        }
        .display .total{
        background-color:#F7FFF1;
        color:#000000;
        font-size: 13px; 
        font-weight:bold;
        padding: 2px 4px;
        border-top:1px solid #609A2C;
        border-bottom:1px solid #609A2C;
        }
        .display .border {
        border-left:2px solid #385320;
        }
        </style> 
        </head> 
        <body> 
            <?php 
            
    if(isset($display)){ 
                echo 
    "$display"
            } 
            
    ?> 
        </body> 
    </html>
    Thank you.. I will try it now..

  10. #10
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I tried it the table color is correct, but the total rejects has something wrong..


    total should be:
    SC = 3
    SH = 6
    SP = 4

    Please see attached file.

    Thank you so much
    Attached Images Attached Images

  11. #11
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much for your help...

    I really appreciated

  12. #12
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Hey sorry, my bad. Total section was copied from section above and I missed a variable edit. Line 290 should be $rejectTotal instead of $reject.
    PHP Code:
    $rejectTotal = (!empty($rejectTotal) ? $rejectTotal "&nbsp;"); 

  13. #13
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you..

    there is one closing </tr> tag missing?

  14. #14
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Here's an updated copy.
    PHP 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 "29";

    //$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) = '
    $id
        ORDER BY r.reject_date, compound_type  ASC"
    ;  
        
    $res mysql_query($sql);
        
    $k=0;             
        while(
    $row mysql_fetch_assoc($res)){                 
            
    $Reports[$k]['reject_date'] = $row['reject_date'];
            
    $Reports[$k]['process_name'] = $row['process_name'];
            
    $Reports[$k]['reject_type'] = $row['reject_type'];
            
    $Reports[$k]['compound_type'] = $row['compound_type'];
            
    $Reports[$k]['reject'] = $row['reject']; 
           
    $k++; 
        }
        
        
    //| *************************************************** |\\
        //| Build functions to return specific values or arrays |\\
        //| *************************************************** |\\
        
        // Returns dates array \\
        
    function dates($array){
            
    $dates = array();    
            foreach(
    $array as $k => $v){
                
    $dates[] = $v['reject_date'];
            }
            
    $dates array_unique($dates);
            
    sort($dates);
            return 
    $dates
        }
        
        
    // Returns process_names array \\
        
    function process_names($array){    
            
    $process_names = array();
            foreach(
    $array as $k => $v){
                
    $process_names[] = $v['process_name'];
            }
            
    $process_names array_unique($process_names);
            
    sort($process_names);
            return 
    $process_names
        }
        
        
    //*****************************************//    
           // Returns number of columns by date \\
        //*****************************************//    
        
    function columns($array,$date){
            
    //process names    
            
    $process_names = array();
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $date){
                    
    $process_names[] = $v['process_name'];
                }
            }
            
    $process_names array_unique($process_names);
            
    sort($process_names);                             
            
    // get reject_types by process name
            
    $reject_types = array();
            foreach(
    $process_names as $process_name){
                foreach(
    $array as $k => $v){
                    if (
    $v['reject_date'] == $date &&  
                        
    $v['process_name'] == $process_name){
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type'];
                    }    
                }
            }    
            
    //Convert reject_type to count        
            
    $counts = array();
            foreach(
    $reject_types as $reject_type){
                
    $counts[] = count($reject_type);
            }
            
    //return largest value
            
    $max_count max($counts);
            return 
    $max_count;  
        }
        
    //*****************************************//    
         // END Returns number of columns by date \\
        //*****************************************//    
        
        // Returns compound_type array for process_name \\
        
    function compound_types($array,$process_name){    
            
    $compound_types = array();
            foreach(
    $array as $k => $v){
                if (
    $v['process_name'] == $process_name){
                    
    $compound_types[] = $v['compound_type'];
                }    
            }
            
    $compound_types array_unique($compound_types);
            
    sort($compound_types);
            return 
    $compound_types
        }
        
        
    // Returns reject_type array for date and process_name \\
        
    function reject_types($array,$reject_date,$process_name){    
            
    $reject_types = array();
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $reject_date &&  
                    
    $v['process_name'] == $process_name){
                    
    $reject_types[] = $v['reject_type'];
                }    
            }
            
    $reject_types array_unique($reject_types);
            
    sort($reject_types);
            return 
    $reject_types
        }
         
        
    // Returns reject amount for cell \\
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){
            
    $result "";    
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == "{$reject_date}&&  
                    
    $v['process_name'] == "{$process_name}&&
                    
    $v['reject_type'] == $reject_type &&
                    
    $v['compound_type'] == $compound_type){
                    
    $result $v['reject'];
                }
            }
            return 
    $result
        }
        
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){
            
    $total=0;
            foreach(
    $array as $k => $v){
                if (
    $v['reject_date'] == $reject_date &&  
                    
    $v['process_name'] == $process_name &&
                    
    $v['reject_type'] == $reject_type){
                    
    $total += $v['reject'];
                }    
            }
            
    $total = ($total!=number_format($total2'.''') : '');
            return 
    $total
        }
        
    //echo "<pre>"; 
    //print_r($Reports); 
    //echo "</pre>"; 

    ////Build display data BEFORE output to browser//// 
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\"> 
            <thead> 
                <tr> 
                    <th>Compound</th>\r"

                    
    //I'll keep your id identifier in place                 
                    
    $i=1;
                    
    $dates dates($Reports);                 
                    foreach(
    $dates as $date){ 
                        
    //We'll run our columns function in our date loop to get column count
                        
    $cols columns($Reports,$date); 
                        
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r"
                        
                        
    $i++;
                    } 
                
    $display .= "</tr> 
                </thead>"
    ;      
                
                    
    $process_names process_names($Reports); 
                    foreach(
    $process_names as $process_name){                 
                        
    $display .= "<tr>\r";     
                            
    $display .= "<td>$process_name</td>\r";    
                            
                        foreach(
    $dates as $date){      
                               
    $bor=0;                               
                             
    $reject_types reject_types($Reports,$date,$process_name);
                            
    $reject_type_count count($reject_types);
                            
    $type_for_date = (!empty($reject_types) ? "true" "false");
                            
    $cols columns($Reports,$date);
                            
                            if (
    $type_for_date == "false"){
                                for(
    $c=0;$c<$cols;$c++){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>&nbsp;</td>\r";    
                                    
                                    
    $bor++;
                                }
                            }else{
                                foreach(
    $reject_types as $reject_type){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>$reject_type$bor</td>\r"
                                    
                                    
    $bor++; 
                                }                            
                                for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                     
                                    
    $display .= "<td$border>&nbsp;</td>\r";    
                                    
                                    
    $bor++;
                                }
                            }                         
                        }                      
                
    $display .= "</tr>\r";
                
                    
    $compound_types compound_types($Reports,$process_name);            
                            foreach(
    $compound_types as $compound_type){                 
                                
    $display .= "<tr>\r";     
                                    
    $display .= "<td>$compound_type</td>\r";
                                                        
                                foreach(
    $dates as $date){       
                                    
    $bor=0;              
                                    
    $reject_types reject_types($Reports,$date,$process_name);
                                    
    $reject_type_count count($reject_types);
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false");
                                    
    $cols columns($Reports,$date);
                                     if (
    $type_for_date == "false"){
                                        for(
    $c=0;$c<$cols;$c++){
                                            
    $border = ($bor==" class=\"border\"" ''); 
                                                         
                                            
    $display .= "<td$border>&nbsp;</td>\r";
                                            
                                            
    $bor++;
                                        }
                                    }else{
                                        foreach(
    $reject_types as $reject_type){    
                                            
    $border = ($bor==" class=\"border\"" ''); 
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type);
                                            
    $reject = (!empty($reject) ? $reject "&nbsp;");
                                                                                
                                            
    $display .= "<td$border>$reject</td>\r";
                                                
                                            
    $bor++;                                    
                                                                        
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                                
    $border = ($bor==" class=\"border\"" '');
                                                      
                                                
    $display .= "<td$border>&nbsp;</td>\r";
                                                
                                                
    $bor++;
                                            }
                                        }
                                    }                                                
                                }                         
                
    $display .= "</tr>\r";                                                                                                                  
                            } 
                         
               
    //---total rejects per process and per reject type---//
                            
    $display .= "<tr>\r"
                            
    $display .= "<td class=\"total\">Total</td>\r";                       
                                                        
                                foreach(
    $dates as $date){       
                                    
    $bor=0;              
                                    
    $reject_types reject_types($Reports,$date,$process_name);
                                    
    $reject_type_count count($reject_types);
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false");
                                    
    $cols columns($Reports,$date);
                                     if (
    $type_for_date == "false"){
                                        for(
    $c=0;$c<$cols;$c++){
                                            
    $border = ($bor==" border" ''); 
                                                                 
                                            
    $display .= "<td class=\"total$border\">&nbsp;</td>\r";
                                            
                                            
    $bor++;
                                        }
                                    }else{
                                        foreach(
    $reject_types as $reject_type){    
                                            
    $border = ($bor==" border" '');  
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type);
                                            
    $rejectTotal = (!empty($rejectTotal) ? $rejectTotal "&nbsp;");
                                                                                
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r";
                                                
                                            
    $bor++;                                    
                                                                        
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){    
                                                
    $border = ($bor==" border" '');
                                                              
                                                
    $display .= "<td class=\"total$border\">&nbsp;</td>\r";
                                                
                                                
    $bor++;
                                            }
                                        }
                                    }                                                
                                }  
                            
    $display .= "</tr>\r";
                            }                
        
    $display .= "</table>\r";

    ?> 
    <!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>
        <style type="text/css">
        .display {
        background-color:#9D9D9D;
        color:#00000;  
        font-family: Arial;
        font-size: 13px;
        }
        .display th{
        background-color:#4F8E38;
        color:#FFF;
        font-size: 14px;
        font-weight:bold;
        text-align:center;
        padding: 1px 3px;
        }
        .display td{
        background-color:#FFF;
        color:#00000;
        font-weight:bold;
        padding: 2px 4px;
        border:1px solid #E1E1E1;
        }
        .display .total{
        background-color:#F7FFF1;
        color:#000000;
        font-size: 13px; 
        font-weight:bold;
        padding: 2px 4px;
        border-top:1px solid #609A2C;
        border-bottom:1px solid #609A2C;
        }
        .display .border {
        border-left:2px solid #385320;
        }
        </style> 
        </head> 
        <body> 
            <?php 
            
    if(isset($display)){ 
                echo 
    "$display"
            } 
            
    ?> 
        </body> 
    </html>

  15. #15
    SitePoint Member
    Join Date
    Jul 2013
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the updated one seems to be working now, you fixed pretty much all the mistakes,

  16. #16
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    this is my whole code:

    weekly_reject.php

    Code:
    <?php
        error_reporting(0);
     session_start();
      ob_start();
      date_default_timezone_set("Asia/Singapore");
      
      include('connection.php');  
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <title>Weekly Report</title>
    <head>
    <link rel="stylesheet" type="text/css" href="op_reject.css" />
    <script type="text/javascript" src="jquery.js"></script>
    <script type='text/javascript' src='jquery.autocomplete.js'></script>
    <link rel="stylesheet" type="text/css" href="jquery.autocomplete.css" />
    
    <style type="text/css">
        .display {
        background-color:#9D9D9D;
        color:#00000;  
        font-family: Arial;
        font-size: 13px;
        }
        .display th{
        background-color:#4F8E38;
        color:#FFF;
        font-size: 14px;
        font-weight:bold;
        text-align:center;
        padding: 1px 3px;
        }
        .display td{
        background-color:#FFF;
        color:#00000;
        font-weight:bold;
        padding: 2px 4px;
        border:1px solid #E1E1E1;
        }
        .display .total{
        background-color:#F7FFF1;
        color:#000000;
        font-size: 13px; 
        font-weight:bold;
        padding: 2px 4px;
        border-top:1px solid #609A2C;
        border-bottom:1px solid #609A2C;
        }
        .display .border {
        border-left:2px solid #385320;
        }
        </style> 
    
    <script type="text/javascript">
    //----auto complete week--//
    
    $().ready(function() {
        $("#week_selected").autocomplete("get_week_list.php", {
           width: 115,
            matchContains: true,
            mustMatch: true,
            selectFirst: false
        });
        
        $("#week_selected").result(function(event, data, formatted) {
            $("#week_number").val(data[1]);
        });
        });
        
    /*AJAX*/
    function AJAX(){
            var xmlHttp;
            try{
                xmlHttp=new XMLHttpRequest(); // Firefox, Opera 8.0+, Safari
                return xmlHttp;
                }
            catch (e){
                try{
                    xmlHttp=new ActiveXObject("Msxml2.XMLHTTP"); // Internet Explorer
                    return xmlHttp;
                    }
                catch (e){
                    try{
                        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
                        return xmlHttp;
                        }
                    catch (e){
                        alert("Your browser does not support AJAX!");
                        return false;
                        }
                    }
                }
            }   
            
      //-----get weekdata from week---//
    function getweekdata()
            {
               //  if (window.event.keyCode==13 || window.event.keyCode==10) {
                divid = "week_data";
                var url = "get_weekly_reject.php";
                var str = "id=" + document.getElementById("week_number").value;
                
                var xmlHttp = AJAX();
                xmlHttp.onreadystatechange =  function(){
                if(xmlHttp.readyState > 0 && xmlHttp.readyState < 4){
                   // document.getElementById(divid).innerHTML=loadingmessage;
                    }
                if (xmlHttp.readyState == 4) {
                    if (xmlHttp.status == 200) {
                        var jsonart = xmlHttp.responseText;
                        document.getElementById(divid).innerHTML = jsonart;
                        }
                    }
                }
                xmlHttp.open("POST", url, true);
                xmlHttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
                xmlHttp.setRequestHeader("Content-length", str.length);
                xmlHttp.setRequestHeader("Connection", "close");
                xmlHttp.send(str);
              
           // }
            }           
    </script>
    </head>
    <body onload=document.getElementById("week_selected").focus();>
    <form name="weekly_reject" action="" method="post">
    <div id="ddcolortabs">
    <ul>
    <li> <a href="index.php" title="Operator's Shift Report"><span>Operator's Shift Report</span></a></li>
    <li> <a href="remarks.php" title="Remarks"><span>Remarks</span></a></li>
    <li id="current"> <a href="weekly_report.php" title="Reports"><span>Reports</span></a></li>
    </ul>
    </div>
    <br/>
    <div id="ddcolortabs1">
    <ul>
    <li><a href="weekly_report.php" title="Weekly Report"><span>Weekly Report</span></a></li>
    <li id="current"><a href="weekly_reject.php" title="Weekly Reject"><span>Weekly Reject</span></a></li>
    <li><a href="monthly_report.php" title="Monthly Report"><span>Monthly Report</span></a></li>
    <li><a href="remarks_report.php" title="Remarks Report"><span>Remarks Report</span></a></li>
    </ul>
    </div>
    <br/>
    <div>
    <table>
    <tr>
    <td style="border: none;">Type Week:</td>
    <td><input type="text" name="week_selected" id="week_selected" value="" size="15" onkeyup="getweekdata();"></td>
    </tr>
    </table>
    </div>
    
    <input type="hidden" name="week_number" id="week_number">
    
    <div id='week_data'>
    </div>
    </form>
    </body>
    </html>
    and the get_weekly_reject.php
    PHP 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 "29"

    //$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) = '
    $id'  
        ORDER BY r.reject_date, compound_type  ASC"
    ;   
        
    $res mysql_query($sql); 
        
    $k=0;              
        while(
    $row mysql_fetch_assoc($res)){                  
            
    $Reports[$k]['reject_date'] = $row['reject_date']; 
            
    $Reports[$k]['process_name'] = $row['process_name']; 
            
    $Reports[$k]['reject_type'] = $row['reject_type']; 
            
    $Reports[$k]['compound_type'] = $row['compound_type']; 
            
    $Reports[$k]['reject'] = $row['reject'];  
           
    $k++;  
        } 
         
        
    //| *************************************************** |\\ 
        //| Build functions to return specific values or arrays |\\ 
        //| *************************************************** |\\ 
         
        // Returns dates array \\ 
        
    function dates($array){ 
            
    $dates = array();     
            foreach(
    $array as $k => $v){ 
                
    $dates[] = $v['reject_date']; 
            } 
            
    $dates array_unique($dates); 
            
    sort($dates); 
            return 
    $dates;  
        } 
         
        
    // Returns process_names array \\ 
        
    function process_names($array){     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                
    $process_names[] = $v['process_name']; 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names); 
            return 
    $process_names;  
        } 
         
        
    //*****************************************//     
           // Returns number of columns by date \\ 
        //*****************************************//     
        
    function columns($array,$date){ 
            
    //process names     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $date){ 
                    
    $process_names[] = $v['process_name']; 
                } 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names);                              
            
    // get reject_types by process name 
            
    $reject_types = array(); 
            foreach(
    $process_names as $process_name){ 
                foreach(
    $array as $k => $v){ 
                    if (
    $v['reject_date'] == $date &&   
                        
    $v['process_name'] == $process_name){ 
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type']; 
                    }     
                } 
            }     
            
    //Convert reject_type to count         
            
    $counts = array(); 
            foreach(
    $reject_types as $reject_type){ 
                
    $counts[] = count($reject_type); 
            } 
            
    //return largest value 
            
    $max_count max($counts); 
            return 
    $max_count;   
        } 
        
    //*****************************************//     
         // END Returns number of columns by date \\ 
        //*****************************************//     
         
        // Returns compound_type array for process_name \\ 
        
    function compound_types($array,$process_name){     
            
    $compound_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['process_name'] == $process_name){ 
                    
    $compound_types[] = $v['compound_type']; 
                }     
            } 
            
    $compound_types array_unique($compound_types); 
            
    sort($compound_types); 
            return 
    $compound_types;  
        } 
         
        
    // Returns reject_type array for date and process_name \\ 
        
    function reject_types($array,$reject_date,$process_name){     
            
    $reject_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name){ 
                    
    $reject_types[] = $v['reject_type']; 
                }     
            } 
            
    $reject_types array_unique($reject_types); 
            
    sort($reject_types); 
            return 
    $reject_types;  
        } 
          
        
    // Returns reject amount for cell \\ 
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){ 
            
    $result "";     
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == "{$reject_date}&&   
                    
    $v['process_name'] == "{$process_name}&& 
                    
    $v['reject_type'] == $reject_type && 
                    
    $v['compound_type'] == $compound_type){ 
                    
    $result $v['reject']; 
                } 
            } 
            return 
    $result;  
        } 
         
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\ 
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){ 
            
    $total=0
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name && 
                    
    $v['reject_type'] == $reject_type){ 
                    
    $total += $v['reject']; 
                }     
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
    //echo "<pre>";  
    //print_r($Reports);  
    //echo "</pre>";  

    ////Build display data BEFORE output to browser////  
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\">  
            <thead>  
                <tr>  
                    <th>Compound</th>\r"
    ;  
                    
    //I'll keep your id identifier in place                  
                    
    $i=1
                    
    $dates dates($Reports);                  
                    foreach(
    $dates as $date){  
                        
    //We'll run our columns function in our date loop to get column count 
                        
    $cols columns($Reports,$date);  
                         
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r";  
                         
                        
    $i++; 
                    }  
                
    $display .= "</tr>  
                </thead>"
    ;       
                 
                    
    $process_names process_names($Reports);  
                    foreach(
    $process_names as $process_name){                  
                        
    $display .= "<tr>\r";      
                            
    $display .= "<td>$process_name</td>\r";     
                             
                        foreach(
    $dates as $date){       
                               
    $bor=0;                                
                             
    $reject_types reject_types($Reports,$date,$process_name); 
                            
    $reject_type_count count($reject_types); 
                            
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                            
    $cols columns($Reports,$date); 
                             
                            if (
    $type_for_date == "false"){ 
                                for(
    $c=0;$c<$cols;$c++){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>&nbsp;</td>\r";     
                                     
                                    
    $bor++; 
                                } 
                            }else{ 
                                foreach(
    $reject_types as $reject_type){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>$reject_type$bor</td>\r";  
                                     
                                    
    $bor++;  
                                }                             
                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>&nbsp;</td>\r";     
                                     
                                    
    $bor++; 
                                } 
                            }                          
                        }                       
                
    $display .= "</tr>\r"
                 
                    
    $compound_types compound_types($Reports,$process_name);             
                            foreach(
    $compound_types as $compound_type){                  
                                
    $display .= "<tr>\r";      
                                    
    $display .= "<td>$compound_type</td>\r"
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                            
    $display .= "<td$border>&nbsp;</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{ 
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" class=\"border\"" '');  
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type); 
                                            
    $reject = (!empty($reject) ? $reject "&nbsp;"); 
                                                                                 
                                            
    $display .= "<td$border>$reject</td>\r"
                                                 
                                            
    $bor++;                                     
                                                                         
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                
    $border = ($bor==" class=\"border\"" ''); 
                                                       
                                                
    $display .= "<td$border>&nbsp;</td>\r"
                                                 
                                                
    $bor++; 
                                            } 
                                        } 
                                    }                                                 
                                }                          
                
    $display .= "</tr>\r";                                                                                                                   
                            }  
                          
               
    //---total rejects per process and per reject type---// 
                            
    $display .= "<tr>\r";  
                            
    $display .= "<td class=\"total\">Total</td>\r";                        
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" border" '');  
                                                                  
                                            
    $display .= "<td class=\"total$border\">&nbsp;</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{ 
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" border" '');   
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type); 
                                            
    $rejectTotal = (!empty($rejectTotal) ? $rejectTotal "&nbsp;"); 
                                                                                 
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r"
                                                 
                                            
    $bor++;                                     
                                                                         
                                            for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                
    $border = ($bor==" border" ''); 
                                                               
                                                
    $display .= "<td class=\"total$border\">&nbsp;</td>\r"
                                                 
                                                
    $bor++; 
                                            } 
                                        } 
                                    }                                                 
                                }   
                            
    $display .= "</tr>\r"
                            }                 
        
    $display .= "</table>\r"
    }


            if(isset(
    $display)){ 
                echo 
    "$display"
            } 
    ?>
    on my first dummy data sample the layout is right, but when I tried the real data the display is like what I attached, for better understanding I also attached my database.


    Thank you so much.
    Attached Images Attached Images
    Attached Files Attached Files

  17. #17
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Filling in of total columns by Process_Name needs to be done individually for each reject_type so we count these as we loop through the display and when the total count is reached, THEN we add extra <td>*</td> cells to fill up to max count for date.

    I also noticed with this NEW SQL file that there are duplicate entries for particular rejects. This was causing reject values and totals to not match. For example:
    Code:
    [58] => Array
            (
                [reject_date] => 2013-07-22
                [process_name] => Core Molding
                [reject_type] => SC
                [compound_type] => P35
                [reject] => 31.90
            )
    AND
    Code:
    [74] => Array
            (
                [reject_date] => 2013-07-22
                [process_name] => Core Molding
                [reject_type] => SC
                [compound_type] => P35
                [reject] => 0.50
            )
    I don't know if this is a database error or there will be duplicate entries for same date and type. What I did was ADD duplicates together, so instead of the last match being shown we show the total of both as $reject. Rejects and totals seem to be adding up and columns are matching now.

    PHP 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 "29"

    //$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) = '
    $id'  
        ORDER BY r.reject_date, compound_type  ASC"
    ;   
        
    $res mysql_query($sql); 
        
    $k=0;              
        while(
    $row mysql_fetch_assoc($res)){                  
            
    $Reports[$k]['reject_date'] = $row['reject_date']; 
            
    $Reports[$k]['process_name'] = $row['process_name']; 
            
    $Reports[$k]['reject_type'] = $row['reject_type']; 
            
    $Reports[$k]['compound_type'] = $row['compound_type']; 
            
    $Reports[$k]['reject'] = $row['reject'];  
           
    $k++;  
        } 
         
        
    //| *************************************************** |\\ 
        //| Build functions to return specific values or arrays |\\ 
        //| *************************************************** |\\ 
         
        // Returns dates array \\ 
        
    function dates($array){ 
            
    $dates = array();     
            foreach(
    $array as $k => $v){ 
                
    $dates[] = $v['reject_date']; 
            } 
            
    $dates array_unique($dates); 
            
    sort($dates); 
            return 
    $dates;  
        } 
         
        
    // Returns process_names array \\ 
        
    function process_names($array){     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                
    $process_names[] = $v['process_name']; 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names); 
            return 
    $process_names;  
        } 
         
        
    //*****************************************//     
           // Returns number of columns by date \\ 
        //*****************************************//     
        
    function columns($array,$date){ 
            
    //process names     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $date){ 
                    
    $process_names[] = $v['process_name']; 
                } 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names);                              
            
    // get reject_types by process name 
            
    $reject_types = array(); 
            foreach(
    $process_names as $process_name){ 
                foreach(
    $array as $k => $v){ 
                    if (
    $v['reject_date'] == $date &&   
                        
    $v['process_name'] == $process_name){ 
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type']; 
                    }     
                } 
            }     
            
    //Convert reject_type to count         
            
    $counts = array(); 
            foreach(
    $reject_types as $reject_type){ 
                
    $counts[] = count($reject_type); 
            } 
            
    //return largest value 
            
    $max_count max($counts); 
            return 
    $max_count;   
        } 
        
    //*****************************************//     
         // END Returns number of columns by date \\ 
        //*****************************************//     
         
        // Returns compound_type array for process_name \\ 
        
    function compound_types($array,$process_name){     
            
    $compound_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['process_name'] == $process_name){ 
                    
    $compound_types[] = $v['compound_type']; 
                }     
            } 
            
    $compound_types array_unique($compound_types); 
            
    sort($compound_types); 
            return 
    $compound_types;  
        } 
         
        
    // Returns reject_type array for date and process_name \\ 
        
    function reject_types($array,$reject_date,$process_name){     
            
    $reject_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name){ 
                    
    $reject_types[] = $v['reject_type']; 
                }     
            } 
            
    $reject_types array_unique($reject_types); 
            
    sort($reject_types); 
            return 
    $reject_types;  
        } 
          
        
    // Returns reject amount for cell \\ 
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){ 
            
    $result ""
            
    $total=0;     
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == "{$reject_date}&&   
                    
    $v['process_name'] == "{$process_name}&& 
                    
    $v['reject_type'] == "{$reject_type}&& 
                    
    $v['compound_type'] == "{$compound_type}"){ 
                    
    $total += $v['reject']; 
                } 
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\ 
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){ 
            
    $total=0
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name && 
                    
    $v['reject_type'] == $reject_type){ 
                    
    $total += $v['reject']; 
                }     
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
    //echo "<pre>";  
    //print_r($Reports);  
    //echo "</pre>";  

    ////Build display data BEFORE output to browser////  
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\">  
            <thead>  
                <tr>  
                    <th>Compound</th>\r"
    ;  
                    
    //I'll keep your id identifier in place                  
                    
    $i=1
                    
    $dates dates($Reports);                  
                    foreach(
    $dates as $date){  
                        
    //We'll run our columns function in our date loop to get column count 
                        
    $cols columns($Reports,$date);  
                         
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r";  
                         
                        
    $i++; 
                    }  
                
    $display .= "</tr>  
                </thead>"
    ;       
                 
                    
    $process_names process_names($Reports);  
                    foreach(
    $process_names as $process_name){                  
                        
    $display .= "<tr>\r";      
                            
    $display .= "<td>$process_name</td>\r";     
                             
                        foreach(
    $dates as $date){       
                               
    $bor=0;                                
                            
    $reject_types reject_types($Reports,$date,$process_name); 
                            
    $reject_type_count count($reject_types); 
                            
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                            
    $cols columns($Reports,$date); 
                             
                            if (
    $type_for_date == "false"){ 
                                for(
    $c=0;$c<$cols;$c++){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>&nbsp;</td>\r";     
                                     
                                    
    $bor++; 
                                } 
                            }else{ 
                                
    $rt_running_count=0;
                                foreach(
    $reject_types as $reject_type){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>$reject_type</td>\r";  
                                     
                                    
    $bor++;
                                    
    $rt_running_count++;  
                                }
                                if (
    $rt_running_count==$reject_type_count){                             
                                    for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                        
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                        
    $display .= "<td$border>&nbsp;</td>\r";     
                                         
                                        
    $bor++; 
                                    }
                                } 
                            }                          
                        }                       
                
    $display .= "</tr>\r"
                    
    $compound_types compound_types($Reports,$process_name);             
                            foreach(
    $compound_types as $compound_type){                  
                                
    $display .= "<tr>\r";      
                                    
    $display .= "<td>$compound_type</td>\r"
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                            
    $display .= "<td$border>&nbsp;</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{
                                    
                                        
    $ct_running_count=0
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" class=\"border\"" '');  
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type); 
                                            
    $reject = (!empty($reject) ? $reject "&nbsp;"); 
                                                                                 
                                            
    $display .= "<td$border>$reject</td>\r"
                                                 
                                            
    $bor++;
                                            
    $ct_running_count++;                                     
                                            
                                            if (
    $ct_running_count==$reject_type_count){                         
                                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                           
                                                    
    $display .= "<td$border>&nbsp;</td>\r"
                                                     
                                                    
    $bor++; 
                                                }
                                            } 
                                        } 
                                    }                                                 
                                }                          
                
    $display .= "</tr>\r";                                                                                             
                            }  
                                       
               
    //---total rejects per process and per reject type---// 
                            
    $display .= "<tr>\r";  
                            
    $display .= "<td class=\"total\">Total</td>\r";                        
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" border" '');  
                                                                  
                                            
    $display .= "<td class=\"total$border\">&nbsp;</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{ 
                                        
    $rt_running_count=0
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" border" '');   
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type); 
                                            
    $rejectTotal = (!empty($rejectTotal) ? $rejectTotal "&nbsp;"); 
                                                                                 
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r"
                                                 
                                            
    $bor++;
                                            
    $rt_running_count++;                                      
                                            
                                            if (
    $rt_running_count==$reject_type_count){                             
                                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                    
    $border = ($bor==" border" ''); 
                                                                   
                                                    
    $display .= "<td class=\"total$border\">&nbsp;</td>\r"
                                                     
                                                    
    $bor++; 
                                                }
                                            } 
                                        } 
                                    }                                                 
                                }   
                            
    $display .= "</tr>\r"
                            }                 
        
    $display .= "</table>\r"
    }


            if(isset(
    $display)){ 
                echo 
    "$display"
            } 
    ?>

  18. #18
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Filling in of total columns by Process_Name needs to be done individually for each reject_type so we count these as we loop through the display and when the total count is reached, THEN we add extra <td>*</td> cells to fill up to max count for date.

    I also noticed with this NEW SQL file that there are duplicate entries for particular rejects. This was causing reject values and totals to not match. For example:
    Code:
    [58] => Array
            (
                [reject_date] => 2013-07-22
                [process_name] => Core Molding
                [reject_type] => SC
                [compound_type] => P35
                [reject] => 31.90
            )
    AND
    Code:
    [74] => Array
            (
                [reject_date] => 2013-07-22
                [process_name] => Core Molding
                [reject_type] => SC
                [compound_type] => P35
                [reject] => 0.50
            )
    I don't know if this is a database error or there will be duplicate entries for same date and type. What I did was ADD duplicates together, so instead of the last match being shown we show the total of both as $reject. Rejects and totals seem to be adding up and columns are matching now.

    PHP 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 "29"

    //$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) = '
    $id'  
        ORDER BY r.reject_date, compound_type  ASC"
    ;   
        
    $res mysql_query($sql); 
        
    $k=0;              
        while(
    $row mysql_fetch_assoc($res)){                  
            
    $Reports[$k]['reject_date'] = $row['reject_date']; 
            
    $Reports[$k]['process_name'] = $row['process_name']; 
            
    $Reports[$k]['reject_type'] = $row['reject_type']; 
            
    $Reports[$k]['compound_type'] = $row['compound_type']; 
            
    $Reports[$k]['reject'] = $row['reject'];  
           
    $k++;  
        } 
         
        
    //| *************************************************** |\\ 
        //| Build functions to return specific values or arrays |\\ 
        //| *************************************************** |\\ 
         
        // Returns dates array \\ 
        
    function dates($array){ 
            
    $dates = array();     
            foreach(
    $array as $k => $v){ 
                
    $dates[] = $v['reject_date']; 
            } 
            
    $dates array_unique($dates); 
            
    sort($dates); 
            return 
    $dates;  
        } 
         
        
    // Returns process_names array \\ 
        
    function process_names($array){     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                
    $process_names[] = $v['process_name']; 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names); 
            return 
    $process_names;  
        } 
         
        
    //*****************************************//     
           // Returns number of columns by date \\ 
        //*****************************************//     
        
    function columns($array,$date){ 
            
    //process names     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $date){ 
                    
    $process_names[] = $v['process_name']; 
                } 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names);                              
            
    // get reject_types by process name 
            
    $reject_types = array(); 
            foreach(
    $process_names as $process_name){ 
                foreach(
    $array as $k => $v){ 
                    if (
    $v['reject_date'] == $date &&   
                        
    $v['process_name'] == $process_name){ 
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type']; 
                    }     
                } 
            }     
            
    //Convert reject_type to count         
            
    $counts = array(); 
            foreach(
    $reject_types as $reject_type){ 
                
    $counts[] = count($reject_type); 
            } 
            
    //return largest value 
            
    $max_count max($counts); 
            return 
    $max_count;   
        } 
        
    //*****************************************//     
         // END Returns number of columns by date \\ 
        //*****************************************//     
         
        // Returns compound_type array for process_name \\ 
        
    function compound_types($array,$process_name){     
            
    $compound_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['process_name'] == $process_name){ 
                    
    $compound_types[] = $v['compound_type']; 
                }     
            } 
            
    $compound_types array_unique($compound_types); 
            
    sort($compound_types); 
            return 
    $compound_types;  
        } 
         
        
    // Returns reject_type array for date and process_name \\ 
        
    function reject_types($array,$reject_date,$process_name){     
            
    $reject_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name){ 
                    
    $reject_types[] = $v['reject_type']; 
                }     
            } 
            
    $reject_types array_unique($reject_types); 
            
    sort($reject_types); 
            return 
    $reject_types;  
        } 
          
        
    // Returns reject amount for cell \\ 
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){ 
            
    $result ""
            
    $total=0;     
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == "{$reject_date}&&   
                    
    $v['process_name'] == "{$process_name}&& 
                    
    $v['reject_type'] == "{$reject_type}&& 
                    
    $v['compound_type'] == "{$compound_type}"){ 
                    
    $total += $v['reject']; 
                } 
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\ 
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){ 
            
    $total=0
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name && 
                    
    $v['reject_type'] == $reject_type){ 
                    
    $total += $v['reject']; 
                }     
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
    //echo "<pre>";  
    //print_r($Reports);  
    //echo "</pre>";  

    ////Build display data BEFORE output to browser////  
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\">  
            <thead>  
                <tr>  
                    <th>Compound</th>\r"
    ;  
                    
    //I'll keep your id identifier in place                  
                    
    $i=1
                    
    $dates dates($Reports);                  
                    foreach(
    $dates as $date){  
                        
    //We'll run our columns function in our date loop to get column count 
                        
    $cols columns($Reports,$date);  
                         
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r";  
                         
                        
    $i++; 
                    }  
                
    $display .= "</tr>  
                </thead>"
    ;       
                 
                    
    $process_names process_names($Reports);  
                    foreach(
    $process_names as $process_name){                  
                        
    $display .= "<tr>\r";      
                            
    $display .= "<td>$process_name</td>\r";     
                             
                        foreach(
    $dates as $date){       
                               
    $bor=0;                                
                            
    $reject_types reject_types($Reports,$date,$process_name); 
                            
    $reject_type_count count($reject_types); 
                            
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                            
    $cols columns($Reports,$date); 
                             
                            if (
    $type_for_date == "false"){ 
                                for(
    $c=0;$c<$cols;$c++){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>*</td>\r";     
                                     
                                    
    $bor++; 
                                } 
                            }else{ 
                                
    $rt_running_count=0;
                                foreach(
    $reject_types as $reject_type){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>$reject_type</td>\r";  
                                     
                                    
    $bor++;
                                    
    $rt_running_count++;  
                                }
                                if (
    $rt_running_count==$reject_type_count){                             
                                    for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                        
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                        
    $display .= "<td$border>*</td>\r";     
                                         
                                        
    $bor++; 
                                    }
                                } 
                            }                          
                        }                       
                
    $display .= "</tr>\r"
                    
    $compound_types compound_types($Reports,$process_name);             
                            foreach(
    $compound_types as $compound_type){                  
                                
    $display .= "<tr>\r";      
                                    
    $display .= "<td>$compound_type</td>\r"
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                            
    $display .= "<td$border>*</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{
                                    
                                        
    $ct_running_count=0
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" class=\"border\"" '');  
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type); 
                                            
    $reject = (!empty($reject) ? $reject "*"); 
                                                                                 
                                            
    $display .= "<td$border>$reject</td>\r"
                                                 
                                            
    $bor++;
                                            
    $ct_running_count++;                                     
                                            
                                            if (
    $ct_running_count==$reject_type_count){                         
                                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                           
                                                    
    $display .= "<td$border>*</td>\r"
                                                     
                                                    
    $bor++; 
                                                }
                                            } 
                                        } 
                                    }                                                 
                                }                          
                
    $display .= "</tr>\r";                                                                                             
                            }  
                                       
               
    //---total rejects per process and per reject type---// 
                            
    $display .= "<tr>\r";  
                            
    $display .= "<td class=\"total\">Total</td>\r";                        
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" border" '');  
                                                                  
                                            
    $display .= "<td class=\"total$border\">*</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{ 
                                        
    $rt_running_count=0
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" border" '');   
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type); 
                                            
    $rejectTotal = (!empty($rejectTotal) ? $rejectTotal "*"); 
                                                                                 
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r"
                                                 
                                            
    $bor++;
                                            
    $rt_running_count++;                                      
                                            
                                            if (
    $rt_running_count==$reject_type_count){                             
                                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                    
    $border = ($bor==" border" ''); 
                                                                   
                                                    
    $display .= "<td class=\"total$border\">*</td>\r"
                                                     
                                                    
    $bor++; 
                                                }
                                            } 
                                        } 
                                    }                                                 
                                }   
                            
    $display .= "</tr>\r"
                            }                 
        
    $display .= "</table>\r"
    }


            if(isset(
    $display)){ 
                echo 
    "$display"
            } 
    ?>

    Hi,

    Yes there's a possible that it has two values for the same date and reject type. so it will be SUM.

    Thank you

  19. #19
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    Filling in of total columns by Process_Name needs to be done individually for each reject_type so we count these as we loop through the display and when the total count is reached, THEN we add extra <td>*</td> cells to fill up to max count for date.

    I also noticed with this NEW SQL file that there are duplicate entries for particular rejects. This was causing reject values and totals to not match. For example:
    Code:
    [58] => Array
            (
                [reject_date] => 2013-07-22
                [process_name] => Core Molding
                [reject_type] => SC
                [compound_type] => P35
                [reject] => 31.90
            )
    AND
    Code:
    [74] => Array
            (
                [reject_date] => 2013-07-22
                [process_name] => Core Molding
                [reject_type] => SC
                [compound_type] => P35
                [reject] => 0.50
            )
    I don't know if this is a database error or there will be duplicate entries for same date and type. What I did was ADD duplicates together, so instead of the last match being shown we show the total of both as $reject. Rejects and totals seem to be adding up and columns are matching now.

    PHP 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 "29"

    //$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) = '
    $id'  
        ORDER BY r.reject_date, compound_type  ASC"
    ;   
        
    $res mysql_query($sql); 
        
    $k=0;              
        while(
    $row mysql_fetch_assoc($res)){                  
            
    $Reports[$k]['reject_date'] = $row['reject_date']; 
            
    $Reports[$k]['process_name'] = $row['process_name']; 
            
    $Reports[$k]['reject_type'] = $row['reject_type']; 
            
    $Reports[$k]['compound_type'] = $row['compound_type']; 
            
    $Reports[$k]['reject'] = $row['reject'];  
           
    $k++;  
        } 
         
        
    //| *************************************************** |\\ 
        //| Build functions to return specific values or arrays |\\ 
        //| *************************************************** |\\ 
         
        // Returns dates array \\ 
        
    function dates($array){ 
            
    $dates = array();     
            foreach(
    $array as $k => $v){ 
                
    $dates[] = $v['reject_date']; 
            } 
            
    $dates array_unique($dates); 
            
    sort($dates); 
            return 
    $dates;  
        } 
         
        
    // Returns process_names array \\ 
        
    function process_names($array){     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                
    $process_names[] = $v['process_name']; 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names); 
            return 
    $process_names;  
        } 
         
        
    //*****************************************//     
           // Returns number of columns by date \\ 
        //*****************************************//     
        
    function columns($array,$date){ 
            
    //process names     
            
    $process_names = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $date){ 
                    
    $process_names[] = $v['process_name']; 
                } 
            } 
            
    $process_names array_unique($process_names); 
            
    sort($process_names);                              
            
    // get reject_types by process name 
            
    $reject_types = array(); 
            foreach(
    $process_names as $process_name){ 
                foreach(
    $array as $k => $v){ 
                    if (
    $v['reject_date'] == $date &&   
                        
    $v['process_name'] == $process_name){ 
                        
    $reject_types[$process_name][$v['reject_type']] = $v['reject_type']; 
                    }     
                } 
            }     
            
    //Convert reject_type to count         
            
    $counts = array(); 
            foreach(
    $reject_types as $reject_type){ 
                
    $counts[] = count($reject_type); 
            } 
            
    //return largest value 
            
    $max_count max($counts); 
            return 
    $max_count;   
        } 
        
    //*****************************************//     
         // END Returns number of columns by date \\ 
        //*****************************************//     
         
        // Returns compound_type array for process_name \\ 
        
    function compound_types($array,$process_name){     
            
    $compound_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['process_name'] == $process_name){ 
                    
    $compound_types[] = $v['compound_type']; 
                }     
            } 
            
    $compound_types array_unique($compound_types); 
            
    sort($compound_types); 
            return 
    $compound_types;  
        } 
         
        
    // Returns reject_type array for date and process_name \\ 
        
    function reject_types($array,$reject_date,$process_name){     
            
    $reject_types = array(); 
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name){ 
                    
    $reject_types[] = $v['reject_type']; 
                }     
            } 
            
    $reject_types array_unique($reject_types); 
            
    sort($reject_types); 
            return 
    $reject_types;  
        } 
          
        
    // Returns reject amount for cell \\ 
        
    function searchresults($array,$reject_date,$process_name,$reject_type,$compound_type){ 
            
    $result ""
            
    $total=0;     
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == "{$reject_date}&&   
                    
    $v['process_name'] == "{$process_name}&& 
                    
    $v['reject_type'] == "{$reject_type}&& 
                    
    $v['compound_type'] == "{$compound_type}"){ 
                    
    $total += $v['reject']; 
                } 
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
        
    // Returns reject total based on $reject_date,$process_name,$reject_type \\ 
        
    function resulttotals($array,$reject_date,$process_name,$reject_type){ 
            
    $total=0
            foreach(
    $array as $k => $v){ 
                if (
    $v['reject_date'] == $reject_date &&   
                    
    $v['process_name'] == $process_name && 
                    
    $v['reject_type'] == $reject_type){ 
                    
    $total += $v['reject']; 
                }     
            } 
            
    $total = ($total!=number_format($total2'.''') : ''); 
            return 
    $total;  
        } 
         
    //echo "<pre>";  
    //print_r($Reports);  
    //echo "</pre>";  

    ////Build display data BEFORE output to browser////  
    $display "<table border=0 cellpadding=0 cellspacing=0 class=\"display\">  
            <thead>  
                <tr>  
                    <th>Compound</th>\r"
    ;  
                    
    //I'll keep your id identifier in place                  
                    
    $i=1
                    
    $dates dates($Reports);                  
                    foreach(
    $dates as $date){  
                        
    //We'll run our columns function in our date loop to get column count 
                        
    $cols columns($Reports,$date);  
                         
                        
    $display .= "<th id=col".$i." colspan=\"$cols\">$date</th>\r";  
                         
                        
    $i++; 
                    }  
                
    $display .= "</tr>  
                </thead>"
    ;       
                 
                    
    $process_names process_names($Reports);  
                    foreach(
    $process_names as $process_name){                  
                        
    $display .= "<tr>\r";      
                            
    $display .= "<td>$process_name</td>\r";     
                             
                        foreach(
    $dates as $date){       
                               
    $bor=0;                                
                            
    $reject_types reject_types($Reports,$date,$process_name); 
                            
    $reject_type_count count($reject_types); 
                            
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                            
    $cols columns($Reports,$date); 
                             
                            if (
    $type_for_date == "false"){ 
                                for(
    $c=0;$c<$cols;$c++){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>&nbsp;</td>\r";     
                                     
                                    
    $bor++; 
                                } 
                            }else{ 
                                
    $rt_running_count=0;
                                foreach(
    $reject_types as $reject_type){     
                                    
    $border = ($bor==" class=\"border\"" '');  
                                                      
                                    
    $display .= "<td$border>$reject_type</td>\r";  
                                     
                                    
    $bor++;
                                    
    $rt_running_count++;  
                                }
                                if (
    $rt_running_count==$reject_type_count){                             
                                    for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                        
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                        
    $display .= "<td$border>&nbsp;</td>\r";     
                                         
                                        
    $bor++; 
                                    }
                                } 
                            }                          
                        }                       
                
    $display .= "</tr>\r"
                    
    $compound_types compound_types($Reports,$process_name);             
                            foreach(
    $compound_types as $compound_type){                  
                                
    $display .= "<tr>\r";      
                                    
    $display .= "<td>$compound_type</td>\r"
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" class=\"border\"" '');  
                                                          
                                            
    $display .= "<td$border>&nbsp;</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{
                                    
                                        
    $ct_running_count=0
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" class=\"border\"" '');  
                                            
    $reject searchresults($Reports,$date,$process_name,$reject_type,$compound_type); 
                                            
    $reject = (!empty($reject) ? $reject "&nbsp;"); 
                                                                                 
                                            
    $display .= "<td$border>$reject</td>\r"
                                                 
                                            
    $bor++;
                                            
    $ct_running_count++;                                     
                                            
                                            if (
    $ct_running_count==$reject_type_count){                         
                                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                    
    $border = ($bor==" class=\"border\"" ''); 
                                                           
                                                    
    $display .= "<td$border>&nbsp;</td>\r"
                                                     
                                                    
    $bor++; 
                                                }
                                            } 
                                        } 
                                    }                                                 
                                }                          
                
    $display .= "</tr>\r";                                                                                             
                            }  
                                       
               
    //---total rejects per process and per reject type---// 
                            
    $display .= "<tr>\r";  
                            
    $display .= "<td class=\"total\">Total</td>\r";                        
                                                         
                                foreach(
    $dates as $date){        
                                    
    $bor=0;               
                                    
    $reject_types reject_types($Reports,$date,$process_name); 
                                    
    $reject_type_count count($reject_types); 
                                    
    $type_for_date = (!empty($reject_types) ? "true" "false"); 
                                    
    $cols columns($Reports,$date); 
                                     if (
    $type_for_date == "false"){ 
                                        for(
    $c=0;$c<$cols;$c++){ 
                                            
    $border = ($bor==" border" '');  
                                                                  
                                            
    $display .= "<td class=\"total$border\">&nbsp;</td>\r"
                                             
                                            
    $bor++; 
                                        } 
                                    }else{ 
                                        
    $rt_running_count=0
                                        foreach(
    $reject_types as $reject_type){     
                                            
    $border = ($bor==" border" '');   
                                            
    $rejectTotal resulttotals($Reports,$date,$process_name,$reject_type); 
                                            
    $rejectTotal = (!empty($rejectTotal) ? $rejectTotal "&nbsp;"); 
                                                                                 
                                            
    $display .= "<td class=\"total$border\">$rejectTotal</td>\r"
                                                 
                                            
    $bor++;
                                            
    $rt_running_count++;                                      
                                            
                                            if (
    $rt_running_count==$reject_type_count){                             
                                                for(
    $d=$reject_type_count;$d<$cols;$d++){     
                                                    
    $border = ($bor==" border" ''); 
                                                                   
                                                    
    $display .= "<td class=\"total$border\">&nbsp;</td>\r"
                                                     
                                                    
    $bor++; 
                                                }
                                            } 
                                        } 
                                    }                                                 
                                }   
                            
    $display .= "</tr>\r"
                            }                 
        
    $display .= "</table>\r"
    }


            if(isset(
    $display)){ 
                echo 
    "$display"
            } 
    ?>

    Hi,

    I tried your code and I attached the output displayed.

    Is it possible that we can add also a total on the rightside per date

    for example

    Compound-----------2013-07-22-------
    Core Molding--C-R-SC-SP-SS-T-TI--Total
    P41------------2.92-3.57-2.33--3.39 12.21 and like the total below of compound the total on the right side should has a color backgournd also..


    Thank you so much

  20. #20
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    Hi,

    I tried your code and I attached the output displayed.

    Is it possible that we can add also a total on the rightside per date

    for example

    Compound-----------2013-07-22-------
    Core Molding--C-R-SC-SP-SS-T-TI--Total
    P41------------2.92-3.57-2.33--3.39 12.21 and like the total below of compound the total on the right side should has a color backgournd also..


    Thank you so much
    I don't see attachment. Did output show correctly? I assume it didn't as you attached image.

  21. #21
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,


    Sorry I forgot to attached the image.


    Here the attached image .


    Thank you so much
    Attached Images Attached Images

  22. #22
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much...for your help..I really appreciate it.

  23. #23
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Drummin View Post
    I don't see attachment. Did output show correctly? I assume it didn't as you attached image.
    Did you see now the screenshot?

    Thank you.

  24. #24
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    Attachments Pending Approval

  25. #25
    SitePoint Evangelist bronze trophy
    Join Date
    Feb 2013
    Posts
    507
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)
    While we're waiting for images to be approved, here's a shot with compound totals.
    Sample133.jpg