Php and MySQL Date validation in form

[Ahkamden]

Hey all I think this is going into the right forum.

I'm looking to have a form with a start and end date that is a series of drop down boxes to select the date.

When the client hits submit. It should compare these dates to make sure that they don't fall within any other rows dates:

For example. If Start Date 1/13/2008 and End Date 1/15/2008. Is already in the database.

No one should be able to enter a Start or End Date of 1/14/2008. Or any of the above.

Should this be done in php or should I look at mysql stored procedures(which I don't know much about?)

Thanks

[phranque]

if i understood correctly, you have several date ranges in a table and you want to avoid any overlaps with the submitted date range.
you could certainly do that in sql with something like:
SELECT COUNT(*) AS date_overlaps FROM table_name WHERE $startDate BETWEEN start_column_name AND end_column_name OR $endDate BETWEEN start_column_name AND end_column_name

where $startDate and $endDate are mysql-useful date strings constructed from your selected form values and the returned date_overlaps value should be 0 or 1.

[Ahkamden]

phranque

Thank you so much! I think you hit the nail right on the head, all I had to do was substitute my fields and seems to be working well.

Thanks again!
Ahk

[Ahkamden]

I've been using this successfully for last couple of days. But tonight I tried adding a listing ID to the WHERE statement. Because Different Listing IDs can have the same weeks.

But when I changed it to

SELECT COUNT(*) AS date_overlaps FROM table_name WHERE $startDate AND listingsdb_id='1'BETWEEN start_column_name AND end_column_name OR $endDate BETWEEN start_column_name AND end_column_name

Now date_overlaps just keeps counting up? So for now I took the slacker way and in php did an date_overlaps >= '1' echo "blah blah can't submit".

But when I try for another listing ID...ie 2 it still is echoing "can't submit"

I'm lost, I've tried looking up multiple WHERE statements info but can't find anything that pointed me in the right direction.

[whoisgregg]

The order is a little messed up... Move that new bit to the left and put in some parentheses to mark what the OR is referring to:

SELECT COUNT(*) AS date_overlaps FROM table_name WHERE listingsdb_id='1' AND ($startDate BETWEEN start_column_name AND end_column_name OR $endDate BETWEEN start_column_name AND end_column_name)

[Ahkamden]

You guys are a savior again!

Thanks Whoisgregg!