Total of a column with request GROUP BY

PHP
1

Hello,
I have a database which calculates of time. My PHP page works well with this request which uses group by but I would like to add a cell, at the bottom of the table with the total of the column HH:MM. I have at the moment no idea. Could you give me an idea ?. Thank you.

<?php


  $liendb = mysql_connect($bddserver, $bddlogin, $bddpassword);
  mysql_select_db ($bdd);
  ?>


<div class="note">
      <p class="title">Note</p>

      <p class="title">Cette page affiche la ventilation des heures par activit&eacute; entre deux dates. La requ&ecirc;te tient compte du type d'activit&eacute; ou sous-cat&eacute;gorie.</p>
</div>


<div id = "recherche">
    <form action="ventilation_categories.php" method="post">
        <img src="calendrier.jpg" width="90" height="90" alt="Calendrier" title="Saisir deux dates" border="0" /> entre
        <input name="somme_PA_1" value="<?php echo $somme_PA_1; ?>" size="10" id="f_date_c" /><button type="button" id="f_trigger_c">...</button>
        et
        <input name="somme_PA_2" value="<?php echo $somme_PA_2; ?>" size="10" id="f_date_d" /><button type="button" id="f_trigger_d">...</button>
        <input type="submit" value="Valider">
    </form>
</div>
<table id="result">
<tr>
    <th>Activit&eacute;s</th>
    <th>Minutes</th>
    <th>Centi&egrave;mes</th>
    <th>HH:MM</th>
</tr>
<?php
 
  if (isset($somme_PA_1));
  if (isset($somme_PA_2));

  {
   $clause = " AND (gtd.date BETWEEN '$somme_PA_1' AND '$somme_PA_2') GROUP BY(activites) ";
  }

$sql = "SELECT CONCAT(activite,' ', categorie) AS 'activites', SUM(duree) as 'min', ROUND(SUM(duree / 60), 2) as 'duree', SUM(duree) as 'minutes' FROM gtd, type WHERE gtd.id_type = type.id "  . $clause;
$resultat = mysql_query ($sql);

while ($eleve = mysql_fetch_array ($resultat))
{
$activite = $eleve['activites'];
$min = $eleve['min'];
$duree = $eleve['duree'];
$minutes = str_pad ( floor ( $eleve[ 'minutes' ] / 60 ), 2, 0, STR_PAD_LEFT );
$minutes .= ':';
$minutes .= str_pad ( $eleve[ 'minutes' ] % 60, 2, 0, STR_PAD_LEFT );


echo "<tr>";
echo "<td>$activite</td>";
echo "<td>$min</td>";
echo "<td>$duree</td>";
echo "<td>$minutes</td>";
echo "<tr>";
}
echo "</table>";

// i tried this but it is the total of the database not the query...

$sql = "SELECT SUM(minutes) AS 'total' FROM gtd";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo $row['total'];

?>

A screenshot…
ImageShack® - Online Photo and Video Hosting

2

Well, you if your minutes is just minutes (i.e., just a number like “5”, “10”, etc.), create a variable outside of your while loop called $sum or something. In your loop, add the value of the minutes to that.

Then, after the while loop, before you close your table, you can use this to get your HH:MM format:


echo floor($sum/60):$sum%60;

That’ll give you hours and minutes, without the trailing zero. To add the trailing zeros, do something like:


$hour = floor($sum/60);
$minute = floor($sum%60);

if($hour < 10)
  $hour = "0" . $hour;
if($minute < 10)
  $minute = "0" . $minute;

(Sorry this took so long, I forgot to submit it like 2 hours ago =p).

3

I’m not a specialist but it’s the same thing that :

$minutes = str_pad ( floor ( $eleve[ 'minutes' ] / 60 ), 2, 0, STR_PAD_LEFT );
$minutes .= ':';
$minutes .= str_pad ( $eleve[ 'minutes' ] % 60, 2, 0, STR_PAD_LEFT );

OK. It is rather for the code which gives me the total of the column which I do not know how to make the “loop while”… I’m just a trainer in multimedia and I’m not a programmer… I just need to resolve this only issue. Tranks

4

Yes, your padding will work as well.

All you need to do in the while is do something like:
$total_minutes += $eleve[‘minutes’];

Then outside, use $total_minutes in place of $eleve[‘minutes’].