Calculating number of nights

PHP
1

I have a form into which the user enters a start date and an end date using two sets of <select> drop down menus for day, month and year. These I have asigned variables

$aday $amonth $ayear

and

$dday $dmonth $dyear

and then generated my dates like this;

$adate = date($ayear .$amonth . $aday);

$ddate = date($dyear . $dmonth . $dday);

I have then run it past checkdate() to verify date validity.

I now need to work out the number of nights between the two dates and also submit all dates between the start and end to my database.

How can I do this?

2

Which format are dates in your database?

Does your PHP logic also check that $adates are less than $ddates?

3

Dates in database are in format yyyy-mm-dd

I have this at present to check dates are all future dates and also check that $ddate is later than $adate

$today = date(Ymd);	
	echo '<p>todays date is'.$today . '</p>';
	
	
	if ($ddate > $today)	{
	echo 'the departure date inserted is later than today';
	}	else	{
		exit ('the departure date you have selected has gone');
	}
	
	if ($ddate > $adate)	{
		echo '<p>departure date later than arrival date ok.</p>';
	}	else	{
		exit('<p>departure date is before arrival date.</p>');
	}

also same code to check $adate > $today

4

You should be able to do something like this:

$nights = (strtotime($departure) - strtotime($arrival)) / 86400;
5

I now need to work out the number of nights between the two dates

Get PHP to do that if you like …

$adate="2008-03-12";
$ddate ="2008-03-20";
$diff = strtotime($ddate) - strtotime( $adate);
echo $diff/86400;

Clunky but goes …

and also submit all dates between the start and end to my database.

Depends really on how your database is set up, presumably you want to find out if a date does not exist within a set of records.

 
      SELECT things
      FROM yourData
      WHERE aDate >= '2008-03-12' AND dDate <= '2008-03-20'

Because if there is a booking on the 15th March, you return something, therefore your logic fails, and booking cannot take place.

Helps?

6

Thanks. That works well to get number of nights. Had not come across strtotime before.

Now stuck getting nights listed individually. Tried a for loop like this

for ($count = $adate; $count < $ddate; ++$count)	{
		echo "$count";
	}

This puts 99 days in every month! Just trying to list the dates on the web page at this stage rather than insert to database. Also tried basing a ‘for’ loop around the strtotime function which gave thousands of results.

When submitted to the database there is an allocation of 20 beds available for each day, which can be booked all together or as two or more separate bookings. php will need to check number of available beds when booking is submitted and either allow or reject it. If allowed it then needs to process details for individual booking aswell as keeping track of the total number of beds booked.

7 

$adate="2008-03-12";
$ddate ="2008-03-20";
$endDate = strtotime($ddate);
$thisDate = $startDate = strtotime($adate);

do {
   echo date('d-M-Y',$thisDate).'<br />';
   $thisDate += 86400;
} while ($thisDate <= $endDate);
8

It is probably a good time to display the relevant table schemas as much of what you will want to do will depend on how you are storing dates, mulitple bed bookings etc.

That way we can best advise on which of PHP or Mysql does the grunt work of date manipulation and retrieval.

9

Here goes to attempt to explain what we want to achieve overall.

We need to offer

  1. booking and price options and bed availability to customers on our website, take online booking details from them,

  2. be able to retrieve / print details of individual bookings

  3. keep a day by day list of the number of beds booked with each booking aswell as total number of beds booked.

  4. block any attempted booking when there are insufficient beds available, or with invalid dates or other irregularities.

Payment will be taken using nochex / paypal and at this stage enterd into booking details ourselves. This aspect not quite so important as we can keep track of payments and personal details elsewhere, but wanted to add it to complete the system.

Probably quite a tall order for a php mysql beginner but I am determined to learn.

So far I have these mysql tables

mysql> describe customer;
custid auto_increment
name
address1
address2
postcode
email

mysql> describe booking;
bookingid auto-increment
custid
totalcost
paymentrecd
depositrecd

mysql> describe bedsbooked;
bookingid
date
bedsbooked1
bedsbooked2

bedsbooked 1 and 2 are beds in two separate areas, maximum 6 in 1 and 14 in 2 (maximum 1 + 2 = 20)

date is in standard mysql format yyyy-mm-dd

mysql> describe bunkhouse;
bedplanid auto_increment
bedplan
pricepernight

bedplan gives written description of option selected eg. individual bed, winter price; exclusive booking of all beds etc

If there is a better way to set out tables all can easily be changed at this stage.

10

Thanks Mark Baker for that bit of code- so simple.

11

I think you need to decide on how to store each booking range.

