Date Comparison problem

ibiraja

Hi.
The date is saved in DATETIME type format in mysql db. When I try to run query to match the date in dababase with current date then it does'nt compare and nothing is returned.
Here is the code:

$query_Rec1 = "SELECT * FROM booked_ads WHERE booked_ads.cat_aid='$search_id2' AND booked_ads.cat_bid='$in_category3' AND ". date("Y-m-d",strtotime('booked_ads.start_date')) . " = " . date('Y-m-d') . " AND booked_ads.ad_details LIKE '%$in_details[$i]%' OR booked_ads.ad_heading LIKE '%$in_details[$i]%' ORDER BY booked_ads.cat_aid ASC";
$query_limit_Rec1 = sprintf("%s LIMIT %d, %d", $query_Rec1, $startRow_Rec1, $maxRows_Rec1);
$Rec1 = mysql_query($query_limit_Rec1, $AdData) or die(mysql_error());
$row_Rec1 = mysql_fetch_assoc($Rec1); 
$t_row=mysql_num_rows($Rec1);

Can anyone point out what's wrong with the code? I'm really tired after tyring for 3 days.
Thanx

Shrike

You will need to use the MySQL date functions rather than those in PHP (DATE_FORMAT() rather than date() )

rachel2004

Originally posted by ibiraja
". date("Y-m-d",strtotime('booked_ads.start_date')) . " = " . date('Y-m-d') . " AND

You cannot use inner double quotation marks within outer double quotation marks in this case. Switch to either single quotes or escape your inner double quotation marks.

Second, to fix the problem you asked about, try converting everything to UNIX timestamps when you do time comparisons.

At the mysql.com site, look up the UNIX_TIMESTAMP function.

If you wish to use PHP, use either mktime() or time() functions. Go to the PHP manual for details on using them.

ibiraja

Thanks all. I just replaced the query and changed the date format to mysql format and it works perfect.

DATE_FORMAT(booked_ads.start_date,'%e/%c/%Y')=DATE_FORMAT(CURDATE(),'%e/%c/%Y')

Thank you for your help.