mfandel Posted October 26, 2014 Share Posted October 26, 2014 (edited) 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;?> <?php echo _guest_summary;?> The full code that pulls in the data and then sums it up in total: <!-- Begin reservation table data --> <br/> <table class="global resv-table-small" cellpadding="0" cellspacing="0"> <tbody> <tr> <?php echo "<td class='noprint'> </td>"; echo "<td>Time</td>"; echo "<td>Guests/Type</td>"; echo "<td>Name</td>"; echo "<td>Special Instructions/Notes</td>"; echo "<td class='noprint'>Table</td>"; echo "<td class='noprint'>Status</td>"; echo "<td class='noprint'>Created</td>"; echo "<td class='noprint'>Details/Delete</td>"; echo "</tr>"; // 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;'> </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> <span class='noprint'>"; printType($row->reservation_hotelguest_yn); //echo "<img src='images/icons/user-silhouette.png' class='middle'/>"; echo "</span></td><td style='width:10%' id='tb_name'><span class='noprint'>".printTitle($row->reservation_title)."</span><strong> <a id='detlbuttontrigger' href='ajax/guest_detail.php?id=".$id."'"; // color guest name if tautologous if($tautologous>1){echo" class='tautologous tipsy' title='"._tautologous_booking."'";} echo ">".$row->reservation_guest_name."</a></strong>"; // old reservations symbol if( (strtotime($row->reservation_timestamp) + $general['old_days']*86400) <= time() ){ echo "<img src='images/icons/clock-bolt.png' class='help tipsyold middle smicon' title='"._sentence_11."' />"; } // recurring symbol if ($row->repeat_id !=0) { echo " <img src='images/icons/loop-alt.png' alt='"._recurring. "' title='"._recurring."' class='tipsy' border='0' >"; } echo"</td><td style='width:10%' id='tb_note'>"; if ($_SESSION['page'] == 1) { echo $row->outlet_name; }else{ echo $row->reservation_notes; } echo "</td>"; if($_SESSION['wait'] == 0){ echo "<td class='big tb_nr' style='width:85px;' id='tb_table'><img src='images/icons/table_II.png' class='tipsy leftside noprint' title='"._table."' /><div id='reservation_table-".$id."' class='inlineedit'>".$row->reservation_table."</div></td>"; } echo "<td class='noprint'><div>"; getStatusList($id, $row->reservation_status); echo "</div></td>"; echo "<td class='noprint'>"; echo "<small>".$row->reservation_booker_name." | ".humanize($row->reservation_timestamp)."</small>"; echo "</td>"; echo "<td class='noprint'>"; // MOVE BUTTON // echo "<a href=''><img src='images/icons/arrow.png' alt='move' class='help' title='"._move_reservation_to."'/></a>"; // WAITLIST ALLOW BUTTON if($_SESSION['wait'] == 1){ $leftspace = leftSpace(substr($row->reservation_time,0,5), $availability); if($leftspace >= $row->reservation_pax && $_SESSION['outlet_max_tables']-$tbl_availability[substr($row->reservation_time,0,5)] >= 1){ echo" <a href='#' name='".$id."' class='alwbtn'><img src='images/icons/check-alt.png' name='".$id."' alt='"._allow."' class='help' title='"._allow."'/></a> "; } } // EDIT/DETAIL BUTTON echo "<a href='?p=102&resID=".$id."'><img src='images/icons/pen-fill.png' alt='"._detail."' class='help' title='"._detail."'/></a> "; // DELETE BUTTON if ( current_user_can( 'Reservation-Delete' ) && $q!=3 ){ echo"<a href='#modalsecurity' name='".$row->repeat_id."' id='".$id."' class='delbtn'> <img src='images/icons/delete.png' alt='"._cancelled."' class='help' title='"._delete."'/></a>"; } echo"</td></tr>"; $tablesum ++; $guestsum += $row->reservation_pax; } } ?> </tbody> <tfoot> <tr style="border:1px #000;"> <td class=" noprint"></td><td></td> <td colspan="2" class="bold"><?php echo $guestsum;?> <?php echo _guest_summary;?></td> <td></td> <td colspan="2" class="bold"><?php echo $tablesum;?> <?php echo _tables_summary;?></td> <?php if($_SESSION['wait'] == 0){ //echo "<td></td>"; } ?> </tr> </tfoot> </table> <!-- End reservation table data --> Edited October 26, 2014 by mfandel Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/ Share on other sites More sharing options...
mfandel Posted October 26, 2014 Author Share Posted October 26, 2014 after posting... I guess this might be a little vague. I apologize as I am not very familiar or savvy 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. It seems fairly basic, but beyond my grasp and hoping someone can shed some light. THanks! Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494784 Share on other sites More sharing options...
MDCode Posted October 26, 2014 Share Posted October 26, 2014 I didn't exactly get what you wanted after your second post. The first post sounded like you wanted guest and table count based on time, and your second post sounds like you want the total of `reservation_pax` based on `reservation_time`. You could probably just gather that through SQL: SELECT SUM(`reservation_pax`) FROM `table_name` WHERE `reservation_time` = '8:00' Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494823 Share on other sites More sharing options...
mfandel Posted October 26, 2014 Author Share Posted October 26, 2014 Sorry for any confusion. I thought or meant them to be the same. I was just trying to clarify the table headings in mysql. Guest count is related to reservation_pax and time of reservation is reservation_time. Thanks for the input... I hate to be completely stupid and have been trying to research how I would add your suggestion to my php file. I know very little about php and mysql. Every way I have tried to add the suggestion which looks like what I need I get parse errors. So unsure how to implement. In my research I also tried this which gives me a parse error, but thought maybe this would work form your post and research. $guestsum2 += $row->reservation_pax WHERE reservation_time = '8:00'; Parse error: syntax error, unexpected '`', expecting identifier (T_STRING) or variable (T_VARIABLE) or '{' or '$' in – Trying to research the error but haven't got much farther than how I am coding or including is not correct. I didn't exactly get what you wanted after your second post. The first post sounded like you wanted guest and table count based on time, and your second post sounds like you want the total of `reservation_pax` based on `reservation_time`. You could probably just gather that through SQL: SELECT SUM(`reservation_pax`) FROM `table_name` WHERE `reservation_time` = '8:00' Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494850 Share on other sites More sharing options...
MDCode Posted October 26, 2014 Share Posted October 26, 2014 The code I posted is SQL, you will have to find the code that is getting your information from the database. Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494855 Share on other sites More sharing options...
mfandel Posted October 27, 2014 Author Share Posted October 27, 2014 I am having trouble getting the data to display: Here is what I have so far, but not working or displaying anything: <?php $query = 'SELECT SUM(`reservation_pax`) FROM `reservations` WHERE `reservation_time` = 8:00' ; $result = mysql_query($query); while($row = mysql_fetch_assoc($result)) { echo $row['SUM(reservation_pax)']; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494860 Share on other sites More sharing options...
mfandel Posted October 27, 2014 Author Share Posted October 27, 2014 ok... thanks for the help... with a little more research I am getting close. I 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. Here is what I got working from what you shared.... Just need help to narrow by session page is already creating for specific date being viewed. <?php $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)']; } ?> <?php echo _guest_summary;?></td> <td></td> <?php if($_SESSION['wait'] == 0){ //echo "<td></td>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494867 Share on other sites More sharing options...
MDCode Posted October 27, 2014 Share Posted October 27, 2014 (edited) I'm not sure how your date is set up in the database but you can just add on to your WHERE clause like this: $result = mysql_query("SELECT SUM(`reservation_pax`) FROM `reservations` WHERE `reservation_time` = '8:00' AND `date` = 'Enter some date here' "); That will get the sum of guests where the reservation time was at 8:00 and the date was whatever you enter. Edited October 27, 2014 by SocialCloud Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494878 Share on other sites More sharing options...
mfandel Posted October 27, 2014 Author Share Posted October 27, 2014 That worked great! So what I have working is below... my final question is how to pass a variable for the selected date rather than having to enter the date manually. $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)']; } ?> When I try to use the $session information I get a syntax error: $_SESSION['selectedDate'] $result = mysql_query("SELECT SUM(reservation_pax) FROM reservations WHERE reservation_time = '8:00:00' AND reservation_date = $_SESSION['selectedDate'] AND reservation_hidden ='0'") ; I don't know if relevant, but I have to drop the single quotes around the table names to get it to not give me an error. Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494930 Share on other sites More sharing options...
MDCode Posted October 27, 2014 Share Posted October 27, 2014 What I put in the query are not single quotes. They are backticks. Backticks are used in queries to distinguish table names/column names Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494934 Share on other sites More sharing options...
mfandel Posted October 27, 2014 Author Share Posted October 27, 2014 thanks for the clarification. WHat would be the correct way to replace the 2014-10-24 with the $_SESSION['selectedDate'] Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494938 Share on other sites More sharing options...
MDCode Posted October 27, 2014 Share Posted October 27, 2014 The backticks should not be causing that error. Try this: SELECT SUM(`reservation_pax`) FROM `reservations` WHERE `reservation_time` = '8:00:00' AND `reservation_date` = '{$_SESSION['selectedDate']}' AND `reservation_hidden` ='0' I posted that on my server and no syntax errors occurred. Just know that trusting the integrity sessions is bad. You should properly escape that as well. Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494956 Share on other sites More sharing options...
mfandel Posted October 27, 2014 Author Share Posted October 27, 2014 Thank the syntax was perfect...no errors. Though it doesn't give me the date dynamically. I guess my poor hunch and research about sessions gave me the impression that if I copied that $_SESSION['selectedDate'] it would dynamically give me the the data for the date automatically as it was used early in the file, but no luck. I am going to have to do some research and dig into the original code to see how to setup using the selected date. It is a big deal breaker that I didn't consider... It works fine as long as I don't navigate to another date. If I do it shows me the counts for the manual date I entered on all dates.... big oops, so I have to figure out how to make it dynamic based on date selected. So I have to figure out a way to get it to read the date the user selected when page loaded. If you can point me in any direction or place to read up on attempting this let me know or even better if in the original code above you know or can see anything I can call that would give me the session. Thanks for all the help I am a lot farther along for sure. Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494980 Share on other sites More sharing options...
mfandel Posted October 28, 2014 Author Share Posted October 28, 2014 Hey.... thanks for all your help. It really pointed me in the right direction.... I finally found the correct session variable and it works like a charm. $result = mysql_query("SELECT SUM(reservation_pax) FROM reservations WHERE reservation_time = '8:00:00' AND reservation_date = '{$_SESSION['selectedDate']}' 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)']; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/292066-sum-of-row-based-on-specific-time-field-rather-than-just-total/#findComment-1494989 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.