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

**newphpcoder** · 04-26-2012 09:02 PM

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:

PHP 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

**jaimegm** · 04-27-2012 03:08 PM

Can you isolate your problem? I mean copy this code in a new page and get rid of all code that is not necessary there in such way that you can specify exactly your problem.

**newphpcoder** · 04-27-2012 07:59 PM

ok thank you..

I resolve it now using this code:

PHP 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:

PHP Code:


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

**jaimegm** · 04-27-2012 09:33 PM

Could you try using mysql_fetch_array instead of mysql_fetch_array
And WorkingDays is a string and it should be a number, that is why you are receiving those warnings

**newphpcoder** · 04-27-2012 09:38 PM

I tried this for testing before I put on my forms:

PHP Code:


<?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);



 $sql = "SELECT FromMonth, ToMonth FROM so_month";

$res = mysql_query($sql,$con);



$row = mysql_fetch_assoc($res);

$FromMonth = $row['FromMonth'];

$ToMonth = $row['ToMonth'];



function monthNames($from, $to){

   $range=array();

   for($i=$from; $i<=$to; $i++){

           $range[$i]=date('M', mktime(0,0,0,$i));

   }

    return $range;

}

 $month_ = implode("' ', ",monthNames($FromMonth,$ToMonth));

 //echo $month_;

 







foreach( monthNames($FromMonth, $ToMonth) as $month){ 

//$sql = "SELECT MonthName, SUM(WorkingDays) AS WorkingDays FROM working_days WHERE Month_Name IN ('.implode( ',' , $month_ ).') ORDER BY MonthName";



$sql = "SELECT MonthName, SUM(WorkingDays) AS WorkingDays FROM working_days WHERE MonthName IN ('$month') GROUP BY MonthName";

$res = mysql_query($sql, $con);

$SumWorkingDays = 0; 

while($row = mysql_fetch_array($res)){



$WorkingDays = $row['WorkingDays'];

$SumWorkingDays += $WorkingDays;



}

echo $SumWorkingDays;

}







?>

but the output is:

232423

Then when I try:

PHP Code:


while($row = mysql_fetch_array($res)){



$WorkingDays += $row['WorkingDays'];

//$SumWorkingDays += $WorkingDays;



}

echo $WorkingDays;

}

the output is:

234770

the correct output should be 70

Thank you

**jaimegm** · 04-27-2012 10:42 PM

Instead of just printing the result, now print row by row, with some additional information like MonthName, you will find the answer there I mean something like

PHP Code:


$sql = "SELECT MonthName, WorkingDays FROM working_days WHERE MonthName IN ('$month') GROUP BY MonthName";
$res = mysql_query($sql, $con);

while($row = mysql_fetch_array($res)){

 $WD += $row['WorkingDays']; 
echo "MonthName =".$row['MonthName']." WorkingDays=".$row['WorkingDays']." WD=".$row['WD']."  <br />";

}

That will give you a clue on what is going on

**newphpcoder** · 04-27-2012 11:07 PM

I resolve the issue in working days.

Now I add row for Batch Per day

here is my code:

PHP 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>";

//--List of Compounds----//  

   $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>";

   }  



//------Total Kg/Compound----//  

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



//-----Computation of Working Days---//

$sql = "SELECT FromMonth, ToMonth FROM so_month";

$res = mysql_query($sql,$con);



$row = mysql_fetch_assoc($res);

$FromMonth = $row['FromMonth'];

$ToMonth = $row['ToMonth'];



function monthNames($from, $to){

   $range=array();

   for($i=$from; $i<=$to; $i++){

           $range[$i]=date('M', mktime(0,0,0,$i));

   }

    return $range;

}

 $month_ = implode("' ', ",monthNames($FromMonth,$ToMonth));



foreach( monthNames($FromMonth, $ToMonth) as $month){ 

$sql = "SELECT MonthName, SUM(WorkingDays) AS WorkingDays FROM working_days WHERE MonthName IN ('$month') GROUP BY MonthName";

$res = mysql_query($sql, $con);

$SumWorkingDays = 0; 

while($row = mysql_fetch_array($res)){



 $WorkingDays+=(int)$row['WorkingDays'];

$SumWorkingDays += $WorkingDays;



}

}

//-------Demanded per day-----//

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

$DemandedPerDay = $TotalKg / $SumWorkingDays;



$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']; 

       $DemandedPerDay = $TotalKg * $SumWorkingDays;

       $DemandedPerDay = number_format($DemandedPerDay, 2);

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

   } 

} 

echo "</tr>";



//------Batched Per day-----//



echo "<tr><td>Batched per day</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 DISTINCT CompKg FROM sales_order  WHERE Comp = '$Compound' GROUP BY Comp ORDER BY Comp"; 

   

   $result = mysql_query($sql_sec, $con) ; 

   



   while( $row_sec = mysql_fetch_assoc( $result ) ) 

   { 

         $CompKg = $row_sec['CompKg']; 

         $DemandedPerDay = $TotalKg * $SumWorkingDays;

       $BatchPerDay = @($CompKg / $DemandedPerday);

       $BatchPerDay = number_format($BatchPerDay, 2);

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

   } 

}

echo "</tr>";

?>

this code:

PHP Code:


       $BatchPerDay = @($CompKg / $DemandedPerday);

i got bool: false
$DemandedPerday undefined null

But this part:

PHP Code:


$DemandedPerDay = $TotalKg * $SumWorkingDays;

has data

Thank you

**jaimegm** · 04-28-2012 12:23 AM

Firstly get rid of the @
Again check row by row, or where the error occurs print the data and check what information you have there, as a general rule, before dividing by something ALWAYS check that the divisor<>0

**jaimegm** · 04-28-2012 12:32 AM

and from your jpg I also noticed that your divisor is very very big in compared to your dividend and in such cases you are going to have a quotient<0.02 probably due to that you are only getting zeros.

**newphpcoder** · 04-29-2012 12:43 AM

okay...Thank you so much

**newphpcoder** · 04-29-2012 12:48 AM

Thank you so much

**jaimegm** · 04-29-2012 10:21 AM

I am glad to you you are done.

Here is my suggestion for future issues: Break the program until the point where you know it is working correctly and print out the results where it is not working and check the data, and start making changes from there.

**newphpcoder** · 04-29-2012 06:56 PM

Thank you so much

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

Thread Tools

Display

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions