SitePoint Sponsor

User Tag List

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

    SUM values inside foreach and while loop

    Hi..
    I have table which has a data so_month:
    FromMonth : 5
    ToMonth : 7
    and I have table working_days:
    MonthName
    May
    Jun
    Jul
    MonthNumber
    05
    06
    07
    WorkingDays
    23
    24
    23
    Now I have function to get the 3 consecutive months from FromMonth to ToMonth , which as you can see from May to Jul
    Now I have problem in getting the SUM of Working days.
    here is my code:
    PHP Code:
    <?php
      $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);
    while(
    $row mysql_fetch_array($res)){
    $WorkingDays $row['WorkingDays'];
    }
    echo 
    $WorkingDays;
    }
    ?>
    the output of this code is:
    232423
    and when I change this line:
    $WorkingDays = $row['WorkingDays'];
    to
    $WorkingDays += $row['WorkingDays'];
    the output is:
    234770
    The correct output should be: 70
    Any help is highly appreciated.
    Thank you very much..

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,028
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    When the second query is run direct against the database, is the correct value that you're expecting being returned?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Move your echo $workingDays; outside of the foreach loop and you will only get 70

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    looks like you're trying to do a loop to calculate a total... why aren't you doing a join?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi..

    I resolved it now using thid code:

    PHP Code:
    //-----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;

    }

    Thank you


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
  •