Database fetch with where between two dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
technofreak
Forum Commoner
Posts: 74
Joined: Thu Jun 01, 2006 12:30 am
Location: Chennai, India
Contact:

Database fetch with where between two dates

Post 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 ?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Can't you just use

Code: Select all

WHERE UNIXTIME(date_field) BETWEEN $start AND $end
User avatar
technofreak
Forum Commoner
Posts: 74
Joined: Thu Jun 01, 2006 12:30 am
Location: Chennai, India
Contact:

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Like this then

Code: Select all

WHERE `CustCategory` = '$user_categ' AND UNIXTIME(`date_field`) BETWEEN $start AND $end
User avatar
technofreak
Forum Commoner
Posts: 74
Joined: Thu Jun 01, 2006 12:30 am
Location: Chennai, India
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

I think it should actually be UNIX_TIMESTAMP
User avatar
technofreak
Forum Commoner
Posts: 74
Joined: Thu Jun 01, 2006 12:30 am
Location: Chennai, India
Contact:

Post 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 ?
Post Reply