MySQL Forums
Forum List  »  Newbie

Re: checking item availability by date
Posted by: Puiu Hrenciuc
Date: September 12, 2004 05:26PM

First of all, as I said to others, do not put GET or POST vars directly into SQL since
this is a pottential security issue.

Regarding your SQL instead of giving you the answer, I'll try to make you
understand the answer, let's analyse it a bit :

You are saying :

Give me item_id for records that have :
1. Hire_Start_date > Entered_End_date OR
2. Hire_End_date < Entered_start_date

Today is : 13.09.2004
Someone hired item_1 between 18.09.2004 and 21.09.2004
One client wants to hire the same item between 17.09.2004 and 22.09.2004

1. Hire_Start_date > Entered_End_date (=false since 18.09.2004 > 17.08.2004)
2. Hire_End_date < Entered_start_date (=TRUE !!! since 21.09.2004 < 22.09.2004)

So as you can see item_1 would be returned even if it is already hired and the
periods intersects. What you have to do is check that :

1. (Hire_Start_date > Entered_Start_date AND Hire_Start_date > Entered_End_date) OR
2. (Hire_End_date < Entered_Start_date AND Hire_End_date < Entered_End_date)

in other worlds both checked start and end date must be on the same side of the interval ( all before of already hired interval or all after that interval ). Of course I assume that you already check in your code if Entered_start_date<Entered_end_date.

Sorry for this long answer, but I hope you understand the answer and not just Copy
and Paste it .

P.S.: Here is your SQL code using the abore rules :

SELECT DISTINCT item_id
FROM hire_item
WHERE (hire_item.start_date > $HTTP_POST-VARS [‘start_date’] AND
hire_item.start_date > $HTTP_POST-VARS [‘end_date’]) OR
(hire_item.end_date < $HTTP_POST-VARS [‘start_date’] AND
hire_item.end_date < $HTTP_POST-VARS [‘end_date’]);

Do not forget the pharantesis.

Hope it helps,
Puiu.

Options: ReplyQuote


Subject
Written By
Posted
September 12, 2004 11:46AM
Re: checking item availability by date
September 12, 2004 05:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.