Got problem in table layout

Hi,

I got problem in my table layout.

my table data came from database using php while loop and foreach.

Here is my scenario.

I need to display the reject type per date.
like this:

Compound-- space---2013-07-24 2013-07-25
Compound Mixing----SC-SP-SH----PG-PT

2013-07-24 has 3 reject types SC SP SH
2013-07-25 has 2 reject types PG PT

i want to display the reject type on the date where its belong.
but in this code:

Code:

---


<table><tr><thead><th>Compound</th><th>2013-07-24</th><th>2013-07-25</th></thead></tr><tr><td>Compound Mixing</td><td>SC</td><td>SP</td><td>SH</td><td>PG</td><td>PT</td></table>

---

the output is like this:
as you can see the SC and SP was size like the size of the date. but it supposively 3 reject type within 2013-07-24 and 2 for 2013-07-25

Compound-- space---2013-07-24 2013-07-25
Compound Mixing---SC----------SP-----------SH-PG-PT


and here is the php 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'];
    }
    
        foreach($report_date AS $report_date)
        {
            echo "<th>$report_date</th>"; 
        }
        echo "</thead>";
    echo "</tr>";

        $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'";
        $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>";
            }
        }   
}

     }   
  echo "</table>"; 
 }
?>

---

I hope somebody can help me through this.


Thank you so much.

Not able to test but as a guess maybe try editing the php next to the table cell for the reject_type to be

Code:

---

  if($cnt_rej > 0)   
        {
            echo "<td>';
            while($row_rej=mysql_fetch_assoc($res_rej))
            {
                $reject_type = $row_rej['reject_type'];
               
                echo "$reject_type";
            }
            echo "</td>';
        }

---

I am assuming the while row code will bring up all of the codes for that date

Thank you... I will try it later... :)

Hi, I tried your code and i attached the output and also the format i need to create.


Thank you.

Hi,

here is my new 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>"; 
 }
?>

---

and now i got a problem in displaying the rejects amount per reject type.
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 so much