Jump to content

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


mfandel

Recommended Posts

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 by mfandel
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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)'];
}
?>
Link to comment
Share on other sites

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>";
			}
			?>
Link to comment
Share on other sites

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 by SocialCloud
Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)'];
}
   
?>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.