/    Sign up×
Community /Pin to ProfileBookmark

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

Copy linkTweet thisAlerts:
Apr 27.2012

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:

[code=php]
<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>”;
}

?>
[/code]

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

Thank you

to post a comment
PHP

12 Comments(s) ↴

Copy linkTweet thisAlerts:
@jaimegmApr 27.2012 — 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.
Copy linkTweet thisAlerts:
@newphpcoderauthorApr 28.2012 — ok thank you..

I resolve it now using this code:

[code=php]
<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>";

?>
[/code]

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:


[code=php]
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>";

}
[/code]

but I got this error:

Apr

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:Program FilesxampphtdocsMES PROJECTHalfShellParam.php on line 147

May

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:Program FilesxampphtdocsMES PROJECTHalfShellParam.php on line 147

Jun

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in D:Program FilesxampphtdocsMES PROJECTHalfShellParam.php on line 147

Thank you
Copy linkTweet thisAlerts:
@jaimegmApr 28.2012 — 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
Copy linkTweet thisAlerts:
@newphpcoderauthorApr 28.2012 — I tried this for testing before I put on my forms:

[code=php]
<?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;
}



?>
[/code]

but the output is:

232423

Then when I try:

[code=php]
while($row = mysql_fetch_array($res)){

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

}
echo $WorkingDays;
}
[/code]

the output is:

234770

the correct output should be 70

Thank you
Copy linkTweet thisAlerts:
@jaimegmApr 28.2012 — 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
[code=php]
$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 />";

}
[/code]
That will give you a clue on what is going on
Copy linkTweet thisAlerts:
@newphpcoderauthorApr 28.2012 — I resolve the issue in working days.

Now I add row for Batch Per day

here is my code:

[code=php]
<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>";
?>

[/code]

this code:

[code=php]
 $BatchPerDay = @($CompKg / $DemandedPerday);

[/code]

i got bool: false

$DemandedPerday undefined null

But this part:

[code=php]
$DemandedPerDay = $TotalKg * $SumWorkingDays;
[/code]

has data

Thank you
Copy linkTweet thisAlerts:
@jaimegmApr 28.2012 — 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
Copy linkTweet thisAlerts:
@jaimegmApr 28.2012 — 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.
Copy linkTweet thisAlerts:
@newphpcoderauthorApr 29.2012 — okay...Thank you so much
Copy linkTweet thisAlerts:
@newphpcoderauthorApr 29.2012 — Thank you so much
Copy linkTweet thisAlerts:
@jaimegmApr 29.2012 — 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.
Copy linkTweet thisAlerts:
@newphpcoderauthorApr 29.2012 — Thank you so much ?
×

Success!

Help @newphpcoder spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.20,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...