Problem in using foreach and while loop in displaying data on table format

Hi…

I tried to use foreach in displaying my table header, but I encountered problem when I tried to display data on the first row , my query only display the last Sum for the last Comp.

here is my code:


<html>
<head>
<title>Half Shell</title>
<link rel="stylesheet" type="text/css" href="kanban.css" />
<?php
  error_reporting(E_ALL ^ E_NOTICE);
  date_default_timezone_set("Asia/Singapore"); //set the time zone
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);


?>

<body>
<form name="param" action="" method="post" onSubmit="return false">

<div id="fieldset_PS">
<?php
   echo "<table>";

   $sql = "SELECT DISTINCT s.Comp FROM sales_order s, param_settings p WHERE s.Comp = p.Compounds ORDER BY s.Comp";
   $res_comp = mysql_query($sql, $con);
   while($row_comp = mysql_fetch_assoc($res_comp)){
        $Comp[] = $row_comp['Comp'];
   }
   echo "<th>&nbsp;</th>";
   foreach($Comp AS $Comp){
    echo "<th>$Comp</th>";
  }

   echo "<tr>
   <td>Total Kg/Compound</td>";
      $sql_sec = "SELECT SUM(TotalKg) AS TotalKg FROM sales_order  WHERE Comp = '$Comp' ORDER BY Comp";
   $res_sec = mysql_query($sql_sec, $con);

   while($row_sec = mysql_fetch_assoc($res_sec)){
       $TotalKg[] = $row_sec['TotalKg'];

   }
   foreach($TotalKg AS $TotalKg){
    echo "<td>$TotalKg</td>
    </tr>";
  }

   ?>

I also attach the correct output that should be and the result from my code.

Thank you

Okay. Lets look at this.


   $sql = "SELECT DISTINCT s.Comp FROM sales_order s, param_settings p WHERE s.Comp = p.Compounds ORDER BY s.Comp";

There is no need for the param_settings table to be in this query, since you’re not using it in any fashion. SELECT DISTINCT when used with a join that does nothing is redundant behavior.
Additionally, we’ll solve your need for multiple queries by using GROUP BY.


   $sql = "SELECT Comp,SUM(TotalKg) AS total FROM sales_order GROUP BY Comp ORDER BY Comp";

Now when you extract it, foreach it twice.

The problem you’re having with your foreach is this:

foreach($TotalKg AS $TotalKg){

You’re using the same variable for your AS. Doing so will overwrite your array during the first execution.


   $sql = "SELECT Comp,SUM(TotalKg) AS total FROM sales_order GROUP BY Comp ORDER BY Comp";
   $res_comp = mysql_query($sql, $con);
   while($row_comp = mysql_fetch_assoc($res_comp)){
        $Comps[] = $row_comp['Comp'];
   }
   echo "<th>&nbsp;</th>";
   foreach($Comps AS $Comp){
    echo "<th>$Comp</th>";
  }

   echo "</tr><tr>
   <td>Total Kg/Compound</td>";
   foreach($Comps AS $TotalKg){
    echo "<td>$TotalKg</td>
    </tr>";
  }

(PS: You didnt close your header row tag.)

ok thank you…

I resolve it now using this code:


<html>
<head>
<title>Half Shell</title>

<link rel="stylesheet" type="text/css" href="kanban.css" />
<?php
  error_reporting(E_ALL ^ E_NOTICE);
  date_default_timezone_set("Asia/Singapore"); //set the time zone
$con = mysql_connect('localhost', 'root','');

if (!$con) {
    echo 'failed';
    die();
}

mysql_select_db("mes", $con);


?>

<body>
<form name="param" action="" method="post" onSubmit="return false">

<div id="fieldset_PS">
<?php
   echo "<table>";

   $sql = "SELECT DISTINCT s.Comp FROM sales_order s, param_settings p WHERE s.Comp = p.Compounds ORDER BY s.Comp";
   $res_comp = mysql_query($sql, $con);
   while($row_comp = mysql_fetch_assoc($res_comp)){
        $Comp[] = $row_comp['Comp'];
   }
   echo "<tr><th>&nbsp;</th>";

   foreach($Comp AS $Comp){
    echo "<th>$Comp</th>";
   }
      echo "<tr><td>Total Kg/Compound</td>";
 $Compound = array();
 $sql = "SELECT DISTINCT s.Comp FROM sales_order s, param_settings p WHERE s.Comp = p.Compounds ORDER BY s.Comp";
   $res_comp = mysql_query($sql, $con);
   while($row_comp = mysql_fetch_assoc($res_comp)){
        $Compound[] = $row_comp['Comp'];
   }
 foreach($Compound AS $Compound)
{
   $sql_sec = "SELECT SUM(TotalKg) AS TotalKg FROM sales_order  WHERE Comp = '$Compound' GROUP BY Comp ORDER BY Comp";

   # add error code compliant with the rest of error code management you are already using
   $result = mysql_query($sql_sec, $con) ;

   while( $row_sec = mysql_fetch_assoc( $result ) )
   {
         $TotalKg = $row_sec['TotalKg'];
         echo "<td>$TotalKg</td>";
   }
}
echo "</tr>";

echo "<tr><td>Demand per day (Kg)</td></tr>";

?>

Now I need to add rows for working days.

Working days is SUM of workingdays of 3 months, I have code for getting the consecutive 3 months but now I got a problem in getting the SUM of working days.

here is my code for that:


echo "<tr><td>Working Days</td>";
function monthNames($from, $to){
   $range=array();
   for($i=$from; $i<=$to; $i++){
           $range[$i]=date('M', mktime(0,0,0,$i));
   }
    return $range;
}

$sql = "SELECT FromMonth, ToMonth FROM so_month";
$res = mysql_query($sql,$con);

$row = mysql_fetch_assoc($res);
$FromMonth = $row['FromMonth'];
$ToMonth = $row['ToMonth'];


foreach( monthNames($FromMonth, $ToMonth) as $month){ echo $month,'<br>';
$sql = "SELECT MonthName, SUM(WorkingDays) AS WorkingDays FROM working_days WHERE Month_Name = '$month' ORDER BY MonthName";
$res = mysql_query($sql, $con);

$row = mysql_fetch_assoc($res);

$WorkingDays = $row['WorkingDays'];

echo "<td>$WorkingDays</td>";

}

but I got this error:

Apr

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\Program Files\xampp\htdocs\MES PROJECT\HalfShellParam.php on line 147
May

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\Program Files\xampp\htdocs\MES PROJECT\HalfShellParam.php on line 147
Jun

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:\Program Files\xampp\htdocs\MES PROJECT\HalfShellParam.php on line 147

Thank you

That means your query failed, check the query on line 144. (try echoing mysql_error())