Date Search Help !!!
Posted 05 May 2006 - 04:01 PM
I've been trying to get this sorted for ages and have searched loads of forums and so on ... no joy !
Basically I have a DB setup with a timestamp filed and date filed
timestamp = time();
date = date('d-m-Y' , time());
This is working fine and inputting the timestamp and also the date into my DB, I have a seach function on the date where the user selects a date from a calendar ie 05-05-2006 and that search the date colum, then I have the ability to seach back 7 days using the time() - 60*60 etc ....
My problem is searching between dates !!! When the user inputs 01-04-2006 to 01-05-2006 and using the sql command WHERE date >= $date1 AND date <= $date2 it doesnt work, and I have no idea how to make it work - i've tried converting the 01-04-2006 to a timestamp but no luck either and it tries to find the exact match even using LIKE its not getting it.
Can anyone help me with this - I hope what I'm saying makes sence lol
Posted 05 May 2006 - 04:38 PM
$date1 = "04-15-2006"; $date2 = "05-05-2006"; // first, let's change the dates to a readable format for MySQL: list($month1, $day1, $year1) = explode("-", $date1); list($month2, $day2, $year2) = explode("-", $date2); $date1 = "$year1-$month1-$day1"; $date2 = "$year2-$month2-$day2"; // now, let's run the comparison with taking into account that we // have to change the actual column values into readable dates, too $sql = "SELECT * FROM tableName WHERE DATE(CONCAT_WS("-", SUBSTRING(dateColumn, 7), SUBSTRING(dateColumn, 1, 2), SUBSTRING(dateColumn, 4, 2))) BETWEEN '$date1' AND '$date2'"; $res = mysql_query($sql);
like i said, i'm not positive this will solve it for you as you have it now, but it's worth a try. i would recommend changing the column to a DATE type and formatting your insert to match the SQL DATE format.
Posted 05 May 2006 - 04:51 PM
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users