SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    calculating number of nights

    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

    Code:
    $aday $amonth $ayear
    and

    Code:
    $dday $dmonth $dyear
    and then generated my dates like this;

    Code:
    $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. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Which format are dates in your database?

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

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    $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. #4
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should be able to do something like this:

    PHP Code:
    $nights = (strtotime($departure) - strtotime($arrival)) / 86400

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I now need to work out the number of nights between the two dates
    Get PHP to do that if you like ...

    PHP Code:
    $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.
    Code:
     
          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. #6
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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. #7
    SitePoint Evangelist
    Join Date
    Jun 2006
    Location
    Wigan, Lancashire. UK
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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); 
    ---
    Development Projects:
    PHPExcel
    PHPPowerPoint

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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. #9
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

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

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

    Code MySQL:
    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

    Code MySQL:
    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. #10
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Mark Baker for that bit of code- so simple.

  11. #11
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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

    Code:
    bedsbooked;
    =========
    bookingid      | 23
    aDate          | 2008-03-01
    dDate          | 2008-03-03
    bedsbooked1 | 3
    bedsbooked2 | 0
    OR: store each booking, date, bed "instance".

    Code:
    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
    Code:
    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. #12
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #13
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by grizzley View Post
    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.
    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. #14
    SitePoint Zealot
    Join Date
    Oct 2008
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #15
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by grizzley View Post
    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.
    I agree with you.

    Quote Originally Posted by grizzley View Post
    Must say the help from the forum has been excellent so far, especially Cups.
    Most kind of you to say so.

    Quote Originally Posted by grizzley View Post
    Biggest problem is actually pinpointing and explaining precicely what is required!
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •