Selecting records between two dates

teknodude

This problem is driving me mad!

I have two dates stored in a MySQL table in DATE columns, one named rental_period_begin and the other rental_period_end. The date are stored in the format 0000-00-00. An example date is 0000-01-09 (the year is not required).

The user inputs a date and a duration, causing the generation of two dates:

$start_date = $f_date_month."-".$f_date_day;
$end_date = @strftime("%Y-%m-%d", @mktime(0, 0, 0, $f_date_month, $f_date_day + ($f_duration * 7)));
$end_date = @substr($end_date, 5, @strlen($end_date));

The end date, as you can see, is chopped to remove the "0000-" from the mktime function.

The following SQL in theory (my theory!) should work, and select DB records between the two dates generated above:

WHERE DATE_FORMAT(rental_period_begin, '%m-%d') >= '#".$start_date."#' AND DATE_FORMAT(rental_period_end, '%m-%d') <= '#".$end_date."#'

But it doesn't, and instead no records are found matching the WHERE conditions.

Any help would be great! Thanks,

teknodude

nnichols

Why are you adding the "#"s to the dates?
Try echoing the query to the browser and I'm sure you will see what's wrong.

nnichols

Change -

$end_date = @strftime("%Y-%m-%d", @mktime(0, 0, 0, $f_date_month, $f_date_day + ($f_duration * 7))); 
$end_date = @substr($end_date, 5, @strlen($end_date));

to -

$end_date = @strftime("%m-%d", @mktime(0, 0, 0, $f_date_month, $f_date_day + ($f_duration * 7)));

teknodude

Thanks nnichols, I've tidied up the $end_date variable now. I was only putting in the #s because when you insert a date into the DB you put #s around it to indicate that it's a date.

I already am echoing the query to the browser, and there is no error to my eye :-S Here's an example query:

SELECT listing_id, property_address, property_occupants, rental_price, DATE_FORMAT(rental_period_begin, '%M %D') AS rental_period_begin, DATE_FORMAT(rental_period_end, '%M %D') AS rental_period_end, property_description FROM listings WHERE DATE_FORMAT(rental_period_begin, '%m-%d') >= '06-16' AND DATE_FORMAT(rental_period_end, '%m-%d') <= '06-23' AND date_approved IS NOT NULL AND date_approved <> '' AND enabled=1 ORDER BY date_approved DESC LIMIT 0, 5

Thanks for your help so far,

teknodude

nnichols

When you run queries you should use something like this -

$sql = "query stuff here";
$res = mysql_query($sql, $db)
or die('MySQL error:' . mysql_error() . '<BR>Query: ' . $sql);

This gives nice error messages, which are helpful most of the time.

I can't see any errors in your syntax. Do you get any errors or just nothing returned?

nnichols

I think the problem lies with the logic in your WHERE clause. Check it carefully.

teknodude

Well the rest of the logic in the WHERE clause works ok, it's just the part peretaining to the date that isn't working i.e. :

DATE_FORMAT(rental_period_begin, '%m-%d') >= '06-16' AND DATE_FORMAT(rental_period_end, '%m-%d') <= '06-23'

Your help so far is really appreciated 🙂 Any more ideas please?

teknodude

nnichols

It depends on what it is your query is supposed to retrieve. You need to explain what the data in the table represents and what exactly you want to query for.