adding totals of returned columns

[lauralee]

The administrator is able to receive the reservations for the dinner date, but I haven't been able to figure out how to add the results of the columns for a subtotal, and then a grand total. Here is the code that returns the information from the database that customers have entered from a form. Now I need to add up the entree1, entree2, numberinparty, numberofmembers and number of guests for each timeslot returned. I use ORDER BY to get the results grouped together by timeslot, but when I try to add GROUP BY and get a SUM of the results, I get an error. Is GROUP BY the way to go? If so, what is the syntax in order to get the columns' sums?

<?php  // Request the text of all the reservations$result = @mysql_query("SELECT lastname, firstname, numberinparty, entree1, entree2, timeslot, numberofmembers, guestsFROM Reservations WHERE dinnerdate='August 1' ORDER BY timeslot, lastname");if (!$result) {  exit('<p>Error performing query: ' . mysql_error() . '</p>');}echo '<table class="list"><tr><th>TIME</th><th>FIRST NAME</th><th>LAST NAME</th><th>ENTREE #1</th><th>ENTREE #2</th><th># IN PARTY</th><th># OF MEMBERS</th><th># OF GUESTS</th></tr>';// Display the reservation list in a paragraphwhile ($row = mysql_fetch_array($result)) {echo '<tr><td>' . $row['timeslot'] . '</td><td>' . $row['firstname'] . '</td><td>' . $row['lastname'] . '</td><td>' . $row['entree1'] . '</td><td>'. $row['entree2'] . '</td><td>' . $row['numberinparty'] . '</td><td>' . $row['numberofmembers'] .'</td><td>' . $row['guests'] . '</td></tr>';    }    echo '</table>';?>

[justsomeguy]

If you're getting all of the rows anyway, you might as well just add it up as you go.

$grandtotal = 0;while ($row = mysql_fetch_array($result)){  $subtotal = $row['entree1'] + $row['entree2'] + $row['numberinparty'] + $row['numberofmembers'] + $row['guests'];  $grandtotal += $subtotal;  ...

[lauralee]

Sorry, I don't want to add all of them together, I want a subtotal of each of them for each timeslot. For instance, if is a party of 4 who have placed a reservation for 6:00 pm and they have preordered 3 entree1 and 1 entree2, then there is 6:00 pm reservation made by a party of 7 for example that have ordered 3 entree1 and 4 entree2, I need to show how many orders for entree1 and how many orders for entree2 and how many total people for the 6:00 pm reservations (as well as all of the timeslots). Then, a grand total for the entire evening; how many entree1 and entree2 ordered, and how many total people and how many were members and how many were guests. How can I get a subtotal of each item returned for each timeslot and then a grand total for each item for the whole night?

[justsomeguy]

You can use an array to keep track of each timeslot, so each element in the array will contain the subtotal for a certain time slot.

$totals = array();$total_entree1 = 0;$total_entree2 = 0;$total_people = 0;while ($row = mysql_fetch_array($result)){  $people = $row['numberinparty'] + $row['numberofmembers'] + $row['guests'];  if (!isset($totals[$row['timeslot']]))	$totals[$row['timeslot']] = array('entree1' => 0, 'entree2' => 0, 'people' => 0);  $totals[$row['timeslot']]['entree1'] += $row['entree1'];  $totals[$row['timeslot']]['entree2'] += $row['entree2'];  $totals[$row['timeslot']]['people'] += $people;  $total_entree1 += $row['entree1'];  $total_entree2 += $row['entree2'];  $total_people += $people;  ...}echo '<table>';echo '<tr><td>Timeslot</td><td>Entree 1</td><td>Entree 2</td><td>People</td><tr>';foreach ($totals as $timeslot => $total){  echo '<tr>';  echo '<td>' . $timeslot . '</td>';  echo '<td>' . $total['entree1'] . '</td>';  echo '<td>' . $total['entree2'] . '</td>';  echo '<td>' . $total['people'] . '</td>';  echo '</tr>';}echo '<tr>';echo '<td><b>Totals</td>';echo '<td><b>' . $total_entree1 . '</b></td>';echo '<td><b>' . $total_entree2 . '</b></td>';echo '<td><b>' . $total_people . '</b></td>';echo '</tr>';echo '</table>';

You might want to reformat the output, but that's the general idea. That code is getting the grand total for the entire data set, so if you're looking for the grand total for 1 day then the query will just need to get results for one day (which it looks like it already is).You can do more queries to get this other information, but if you're getting everything anyway you might as well just use the existing data and save the database server a little work.

[lauralee]

WOW! Thanks so much for working out the code for me. I had a general (very vague) idea what was needed, but didn't know how to work it out. I really appreciate your help! I'll copy your suggested code and play with it until I really understand how it works, then I'll implement it in my web page.Thanks, again.

[lauralee]

Oops! When I plugged in the new code, I received a 500 internal server error. Was there something else I need to do??

[lauralee]

Also, I've never seen a += sign used together before. what is it for?

[justsomeguy]

That code isn't going to work standalone, that will need to be added to your existing code. Your code already has a while loop that goes through the result set. The new code that I posted before the while loop goes before your while loop, the code inside the loop goes at the start of your loop, and the code after the loop goes after your loop, but you still need the code you started with. Also, you might want to use a browser other than IE to develop with, other browsers will show the actual error message from PHP instead of the generic 500.

[lauralee]

Hello.I am self-taught using the W3C online tutorials, and sitepoint books. I have created several static HTML websites using HTML and CSS and am now developing the "database driven" website on which I have needed help. I am very new to PHP but am enjoying learning it. It helps to copy something, then see how it works to understand it and then be able to manipulate it to do what I want. I have been looking over the code you gave me, but haven't seen any error, so don't know why it isn't working. I'll continue working on it, but if you have any suggestions as to where I should look, please let me know.Thanks.

[lauralee]

Just read your reply. Will follow your suggestions. I'll change to FireFox. I usually work in one and then check with the other.

[justsomeguy]

I put the "..." in the while loop, that's where the existing code that you have in your while loop goes, with the code that you already have before and after your while loop going before and after the updated while loop (this code is meant as a change to your code, not new code).Combining a math operator with = assigns the result to the same variable. So these are equivalent:$var = $var + 5;$var += 5;

[lauralee]

Thanks again for the info. I thought I had placed the new code in where it needed to go, along with the existing code that I had already done. But, that must be where the error is. I will work on it and see if I can figure out what to do. I'll let you know if I got it working. If not, I'll be asking for help again. Thanks for your patience!

[justsomeguy]

If you need help, post the code you're working with and we'll get it cleaned up.

[lauralee]

Thanks. I'll be out of touch for a few days. Will check back late Sunday, Sep 28 with progress.

[lauralee]

Thanks for the help! I finally worked it out. I had to take it one step at a time until I understood how it would add the subtotals, then the totals. Kudos to JustSomeGuy!!