CODEWITHSUNDEEP
phpmysql
bash3r
bash3r

Reputation: 135

PHP/MySQL - Calculate Total for each Reservation

I've got a Reservation application, and I'd like to calculate the total number of meals needed by reservations date span.

My database has fields:

$name - Person reserving
$chkin - Check in date (DATE yyyy-mm-dd)
$chkout - Check out date (DATE yyyy-mm-dd)
$guests - Number of people in group
$meal - Eating meals (Yes/No)

So for each reservation I have:

// Days of Stay
$days = (strtotime($chkout) - strtotime($chkin)) / (60 * 60 * 24);

What I'm not sure of is how to do the calculation for each reservation in the database.

My calculation for each reservation would be something like:

$days * $guests

I would appreciate advice on this query... I'm trying to give a snapshot of how many meals will be need to be prepared for a given month, weeekend, etc.

Thank you!

Upvotes: 0

Views: 512

Answers (4)

Kickstart
Kickstart

Reputation: 21523

Something like this:-

SELECT SUM(guests * DATEDIFF(IF(chkin < $start_range, $start_range, chkin), IF(chkout > $end_range, $end_range, chkout)))
FROM sometable
WHERE chkin < $end_range
AND chkout > $start_range
AND meal = 'Yes'

Uses DATEDIFF() to get the number of days between the check in date (or if later the start of the date range you are interested in) and the check out date (or if earlier the end of the date range you are interested in) where meal is 'yes' and multiplies that by the number of guests, then uses SUM() to add them up for all bookings.

Upvotes: 1

bash3r
bash3r

Reputation: 135

I was able to use Kickstart's and Erik's reply to come up with a solution! Thanks!!

// Get Current Month
$current_month = date('F');

// Current timestamp is assumed, so these find first and last day of THIS month
$start_range = date('Y-m-01'); // hard-coded '01' for first day
$end_range  = date('Y-m-t');

        $sql = "SELECT SUM(DATEDIFF(a.chkout,a.chkin) * a.guests) AS meals
        FROM reservations AS a
        WHERE a.chkin > '$start_range' AND a.chkout < '$end_range' 
        AND meal = 'Yes' AND confirm = 'Yes'";

No I will work towards forecasting for upcoming months as well!

Upvotes: 0

Erik Nedwidek
Erik Nedwidek

Reputation: 6184

I'll give you a snippet as a starting point.

Let's say you want the top 10 guests with the longest stay.

SELECT DATEDIFF(chkout, chkin) AS daysstaying FROM reservations ORDER BY daysstaying DESC LIMIT 10

You can do calculations in queries and use the AS operator to create an alias for the result. I'm leaving it to you to work out the specific query you need to obtain the results you need.

I would also suggest that you investigate using views if this query needs to be used often.

http://dev.mysql.com/doc/refman/5.5/en/views.html

Upvotes: 0

The Humble Rat
The Humble Rat

Reputation: 4696

You can use the MYSQL date diff to achieve a count of the days, then this can be multiplied by the number of guests. Finally you will need to select a date range for this search. Below are the basics or the MYSQL needed in order to get these values. This should put you on the right track to achieving what you want.

SELECT DATEDIFF(a.chkout,a.chkin) * a.guests as meals
FROM table as a 
WHERE a.chkin > '0000-00-00' AND a.chkout < '0000-00-00

I have tested this with a table of mine and it appears to be working correctly, however, without much data I can only test lightly. If you have any issues, leave a comment and I will try to help further.

http://www.w3schools.com/sql/func_datediff.asp

Upvotes: 0

Related Questions