Jump to content

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

newphpcoder

By newphpcoder
in PHP Coding Help

Recommended Posts

newphpcoder Advanced Member

newphpcoder

Posted
Posted

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

post-101569-13482403466694_thumb.jpg

post-101569-13482403466746_thumb.jpg

teynon Member

teynon

Posted
Posted 

foreach($TotalKg AS $TotalKg){

 

You are replacing the array variable with the element variable.

 

Do something like this:

foreach($TotalKg AS $value) echo "<th>{$value}</th>";

 

You have multiple occurances of this issue.

 

teynon Member

teynon

Posted
Posted

 

  foreach($Comp AS $Comp){ is another occurance of this.

 

Also, your query: 

SELECT SUM(TotalKg) AS TotalKg FROM sales_order  WHERE Comp = '$Comp' ORDER BY Comp

 

This query can only select one result because SUM will group them. See this page: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

 

If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.
newphpcoder Advanced Member

newphpcoder

Posted
  • Author
Posted

I tried your suggested :

 

<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> </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 $Total){
    echo "<tr><td>$Total</td>
    </tr>";
  }   

   }
   ?>

 

And now I attach the output:

 

Thank you so much for your effort to help me.

post-101569-1348240346679_thumb.jpg

newphpcoder Advanced Member

newphpcoder

Posted
  • Author
Posted

I revise again 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> </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 $Total){
    echo "<td>$Total</td>
    </tr>";
  }    
}

   ?>

 

because I duplicate the tr so i remove it.

 

I will attach agin the output still wrong :(

post-101569-13482403466892_thumb.jpg

newphpcoder Advanced Member

newphpcoder

Posted
  • Author
Posted

I tried this:

 

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

 

And it works.

 

newphpcoder Advanced Member

newphpcoder

Posted
  • Author
Posted

Now I need to add 3 Months Name below working days.

 

 

 

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 "<tr><th> </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>Working Days</td></tr>";
   
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 "<tr>$month</tr>";// this part....
}

?>

 

 

 

I attach my sample forms and the result of m code.

 

 

 

Thank you again

post-101569-13482403467905_thumb.jpg

post-101569-13482403468016_thumb.jpg

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.