Jump to content

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


newphpcoder

Recommended Posts

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

Link to comment
Share on other sites

 

  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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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