Show bookings_items with availability

[johnbow]

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

[chris]

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

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

[johnbow]

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

[chris]

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

[johnbow]

Chris,
Got it working in the PHP code. All good now. Thanks.
John