Question on MySQL SELECT with dates

blueman

I am having a problem with a SELECT statement.

HARD CODED DATES - THIS QUERY WORKS - RETURNS THE DATES FROM THE DATABASE:

$before = "2003-01-01";
$after = "2003-09-08";
$query_date = mysql_query("select * from reports where report_date between '$before' and '$after'");

NOW THE DATE VARIABLES ARE DYNAMICALLY CREATED - DOESNT WORK:

$after = date('Y-m-d', mktime(0,0,0,date('n'), date('j')-$recent_days, date('Y')));
$before = date('Y-m-d');
$query_date = mysql_query("select * from reports where report_date between '$before' and '$after'");

CAN ANYONE TELL ME WHAT I'M MISSING????

thanks in advance

LordShryku

Try instead

$after = date("Y-m-d", mktime(0,0,0, date(m), date(d)-$recent_days, date(Y)));

blueman

🙁 Nope... I tried it... didnt work

thanks anyways

LordShryku

Do you get an error from mysql_error()? Have you tried echong the SQL statement to the screen?

blueman

no, it doesnt generate an error.

but after the select is done, and it fetches and loops the results, i get 0 rows from the database.

but when you assign the values like $before = "2003-01-01";
$after = "2003-09-08"; it retrieves the results normally...

thanx again

LordShryku

Just to debug, pull the query out of the function.

$after = date('Y-m-d', mktime(0,0,0,date('n'), date('j')-$recent_days, date('Y'))); 
$before = date('Y-m-d'); 
$query_date = "select * from reports where report_date between '$before' and '$after'"; 
echo $query_date;

Just to make sure your query looks right.

blueman

the URL:

http://216.242.44.197/search_form_test.php

If you just hit SUBMIT, without filling any fields, it should pull 3 or more rows from the database.
as you can see, it echoes the dates correctly at the top, but doesnt return anything from the database

blueman

here's the qurey echoed as you suggested:

select * from reports where status = '1' AND lady_name like '%%%' AND screen_name like '%%%' AND report_date between '2003-09-09' and '2003-09-02'