SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Attached Images Attached Images

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Okay. Lets look at this.
    PHP Code:
       $sql "SELECT DISTINCT s.Comp FROM sales_order s, param_settings p WHERE s.Comp = p.Compounds ORDER BY s.Comp"
    There is no need for the param_settings table to be in this query, since you're not using it in any fashion. SELECT DISTINCT when used with a join that does nothing is redundant behavior.
    Additionally, we'll solve your need for multiple queries by using GROUP BY.
    PHP Code:
       $sql "SELECT Comp,SUM(TotalKg) AS total FROM sales_order GROUP BY Comp ORDER BY Comp"
    Now when you extract it, foreach it twice.

    The problem you're having with your foreach is this:

    foreach($TotalKg AS $TotalKg){

    You're using the same variable for your AS. Doing so will overwrite your array during the first execution.

    PHP Code:
       $sql "SELECT Comp,SUM(TotalKg) AS total FROM sales_order GROUP BY Comp ORDER BY Comp";
       
    $res_comp mysql_query($sql$con); 
       while(
    $row_comp mysql_fetch_assoc($res_comp)){
            
    $Comps[] = $row_comp['Comp'];
       }
       echo 
    "<th>&nbsp;</th>";
       foreach(
    $Comps AS $Comp){
        echo 
    "<th>$Comp</th>";
      }   

       echo 
    "</tr><tr>
       <td>Total Kg/Compound</td>"
    ;
       foreach(
    $Comps AS $TotalKg){
        echo 
    "<td>$TotalKg</td>
        </tr>"
    ;
      } 
    (PS: You didnt close your header row tag.)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    657
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    That means your query failed, check the query on line 144. (try echoing mysql_error())
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •