I have modified your code as following, please check the comment that added and modified by thietkeweb:
<?php
elseif($_POST["from1_date"] && $_POST["to1_date"])
{
$from_date = mysql_real_escape_string($_POST['from1_date']);
$to_date = mysql_real_escape_string($_POST['to1_date']);
$shift = mysql_real_escape_string($_POST['shift1']);
// Added by thietkeweb
if($_POST["shift1"]) {
$shift_extra_query1 = "AND d.shift='$shift'";
$shift_extra_query2 = "AND t.shift='$shift'";
}
$Batch_yield = 0;
$c_input = 0;
$co_output = 0;
$loopctr = 0;
$totalloop = 0;
// modified by thietkeweb
$query = "SELECT d.operation_name, SUM(d.input_qty) AS inputqty, SUM(d.output_qty) AS outputqty FROM traceability d, plt_transact t WHERE t.plt_date BETWEEN '$from_date' AND '$to_date' ".$shift_extra_query1." AND d.operation_name IN ('01 Oper', '02 Oper', '03 Oper', '04 Oper' , '05 Oper' , '06 Oper' , '07 Oper', '08 Oper' , '09 Oper' , '10 Oper') GROUP BY d.operation_name ORDER BY d.operation_name";
$result = mysql_query($query);
if($result)
{
echo "<table cellspacing='2' style='font-family: arial narrow; font-size: 12px; border-width: 2px 2px 2px 2px; border-style: solid;'>";
echo "<tr>";
echo "<tr><b> From Date: " . $_POST['from1_date'] . "</b></tr>";
echo "<tr><b> To Date: " . $_POST['to1_date'] . "</b></tr>";
echo "<tr><b> Shift Date: " . $_POST['shift1'] . "</b></tr>";
echo "<th class='tdclass'>PLT #</th>";
$total_row = mysql_num_rows($result);
$total_columns = $total_row;
for($ctr=0; $ctr < $total_row; $ctr++)
{
$opname = mysql_result($result,$ctr,"operation_name");
$i = strpos($opname," ",0);
$opname = substr($opname,$i);
echo "<th colspan='3' class='tdclass'>" . $opname . "<br /></th>";
}
echo "<th class='tdclass'>Batch Yield</th>";
echo "</tr>";
echo "<tr>";
echo "<td class='tdclass'></td>";
for($ctr=0; $ctr < $total_row; $ctr++)
{
echo "<td class='tdclass'>Input</td>";
echo "<td class='tdclass'>Output</td>";
echo "<td class='tdclass'>Yield</td>";
}
echo "<td class='tdclass'>";
echo "</td>";
echo "</tr>";
}
// modified by thietkeweb
$query = "SELECT DISTINCT p.plt_no FROM plt_transact p, traceability t WHERE plt_date BETWEEN '" . $_POST["from1_date"] . "' AND '" . $_POST["to1_date"] . "' ".$shift_extra_query2." AND p.plt_transact_id = t.plt_transact_id";
$result_loop = mysql_query($query);
while($row = mysql_fetch_array($result_loop))
{
$loopctr += 1;
$plt_no = $row["plt_no"];
$query = "SELECT * FROM plt_transact WHERE plt_no = '$plt_no'";
$result_no = mysql_query($query);
if($result_no)
{
if(mysql_num_rows($result_no) > 0)
{
$chemical_input = 0;
$core_output = 0;
$query = "SELECT SUM(t.output_qty) AS c_input FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='01 Oper' AND p.plt_no = '$plt_no'";
$resultyield = mysql_query($query);
if($resultyield)
{
if(mysql_num_rows($resultyield) > 0) $c_input = $c_input + mysql_result($resultyield,0,"c_input");
}
$query = "SELECT SUM(t.output_qty) AS co_output FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='10 Oper' AND p.plt_no = '$plt_no'";
$resultyield = mysql_query($query);
if($resultyield)
{
if(mysql_num_rows($resultyield) > 0) $co_output = $co_output + mysql_result($resultyield,0,"co_output");
}
$PLT_yield = @($co_output / $c_input) * 100;
$query = "SELECT SUM(t.input_qty) AS c_input FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='01 Oper' AND p.plt_no = '$plt_no'";
$resultyield = mysql_query($query);
if($resultyield)
{
if(mysql_num_rows($resultyield) > 0) $c_input = $chem_input + mysql_result($resultyield,0,"c_input");
}
$query = "SELECT SUM(t.output_qty) AS co_output FROM traceability t, plt_transact p WHERE t.plt_transact_id = p.plt_transact_id AND t.operation_name='10 Oper' AND p.plt_no = '$plt_no'";
$resultyield = mysql_query($query);
if($resultyield)
{
if(mysql_num_rows($resultyield) > 0) $co_output = $co_output + mysql_result($resultyield,0,"co_output");
}
// modified by thietkeweb
$query = "SELECT d.operation_name, SUM(d.input_qty) AS inputqty, SUM(d.output_qty) AS outputqty FROM traceability d, plt_transact t WHERE t.plt_no = '$plt_no' AND t.plt_date BETWEEN '$from_date' AND '$to_date' ".$shift_extra_query1." AND d.plt_transact_id = t.plt_transact_id GROUP BY d.operation_name ORDER BY d.operation_name";
$result = mysql_query($query);
if($result)
{
echo "<tr>";
echo "<td><strong>$plt_no</strong></td>";
while($row2 = mysql_fetch_array($result))
{
echo "<td class='tdclass'>" . number_format($row2["inputqty"],2) . "</td>";
echo "<td class='tdclass'>" . number_format($row2["outputqty"],2) . "</td>";
$inputqty = $row2["inputqty"];
$outputqty = $row2["outputqty"];
$yield = @(($outputqty / $inputqty)*100);
echo "<td class='tdclass'>" . number_format($yield,2) . "</td>";
echo "</td>";
}
echo "<td style='text-align: right; font-weight: bold; font-family: Arial; font-size:10' class = 'tdclass' colspan='";
$loopctr = ($total_row * 2) + 20;
echo "$loopctr'>";
printf ("%01.2f", $PLT_yield);
echo "<strong>%";
echo "</td>";
echo "</tr>";
}
}
}
}
$Batch_yield = @($coresolutioning_output / $chem_input) * 100;
echo "<tr>";
echo "<td style='text-align: right; font-weight: bold; font-family: Arial; font-size:10' class = 'tdclass' colspan='";
$loopctr = ($total_row * 2) + 20;
echo "$loopctr'>Total Batch Yield: ";
printf ("%01.2f", $Batch_yield);
echo "<strong>%";
echo "</td>";
echo "</tr>";
echo "</table>";
}
?>
Explain your code: In your query, when user don’t specify the shift value, then the logic in your MySQL query will be like the following:
Where date between from_date and to_date and shift=‘shift_value’
With that logic, the record in database must be satisfy date range and shift value.
