sum of $row based on specific time field rather than just total

Question

This code works just fine to display a list of reservations for a given day and sums the guest and table counts just fine for the entire day. Sums all reservations returned.

What I am trying to do or figure out is a way to get totals based on specific time intervals. For example how many guests and tables at 8:00, 9:00, 10:00, etc....

I can see where the sums are calculated, but need help adding a way to add a variable to look at the reservation_time and sum by hour rather than just daily total.

        $tablesum ++;
        $guestsum += $row->reservation_pax;
<?php echo $guestsum;?>&nbsp;&nbsp;<?php echo _guest_summary;?>

The full code that pulls in the data and then sums it up in total:

<?php
    // Clear reservation variable
    $reservations ='';

    if ($_SESSION['page'] == 1) {
        $reservations = querySQL('all_reservations');
    }else{
        $reservations = querySQL('reservations');
    }

    // reset total counters
    $tablesum = 0;
    $guestsum = 0;

    if ($reservations) {

        //start printing out reservation grid
        foreach($reservations as $row) {
            // reservation ID
            $id = $row->reservation_id;
            $_SESSION['reservation_guest_name'] = $row->reservation_guest_name;
            // check if reservation is tautologous
            $tautologous = querySQL('tautologous');

        echo "<tr id='res-".$id."'>";
        echo "<td";
        // daylight coloring
        if ($row->reservation_time > $daylight_evening){
            echo " class='evening noprint'";
        }else if ($row->reservation_time > $daylight_noon){
            echo " class='afternoon noprint'";
        }else if ($row->reservation_time < $daylight_noon){
            echo " class='morning noprint'";
        }

        echo " style='width:10px !important; padding:0px;'>&nbsp;</td>";
        echo "<td id='tb_time'";
        // reservation after maitre message
        if ($row->reservation_timestamp > $maitre['maitre_timestamp'] && $maitre['maitre_comment_day']!='') {
            echo " class='tautologous' title='"._sentence_13."' ";
        }
        echo ">";
        echo "<strong>".formatTime($row->reservation_time,$general['timeformat'])."</strong></td>";
        echo "<td id='tb_pax'><strong class='big'>".$row->reservation_pax."</strong>&nbsp;&nbsp;<span class='noprint'>";
        $tablesum ++;
        $guestsum += $row->reservation_pax;
        }
    }
    ?>

    <?php echo $guestsum;?>&nbsp;&nbsp;<?php echo _guest_summary;?>

Ok I am getting close: was able to get it to total and display... though I need to narrow by one more parameter. That is giving me the total guest count (reservation_pax) for all reservations. I need to do it by the given date or session. The page shows only reservation for the date selected, but script counts all days with reservations at 8:00.

Just need help to narrow by session page is already creating for specific date being viewed.

$result = mysql_query("SELECT SUM(reservation_pax) FROM reservations WHERE reservation_time = '8:00'"); 
if($result === FALSE) {
die(mysql_error()); // TODO: better error handling
}

while($row = mysql_fetch_array($result))
{

   echo $row['SUM(reservation_pax)'];
}

Since I got some feedback that this was vague and not vary familiar with php. I just need to be able to count guest based on of the column reservation_time. SO I guess to clarify what I am trying to do is add the total in reservation_pax for or based on a specific reservation_time in another table row. I have read several items, posts and php tutorials, but can't seem to get them applied in this file or to work. — ToddG, Oct 26 '14 at 09:06
Doing some more research... could I do something like this? ` SELECT reservation_time, sum( reservation_pax ) as total_mark FROM`reservations` GROUP BY reservation_time ` I tried but can't seem to figure out how to include in the php file. — ToddG, Oct 26 '14 at 09:40

score 0 · Answer 1 · answered Oct 26 '14 at 08:18

0

You need to generate series with time interval, assuming you have some table with more then 15 rows (I called it lookup_table) you can do this with uservars.

SELECT d.starttime, d.endtime, COUNT(r.id) AS total
FROM
    (
     SELECT
        @starttime := @starttime + INTERVAL 1 HOUR starttime,
        @endtime := @endtime + INTERVAL 1 HOUR endtime
     FROM
        lookup_table
     CROSS JOIN (SELECT @starttime := CURRENT_DATE + INTERVAL 7 HOUR,
                        @endtime := CURRENT_DATE + INTERVAL 8 HOUR - INTERVAL 1 SECOND) v
     LIMIT 15
    ) d
LEFT JOIN
    reservations r
    ON r.reservation_time BETWEEN d.starttime AND d.endtime
GROUP BY d.starttime, d.endtime

If you need to filter out some data from reservations, you need to add that to the join condition.

This is based on the information you provided (which isn't much).

answered Oct 26 '14 at 08:18

Gervs

1,397
9
8

Thanks for the response I think this is much more savvy than I need. As I don't know much about php I am reading a lot. I vaguely understand that currently the script is adding up all the number of guests in the table for that day and giving me the total guest. What i want to do is have individual totals for each available slot (time). So I don' need intervals per say or maybe clueless... I just need to be able to count guest based on of the available reservation slots being in this case 8:00, 8:10, 8:20 and so on. I am trying to add a total in reservation_pax for a specific reservation_time – ToddG Oct 26 '14 at 08:57
Can you provide the DDL for the table (with `SHOW CREATE TABLE`) with some sample data and the required output. What the subquery aliased with d does is generating time slots (`2014-10-26 08:00:00 | 2014-10-26 08:59:59 ... 2014-10-26 22:00:00 | 2014-10-26 22:59:59`). Then the reservation table is joined on reservation_time between that interval, assuming reservation_time is datetime datatype – Gervs Oct 26 '14 at 10:59
Not sure how to post this as comment so will try to make it readable by adding a few comments...apologies if not correct way to do this. reservation_id | reservation_date | reservation_time | reservation_pax – ToddG Oct 26 '14 at 22:26
1 | 7/31/2014 | 8:00:00 | 1 .... 2 | 7/31/2014 | 8:00:00 | 4 ..... 19 | 7/30/2014 | 8:10:00 | 3..... 20 |7/30/2014 | 8:10:00 | 2 – ToddG Oct 26 '14 at 22:28
could I do something simple like this? $guestsum2 += $row->`reservation_pax` WHERE `reservation_time` = '8:00'; After doing some reasearch it looks simple, but I can't get the condition to work...it throws error, so something wrong in the way I am coding. Parse error: syntax error, unexpected '`', expecting identifier (T_STRING) or variable (T_VARIABLE) or '{' or '$' in – ToddG Oct 26 '14 at 23:09
– ToddG Oct 27 '14 at 00:00

score 0 · Accepted Answer · answered Oct 27 '14 at 23:10

$result = mysql_query("SELECT SUM(reservation_pax) FROM reservations WHERE reservation_time = '8:00:00' AND reservation_date = '2014-10-27' AND reservation_hidden ='0'") ; 
if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}

while($row = mysql_fetch_array($result))
{
   echo $row['SUM(reservation_pax)'];
}

?>

sum of $row based on specific time field rather than just total

2 Answers2