Query to check availability, pulling hair out!

[ukweb]

Hi

 

I'm making a booking system for a client which checks the availability of a room. For example, there's a table which has the following columns

 

ID  |  room_id  |  date_from  |    date_to  |

 

Put in some sample data, ie, (id = 1, room_id = 1, date_from = 2010-06-01, date_to = 2010-06-23)

 

I am writing a query which takes a room_id, a date_from, and a date_to and checks how many rows there are for that date range, and obviously if there are records then the room is not available.

 

Here is the query I tried, but it doesn't work, and I haven't a clue what query to use to make it work

 

SELECT *
FROM `booking_resource-booking`
WHERE resource_id = 1
AND date_from >= DATE(2010-06-01) AND date_to <= DATE(2010-06-19)

 

ANY IDEAS would be received with gratitude. MySQL version is 5.1.37

[gizmola]

What are the datatypes of the columns in the booking_resource-booking table.

 

Speaking more generally to do comparison you just specify the date as a string like date_from >= '2010-06-01'.

[ukweb]

What are the datatypes of the columns in the booking_resource-booking table.

 

Speaking more generally to do comparison you just specify the date as a string like date_from >= '2010-06-01'.

 

Hi

 

the data types are:

ID (int)*

room_id (int)

date_from (date)

date_to (date)

 

I specified the date as string, my strengths are in PHP and not SQL queries unfortunately! The type of work I do has not given me the opportunity to explore more complex SQL queries I'm afraid, so in terms of MySQL, I'm a novice :-(

[ukweb]

does anyone have any ideas or pointers? This is really frustrating hell out me!

[xtopolis]

Can you be more specific than "it doesn't work"?  Does it give a MYSQL error, or does it not give you the result you want?  Post which one.  Additionally, it would be easier to tinker with if you posted a table dump with a few sample records of data with the query you're trying. 

[ukweb]

Can you be more specific than "it doesn't work"?  Does it give a MYSQL error, or does it not give you the result you want?  Post which one.  Additionally, it would be easier to tinker with if you posted a table dump with a few sample records of data with the query you're trying. 

 

It

 

It doesn't return any rows, no errors, just 0 results...

[ukweb]

Right, I can't see why this one won't work. I've written this test query and yet again, no results.

SELECT *
FROM `booking_resource-booking`
WHERE date_from > STR_TO_DATE('2010-04-30', '%Y,%m,%d') AND date_from < STR_TO_DATE('2010-12-12', '%Y,%m,%d')

 

What do I have to do? there is a value in the database that meets the criteria, yet NO results. If I could have an indication on where I am going wrong I could probably get the full query working as it should do...

 

[gizmola]

You're misusing STR_TO_DATE() because your format string doesn't match the string you're passing, but as I already explained you can use string constants as I showed in my previous email.