Got problem in table layout

[rhodarose]

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>



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>";
}
?>



I hope somebody can help me through this.


Thank you so much.

[gwmbox]

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



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

[rhodarose]

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

[rhodarose]

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


Thank you.

[rhodarose]

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>";
}
?>


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