CODEWITHSUNDEEP
phpmysqlhtml
anthropaulogy
anthropaulogy

Reputation: 57

MySQL & PHP: summing up data from a table

Okay guys, this probably has an easy answer but has been stumping me for a few hours now.

I am using PHP/HTML to generate a table from a MySQL Table. In the MySQL table (TimeRecords) I have a StartTime and EndTime column. In my SELECT statement I am subtracting the EndTime from the StartTime and aliasing that as TotalHours. Here is my query thus far:

$query = "SELECT *,((EndTime - StartTime)/3600) AS TotalPeriodHours
    FROM TimeRecords
    WHERE Date
    BETWEEN '{$CurrentYear}-{$CurrentMonth}-1'
    AND '{$CurrentYear}-{$CurrentMonth}-31'
    ORDER BY Date
    ";

I then loop that through an HTML table. So far so good. What I would like to do is to add up all of the TotalHours and put that into a separate DIV. Any ideas on 1) how to write the select statement and 2) where to call that code from the PHP/HTML?

Thanks in advance!

Upvotes: 1

Views: 495

Answers (3)

liquorvicar
liquorvicar

Reputation: 6106

You can do this in the same query if you have a unique id using GROUP BY WITH ROLLUP

$query = "
    SELECT unique_id,SUM((EndTime - StartTime)/3600) AS TotalPeriodHours
    FROM TimeRecords
    WHERE Date BETWEEN '{$CurrentYear}-{$CurrentMonth}-1'
      AND '{$CurrentYear}-{$CurrentMonth}-31'
    GROUP BY unique_id WITH ROLLUP
    ORDER BY Date
";

In this instance the last result from your query with contain NULL and the overall total. If you don't have a unique ID you will need to do it in PHP as per Naveen's answer.

A few comments on your code:

  • Using SELECT * is not considered good practice. SELECT the columns you need.

  • Not all months have a day 31 so this may produce unexpected results. If you're using PHP5.3+, you can use

    $date = new DateTime(); $endDate = $date->format( 'Y-m-t' );

The "t" flag here gets the last day of that month. See PHP docs for more on DateTime.

Upvotes: 0

Naveen Kumar
Naveen Kumar

Reputation: 4601

Try this 

$query= "
    SELECT ((EndTime - StartTime)/3600) AS Hours, otherFields, ...
    FROM TimeRecords
    WHERE 
        Date BETWEEN '{$CurrentYear} - {$CurrentMonth} - 1'
        AND '{$CurrentYear}-{$CurrentMonth} - 31' ";

$records =mysql_query($query);
$sum= 0;
while($row=mysql_fetch_array($records))
{
      echo"$row['otherFields']";
      echo"$row['Hours']";
     $sum+=$row['Hours'];
 } 

 echo" Total Hours : $sum ";

Upvotes: 3

zanlok
zanlok

Reputation: 1630

Just use a single query with a Sum(). You could also manually calculate it if you're already displaying all rows. (If paginating or using LIMIT, you'll need a separate query like below.)

$query = "
    SELECT Sum(((EndTime - StartTime)/3600)) AS SumTotalPeriodHours
    FROM TimeRecords
    WHERE 
        Date BETWEEN '{$CurrentYear} - {$CurrentMonth} - 1'
        AND '{$CurrentYear}-{$CurrentMonth} - 31'
";

Upvotes: 0

Related Questions