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.
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.
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