Got problem in table layout

Discussion in 'HTML & Website Design' started by newphpcoder, Jul 24, 2013.

  1. #1
    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:

    
     
    <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>
    
    
    Code (markup):
    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
    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>"; 
     }
    ?>
    
    PHP:

    I hope somebody can help me through this.


    Thank you so much.
     
    newphpcoder, Jul 24, 2013 IP
  2. ekim941

    ekim941 Member

    Messages:
    74
    Likes Received:
    7
    Best Answers:
    7
    Trophy Points:
    33
    #2
    First, you don't have a closing table row </tr>
    Second, you are doing your $reject_type as a table data <td>$reject_type</td>
    Which is creating more data fields than you have columns.
     
    ekim941, Jul 25, 2013 IP
  3. newphpcoder

    newphpcoder Greenhorn

    Messages:
    57
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    16
    #3
    Hi,
    here is my new 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>"; 
     }
    ?>
    
    PHP:
    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
     

    Attached Files:

    newphpcoder, Jul 25, 2013 IP