List weeks of year using calendar function

[jockturner]

Hi,

 

My grasp of PHP is pretty rudimentary, however, I try to learn as much as I can as I go along. Anyway, I've been asked to design a small site which contains a page with a list of dates to show a visitor if accommodation (running from Saturday - Friday) is booked or available. Something like this:

 

2007

2nd Jan - 8th Jan  -  Available

9th Jan - 15th Jan  -  Available

16th Jan - 22nd Jan  -  Booked

23rd Jan - 29th Jan  -  Available

5th Feb - 11th Feb  -  Available

12th Feb - 17th Feb  -  Available

 

etc, etc

 

I'd like to dynamically create the list of dates using the php calendar functions and a mysql database so that each row (52 in total?) could be flagged 'booked' or 'available' allowing easy updating using a back end form. Ideally the page showing the list would have a link taking the visitor to another page showing the following year's dates. Each list would always show a year to view and always run from Jan to Dec.

 

I'm really not clever enough to work this out and any help would be really appreciated.

 

Thanks, Jock

 

 

[cooldude832]

I'm not trying to destroy your idea, but only enhance it.

 

It looks like you are talking about some sort of hotel/lodging location.  I'll assume it has rooms in it lets say rooms A,B,C for simplicity sake.  What you should do is design a table with this sort of structure

BookingID (Primary Key AutoIncriment)

Room (The room choice A,B,C etc)

Date (The date the room is booked for, only 1 day at a time)

 

Then what you do is the user inputs the date range they looking for (You can make a simple form that is a select with the options being each week of the year and the value being some sort of useable data for mysql such as

<option value="20080101:20080108">January 1st- January 8th</option>  then on your second page you can do something like

<?php
$dates = explode(":",$_POST['date']);
$q = "select from `bookings` where Date =>  '".$dates[0]."' and Date <= '".$dates[1]."'";
$r = mysql_query($q) or die(mysql_error());
$rooms = mysql_num_rows($r);
/* Now lets think for a minuite we have a date range of 8 days and 3 rooms in our hotel so there are 24 rooms so lets do a bit of math
*/
$open_rooms = 24-$rooms;
if($open_rooms >0){
echo "There are ".$open_rooms." open on the week of ".$dates[0]." to ."$dates[1];
}
else{
echo "There are currently no rooms open on the week of ....";
}
?>

 

This method allows for a more flexible solution that could be easily expanded such as allowing online booking as an example or allowing a calendar to show which rooms are open on a given day.  Even if you have like 50 rooms its still easy to do because the 24 i used in the open_rooms equation can be a constant defined.

 

If you have questions PM i can show you it in more details

[Barand]

Are we talking about a single property and are all bookings on a weekly basis?

[Psycho]

Homework problem?

[jockturner]

I think it is easier for me to point you to the page which I an trying to emulate: http://www.lanhydrock.com/cantariff.asp

Obviously this is hardcoded but I'm trying to create a more dynamic solution which is linked to a mysql database for easy updating. As well as the current year showing it would be great to have a link which took the visitor to the following year's availability chart.

 

As you can see it is only for one property but making it extensible for more than one should be easy once the core script has been developed.

 

Thanks for the replies so far, any more ideas will be really appreciated.

 

Thanks again...Jock

[cooldude832]

Thats fine if you have 1 room you will need a mysql table called bookings

BookingID (primary key auto increment)

Date (in the format of YYYY/MM/DD)

Booked (Bool type)

 

That is all you need to get started, although your example shows price which you might want to add on later. 

 

Then you need an admin page that has 2 inputs

1 is the date

2 is a selection (yes/no if its open or not)

 

Then you just need to query that data, but there is a trick to do it so you can update it if someone cancels a booking.

booking_process.php

<?php
$date = $_POST['date'];
//Connect all your sql stuff
$q = "select BookingID from `Bookings` Where Date = '".$date."'";
$r = mysql_query($q) or die(mysql_error());
//If the booking is in lets update it from booked to not booked or the other way around
if(mysql_num_rows($r) >0){
$id = mysql_result($r, 0);
$q = "Update `Bookings` set Booked = '".$_POST['booked']."' Where BookingID = '".$id."'";
$r = mysql_query($q) or die(mysql_error());
}
else{
$q = "insert into `bookings` (Date, Booked) Values ('".$date."', '".$_POST['booked']."')";
$r = mysql_query($q) or die(mysql_error());
}
?>

 

The form for that is like

<html>
<form method="post" action="booking_process.php">
<input type="text" name="date" />
<select name="booked">
<option value="1">Yes</option>
<option value="0">No</option>
</select>
<input type="submit" />
</form>
</html>

 

I hope that makes sense to you, any way now you have a partial list of bookings/not bookings for certain dates (its okay that we have blanks because that isn't important).

 

Now we need to create something that tells the user which dates are open, there are many ways to do this, the easiest is to do a query that finds all bookings that fall between the start date and end date where Booked = 1.  So a query be like

 

<?php
$start_date = "2007/01/01";
$end_date = "2007/01/10";
$q = "select BookingID from `Bookings` Where Booked = 1 AND Date >= '".$start_date."' AND Date <= '".$end_date."'";
$r = mysql_query($q) or die(mysql_error());
//Do some date math to figure out how many days are $end_date-$start_date
//Now lets test how many booked rooms we have on that range
if(mysql_num_rows($r) => $number_days)){
//No open rooms
}
else{
//We have open rooms
}
?>

 

I hope this makes sense to you, doing bookings by a week isn't the best method here because odds are you will want to expand down the road and you will be stuck into a very rigid system.  This system allows you to add a lot more data to this table to do more for you later.

[jockturner]

Great stuff!

 

I 'll let you know how I get on and (if you are interested) I will send you the URL of the script in action.

 

Thanks for your interest and support,

 

Jock