Hi!
I hope someone can help me to achive the following:
First, this my table shema:
TABLE NAME: hotel_rates
FIELDS:
ID (INTEGER, AUTO_INCREMENT, PRIMARY KEY)
hotel_name (VARCHAR 40)
room_type (CHAR 3)
date_from (DATE)
date_to (DATE)
rate (FLOAT 4,2)
This table “hotel_rates” looks as follows with some records:
--------- --------------------------- ------------------ ----------------- --------------
| ID | | hotel_name | | room_type | | date_from | |date_to | | rate |
--------- -------------------------- ------------------ ----------------- --------------
1 casino beach SGL 2006-01-01 2006-03-20 150
2 casino beach SGL 2006-03-21 2006-06-15 200
3 casino beach SGL 2006-06-16 2006-09-22 150
4 casino beach SGL 2006-09-23 2006-12-31 200
6 casino beach DBL 2006-01-01 2006-03-20 250
7 casino beach DBL 2006-03-21 2006-06-15 300
8 casino beach DBL 2006-06-16 2006-09-22 250
9 casino beach DBL 2006-09-23 2006-12-31 300
10 casino beach TPL 2006-01-01 2006-03-20 350
11 casino beach TPL 2006-03-21 2006-06-15 400
12 casino beach TPL 2006-06-16 2006-09-22 350
13 casino beach TPL 2006-09-23 2006-12-31 400
14 casino beach CPL 2006-01-01 2006-03-20 450
15 casino beach CPL 2006-03-21 2006-06-15 500
16 casino beach CPL 2006-06-16 2006-09-22 450
17 casino beach CPL 2006-09-23 2006-12-31 500
Now, if a client wants to know all the rates (SGL, DBL, TPL and CPL) for the hotel casino beach and such the dates he wants to travel, lets say, from 2006-01-05 to 2006-01-10 …. I need to query my table “hotel_rates” in order to display the records that match the criteria from 2006-01-05 to 2006-01-10….in this case, the query should show the following results:
--------- --------------------------- ------------------ ----------------- --------------
| ID | | hotel_name | | room_type | | date_from | |date_to | | rate |
--------- -------------------------- ------------------ ----------------- --------------
1 casino beach SGL 2006-01-01 2006-03-20 150
6 casino beach DBL 2006-01-01 2006-03-20 250
10 casino beach TPL 2006-01-01 2006-03-20 350
14 casino beach CPL 2006-01-01 2006-03-20 450
How should I write the right query to achive this results? Is it possible?
Sorry for my English, hope I could make myself understood.
Thanks in advance for any help,
Vago
5  1668  Please read the Posting Guidelines before you post in this forum!
The user's start date must be higher/equal to the table date_from and the user's end date must be lower/equal to the table date_to, so the query is:
[PHP]SELECT * FROM table_name
WHERE date_from <= '$var1' AND date_to >= '$var2';[/PHP]
Ronald :cool:
Hi Ronald!
Thanks a lot for your help.
I follow your instructions and it works for the date range given in my eaxple: from 2006-01-05 to 2006-01-10
However, if I try the following range of dates dose not work, thats to say, I get not results found: from 2006-03-19 to 2006-03-22
I see that the user's start date 2006-03-19 is on the range of the field "date_to" and the user's end date is also on the range of the field "date_to" as well.
With the right query, I should get the following results:
--------- --------------------------- ------------------ ----------------- --------------
| ID | | hotel_name | | room_type | | date_from | |date_to | | rate |
--------- -------------------------- ------------------ ----------------- --------------
1 casino beach SGL 2006-01-01 2006-03-20 150
2 casino beach SGL 2006-03-21 2006-06-15 200
6 casino beach DBL 2006-01-01 2006-03-20 250
7 casino beach DBL 2006-03-21 2006-06-15 300
10 casino beach TPL 2006-01-01 2006-03-20 350
11 casino beach TPL 2006-03-21 2006-06-15 400
14 casino beach CPL 2006-01-01 2006-03-20 450
15 casino beach CPL 2006-03-21 2006-06-15 500
It could be possible to solve this type of escenarios? Do I hava to change the structure of the table?
I, ve been thinking about the option of using just one date field insted of two in order to use BETWEEN in the SQL query. However I would need to insert the rate for every type of room X 365 days = 1460 records per hotel !!! and every year I will need to update all those records!!!
What it can be the solution?
Thanks again in advance for any help!!!
Vago
Oh, you didn't tell me that. However, try the following. I didn't have much time to test it, so you'll have to do that. -
SELECT * FROM table
-
WHERE
-
date_from BETWEEN '$var_from' AND '$var_to'
-
OR
-
date_to BETWEEN '$var_from' and '$var_to';
-
Ronald :cool:
Hi Ronald!
Thanks a lot for your help!!! You are the man!!! It works !!!
I wish you the best for Christmas and New Year 2007!!!
Sincerely,
Vago
You are welcome. And happy holidays to you too.
Ronald :cool:
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mark Reed |
last post by:
Hi All,
I have a table with a date field and 6 number fields. The number fields are
not in numerical order across the fields and I want number 1 to be the
lowest number and number 6 to be the...
|
by: Bill |
last post by:
Hello out there;
This may be a challenge but I'm certain it's possible but I can't seem
to figure out how.
I have a table that has several date fields, e.g., Date1, Date2,
Date3, Date4 ......
|
by: Norma |
last post by:
I am trying to make a query pull data from between the dates I enter
in the parameter but also look back 'in time' to see where 2 other
fields have null values, and only pull data into the query if...
|
by: Matthew |
last post by:
Hey ,
I have built a query which takes values from unbounded fields on a
form, and it all works except for one thing. I have a few fields in
my query that are dates. I also have a start and...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
...
|
by: Don Sealer |
last post by:
I'm guessing this is pretty simple however not simple enough for me.
I'm developing a database to track expenses, income, banking
transactions, etc.
I have a very simple query with four fields,...
|
by: Liam.M |
last post by:
hey guys,
I have one last problem to fix, and then my database is essentially
done...I would therefore very much appreciate any assistance anyone
would be able to provide me with.
Currently I...
|
by: Ragbrai |
last post by:
Howdy All,
I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains...
|
by: QCLee |
last post by:
Sir can you help me to transfer my Access Query to MS excel? i have a
command button on the form to export the parameter query named
"HVACWindwardQuery" to excel spreadsheet and i got the codes...
|
by: Arli |
last post by:
I have the following linked tables:
tblMainPL is my main table that I need to pull the information in from. It has the following fields:
Autonumber1 -PK set as autonumber
Date - short date...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
