newphpcoder Posted April 27, 2012 Share Posted April 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: <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 Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/ Share on other sites More sharing options...
teynon Posted April 27, 2012 Share Posted April 27, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1340926 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 I tried your suggested and still same output. Thank you Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1340929 Share on other sites More sharing options...
teynon Posted April 27, 2012 Share Posted April 27, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1340932 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1340933 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1340935 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1341004 Share on other sites More sharing options...
newphpcoder Posted April 27, 2012 Author Share Posted April 27, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/261675-problem-in-using-foreach-and-while-loop-in-displaying-data-on-table-format/#findComment-1341005 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.