Page 1 of 1

Database fetch with where between two dates

Posted: Mon Jun 26, 2006 6:07 am
by technofreak
Hi,

Am trying to fetch data from the database, for dates between two values. The fields in my database for the dates are under the DATE data type and are working for other operations. I calculate the date after one month from current date, and try to fetch a date value 'DateOfExpiry' between today's date and date after one month, using the following code..

Code: Select all

$query = "SELECT * FROM customer_info WHERE CustCategory = '$user_categ' AND DateOfExpiry BETWEEN '$today' AND '$next_month'";
$q = mysql_query($query);
if ( ! $q ) { die("Can't Fetch: " .mysql_error()); }
Then i display the rows fetched by the query as a table. When i try this code, it doesnot return any results though there are rows satisfying this condition.

I calculate $today and $next_month by,

Code: Select all

$today = date('Y-m-d');
$epoch_today = mktime(0,0,0,date('m'),date('d'),date('y'));
$epoch_next_month = mktime(0,0,0,date('m')+1,date('d'),date(y));
$next_month = strftime('%Y-%m-%d', $epoch_next_month);
When i try to print these values it prints correctly but only the rows are not fetched from the databse. There is no error during fetch and also the field names are correctly displayed in the heading of the table. I am sure, my query statement is not working properly. Where am I wrong ?

Posted: Mon Jun 26, 2006 7:10 am
by Chris Corbyn
Can't you just use

Code: Select all

WHERE UNIXTIME(date_field) BETWEEN $start AND $end

Posted: Mon Jun 26, 2006 7:53 am
by technofreak
I need to have "CustCategory = $cust_cat" in the query. I have problems only with the date part of the query, think so.

Posted: Mon Jun 26, 2006 7:54 am
by JayBird
Like this then

Code: Select all

WHERE `CustCategory` = '$user_categ' AND UNIXTIME(`date_field`) BETWEEN $start AND $end

Posted: Mon Jun 26, 2006 7:58 am
by technofreak
When i used,

Code: Select all

WHERE UNIXTIME(DateOfActivation) BETWEEN $today AND $next_month
The following error was shown in the browser..

Code: Select all

Can't Fetch: FUNCTION hdp_crm.UNIXTIME does not exist

Posted: Mon Jun 26, 2006 8:12 am
by JayBird
I think it should actually be UNIX_TIMESTAMP

Posted: Mon Jun 26, 2006 11:54 am
by technofreak
I tried to the same query in the mysql command line in the terminal and the query fetches the results expected. So, I concluded that the variables which i use, the $today and $next_month, are not actually filling in the required values.

The query doesnot cause an error message to be displayed because, the field names are being fetched correctly and its just a zero rows fetched condition. What am not able to find is, when i echo the variables it displays corectly. But when i use it in the query statement, its not working out properly. why ?