Show bookings_items with availability

Help with installation and general calendar setttings

Show bookings_items with availability

Postby johnbow » Wed Jan 26, 2011 12:16 pm

Hi Chris,
Excellent calendar - hence the donation.

I have a search form that submits a month from a drop-down menu, say 'Jan'. I want to query the database to show a results page with booking items that are available in that month, (even if only one or two days are free).

As your initial database only holds dates booked I've assumed that I need to add a table that has all the dates of the year, say 2011. This might not be the way forward but I can't figure out the PHP to do it. I've tried various SQL queries but just can't get the correct result. (BTW, I'm using DATE_FORMAT(the_date, '%b') to search for all available dates in a particular month, e.g Jan.)

Can you help please?
John
johnbow
 
Posts: 3
Joined: Wed Jan 26, 2011 12:00 pm

Re: Show bookings_items with availability

Postby chris » Wed Jan 26, 2011 1:13 pm

Hi John,
Thanks for the compliment and a special thanks for the donation. It is greatly appreciated! :D

I don't think that you need to create any more database tables to achieve what you want to do.
Basically you need to check the bookings table for each item and month and only return items with a total number of bookings that is less than the number of days in the given month.

The following query assumes that you have sent (via your select list) a numeric month (1 to 12) and the year.

Code: Select all
SELECT
DAY(LAST_DAY('$_GET["year"]-$_GET["month"]-1')) AS days_in_month,
t1.desc_en,
COUNT(t2.id) AS tot_bookings
FROM `bookings_items` AS t1
LEFT JOIN `bookings` AS t2 ON (t2.id_item = t1.id AND MONTH(t2.the_date)=$_GET["month"] AND YEAR(t2.the_date)=$_GET["year"])
GROUP BY t1.id
HAVING tot_bookings<days_in_month


All being well, this "should" return a list of all the items in your "items" database table that have less bookings than the number of days in the month.

The only php that you would need would be the standard code to execute the above query and print out the resulting items.
From your post I am assuming that you would know how to implement this query. If not, feel free to give me a shout and I will try to expand on it more.

Let me know how it goes.

Chris
User avatar
chris
Site Admin
 
Posts: 1089
Joined: Mon Dec 17, 2007 7:42 pm
Location: Málaga, Spain

Re: Show bookings_items with availability

Postby johnbow » Wed Jan 26, 2011 2:25 pm

Thanks Chris,

I did wonder whether or not I needed the extra table.

However, I can't get your query to work but I'll work on it. Could be I've copied your code 'as is' but need to sort out the syntax - double quotes perhaps.

Anyway, thank you very much for the speedy response.

Cheers,
John
johnbow
 
Posts: 3
Joined: Wed Jan 26, 2011 12:00 pm

Re: Show bookings_items with availability

Postby chris » Wed Jan 26, 2011 2:32 pm

OK, I was running that code direct in the database and with hard-coded months and years.
Here it is a bit more elaborated with php:
Code: Select all
$sql="
SELECT
DAY(LAST_DAY('".$_GET["year"]."-".$_GET["month"]."-1')) AS days_in_month,
t1.desc_en,
COUNT(t2.id) AS tot_bookings
FROM `bookings_items` AS t1
LEFT JOIN `bookings` AS t2 ON (t2.id_item = t1.id AND MONTH(t2.the_date)=".$_GET["month"]." AND YEAR(t2.the_date)=".$_GET["year"].")
GROUP BY t1.id
HAVING tot_bookings<days_in_month
";
$res=mysql_query($sql) or die("error getting available items");
while($row=mysql_fetch_assoc($res)){
    echo $row["desc_en"].'<br>';
}


Chris
User avatar
chris
Site Admin
 
Posts: 1089
Joined: Mon Dec 17, 2007 7:42 pm
Location: Málaga, Spain

Re: Show bookings_items with availability

Postby johnbow » Wed Jan 26, 2011 3:19 pm

Chris,
Got it working in the PHP code. All good now. Thanks.
John
johnbow
 
Posts: 3
Joined: Wed Jan 26, 2011 12:00 pm


Return to Support

Who is online

Users browsing this forum: No registered users and 2 guests

cron