Let’s say person a books 3 beds in room1 (limit 6) for the dates 1st, 2nd 3rd of March.

AFAICT, you have 2 main ways to think about storing that.

1 Store the number 3 in the bookings table in room1 with a date range in


bedsbooked;
=========
bookingid      | 23
aDate          | 2008-03-01
dDate          | 2008-03-03
bedsbooked1 | 3
bedsbooked2 | 0

OR: store each booking, date, bed “instance”.


bedsbooked;
=========
bookingid      | 23
bookingDate  | 2008-03-01
bedsbooked1 | 1
bedsbooked2 | 0

bookingid      | 23
bookingDate  | 2008-03-02
bedsbooked1 | 1
bedsbooked2 | 0

bookingid      | 23
bookingDate  | 2008-03-03
bedsbooked1 | 1
bedsbooked2 | 0

The questions you need to ask yourself might include:

What advantage does each method present?
What drawback does each method present?

Any flexibility you can find may have implications for your client, for example the second method offers their customers the ability to pick which actual bed is booked …

so


bookingid      | 23
bookingDate  | 2008-03-03
bedsbooked1 | 5 [bed number 5, next to the toilet ;) ]
bedsbooked2 | 0

Whilst keeping an eye on these 2 issues:

What is likely to be the most frequent database event? selecting or updating? and what effect will my decisions make?

How complicated will the joins be?

Neither of them are show stoppers, because optimizing your queries before you start is not the right way to design things - but still, bear it in mind, and there is a fantastic sql forum here which will get you out of most of the holes you might dig yourself into.

Answer your first question first though:

I now need to work out the number of nights between the two dates and also submit all dates between the start and end to my database.

So, when Mrs Bloggs tries to book 14 nights, 4 beds in room1, from 2nd March to 16th March - how do you flag up and deal with what is quite a small problem which could cause your client to lose the hefty booking?

12

Thanks for all that. Lots to think about.

The second choice, booking, date, bed “instance” looks preferable.

Thoughts to develop this further in the future are to include a plan of the building to enable individual beds to be selected for each booking by clicking on them, probably by management rather than by client.

To deal with Mrs Bloggs if room 1 is not available for 2 nights as example then when she tries to book she needs to receive a message which tells her “room 1 not available 2nd, 3rd March; space still avilable in room 2 for these dates. Do you wish to continue booking? For help please telephone” or something to that effect. Could also raise a query to management for attention.

Without the experience I am unable to assess the complexity of joins etc until I come to do it. Any thoughts from those with experience welcomed.

13

So you have identified that you could want to iterate through a list of potential dates working out the overall likelihood that a booking can take place.

Sounds a little sophisticated ATM, but sounds right for the business.

Kind of :
if total nights (5) and beds required (5) (ie 25 instances) and double bookings is < 20% of total instances - then flag up to the user and management that some deal could be worked out …

I’d add that on later, but you can bear this possibility in mind.

Ditto with the “named bed booking” idea, which reminds me of how I can change my seat when having booked a flight with some operators (I hate window seats).

Without the experience I am unable to assess the complexity of joins etc until I come to do it. Any thoughts from those with experience welcomed.

I was somewhat clumsily trying to address that fear, and reassure you about that.

If you can pose your questions correctly and succinctly in the mysql forum you will discover many helpful people willing to walk you through the essential points.

The trick, as ever it seems to me, is to understand the PHP/Mysql partnership enough to know which handles this applications’ date handling responsibilities the “best”.

That “best” might be the cleanest or the fastest, or the easiest to modify in the future, but there is a lot of duplicity between PHP/Mysql when it comes to dates.

So you might well find yourself switching between this PHP forum and the Mysql forum looking for solutions.

Take it one issue at a time, and if in doubt post it on here.

I say all this in order that (unlike me) you reduce the length of time spent developing “starting from the database design and working outwards”, and think more about “these are the requirements, I will make the database suit them - I know that some db guru will help me on SP”.

14

Thanks for all that; good basic stuff, just what a beginner needs. Moving from basic html into php has opened up a whole new ball game with obvious benefits but also something of a minefield.

I think best way forward is to stick with the database as it stands and develop basic booking system with that, always keeping in mind that it can be developed further or changed at a later date once I have some php mysql experience.

Must say the help from the forum has been excellent so far, especially Cups. Biggest problem is actually pinpointing and explaining precicely what is required!

15

I agree with you.

Most kind of you to say so.

Exactly. Most good posts, as with your original question, contain some code that others can “fix”, but sometimes, as in this case, it is easy to wander off in order to explore wider issues.