Totalling values and grouping in an array

[Shaman13]

I am really hoping someone can help me figure this out. I have spent at least several weeks trying to figure out how to total values generated in an array. Unfortunately there does not appear to be any information out there. The scenario goes like this.... I have a MySQL table which records events by day and time spent. My array looks like this....

$num_rows = mysql_num_rows($result);
print "You currently have $num_rows time entries for "

?>

<font color="#FF0000"size="3">
<? echo $_SESSION['valid_user'];?> on
<b><?php echo $TIDATEA?></b><P>
</font>

<font face="Times New Roman" size="3">
<?php
print("<TABLE BORDER=1,width=725,body bgcolor=#FFFFFF><TR>");
print("<TH>Time Date</TH>");
print("<TH>Amount of Time</TH>");
print("<TH>Reason</TH>");
print("<TH>Case Number</TH>");
print("<TH>Funding</TH>");
print("<TH>Administrative</TH>");
print("<TH>Time Activity</TH>");
print("<TH>Advocate</<TH></TR>");

for ($index = 0; $index < 5; $index++)

while($row=mysql_fetch_array($result)) {
print '<tr><td>'.$row['TIDATE'].'</td><td>'.$row['TTIME']. '</td><td>'.$row['REASON'].'</td><td>'.$row['CASENUM']. '</td><td>'.$row['FUNDSNUM'].'</td><td>'.$row['NONCASE']. '</td><td>'.$row['TIMEACTIVITY'].'</td><td>'.$row['SNUM']. '</td><TD><a href="time_edit.php? TIMEID='.$row['TIMEID'].'">Edit Time</a></td></tr>';
}

?>
</table>
-------------------------------------------
Perhaps what I am trying to do is not possible or it is very likely I am way too new to this stuff to do it! I would like to generate the above array grouped by day i.e. TIDATE with totals for the time field TTIME for each day. Is it even possible. If not I would welcome any alternative ideas. In principle I am trying to allow the user to generate a report which displays their time recorded by date with totals for each day. Any and all suggestions are appreciated!

[edited by: jatar_k at 9:19 pm (utc) on Dec. 10, 2004]

[jatar_k]

would you mind pasting the actual query you are using ;)

[Shaman13]

Here is the query I am using which uses the persons id number SNUM from their session and allows them to select two dates to view the records between the two dates selected.

$result = mysql_query( "SELECT * FROM ttime WHERE SNUM = '".$_SESSION['SNUM']."' AND (TIDATE BETWEEN '%".$TIDATEA."%' AND '%".$TIDATEB."%') ORDER BY TIDATE ASC")

Thanks jatar!

[jatar_k]

try these for some info

SELECT Syntax [dev.mysql.com] looking specifically at GROUP BY
Functions and Modifiers for Use with GROUP BY Clauses [dev.mysql.com] tons of extra info

[Shaman13]

jatar_k If your out there Thanks for the Help! I made some significant progress using the information you pointed to in your post. Thanks Again!

I still have an unresolved issue with my results though. I am hoping someone can help me. My query pulls and displays my results with totals for my groups but only displays the first record in each group and the total below. If I try grouping by reason it displays all the records but then displays a total under each record. What I would like to achieve is a display of a list of records with a total for the day 'TIDATE'. For example, if the user entered four records of 1.0 hours for a day. In this situation I want the array to display all four records with the amount of time for each with the reason, and a total for the day underneath. Here is the query and array I am currently using. Any and all suggestions are really appreciated!
--------------------------------
$result = mysql_query( "SELECT TIDATE,SNUM,FUNDSNUM,REASON,CASEACTIVITY,TIMEACTIVITY, NONCASE,CASENUM,TIMEID,TTIME,SUM(TTIME) FROM ttime WHERE SNUM = '".$_SESSION['SNUM']."' AND (TIDATE BETWEEN '%".$TIDATEA."%' AND '%".$TIDATEB."%') GROUP BY TIDATE,FUNDSNUM ")

or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "You currently have $num_rows time entries for "

?>

<font color="#FF0000"size="3">
<? echo $_SESSION['valid_user'];?> on
<b><?php echo $TIDATEA?></b><P>
</font>

<font face="Times New Roman" size="3">
<?php
print("<TABLE BORDER=1,width=725,body bgcolor=#FFFFFF><TR>");
print("<TH>Time Date</TH>");
print("<TH>Amount of Time</TH>");
print("<TH>Reason</TH>");
print("<TH>Case Number</TH>");
print("<TH>Funding</TH>");
print("<TH>Administrative</TH>");
print("<TH>Time Activity</TH>");
print("<TH>Advocate</<TH></TR>");

for ($index = 0; $index < 5; $index++)

while($row=mysql_fetch_array($result)) {
print '<tr><td>'.$row['TIDATE'].'</td><td>'.$row['TTIME']. '</td><td>'.$row['REASON'].'</td><td>'.$row['CASENUM']. '</td><td>'.$row['FUNDSNUM'].'</td><td>'.$row['NONCASE']. '</td><td>'.$row['TIMEACTIVITY'].'</td><td>'.$row['SNUM']. '</td><TD><a href="time_edit.php? TIMEID='.$row['TIMEID'].'">Edit Time</a></td></tr>';

print '<tr><td>Total '.$row['SUM(TTIME)'].'</td></tr>';
}

?>
</table>

[edited by: jatar_k at 9:20 pm (utc) on Dec. 10, 2004]
[edit reason] fixed sidscroll [/edit]