Jump to content

Archived

This topic is now archived and is closed to further replies.

cheadirl

Date Search Help !!!

Recommended Posts

Hi ,

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

Thanks
Wayne

Share this post


Link to post
Share on other sites
hey, wayne, the problem is that in SQL, a DATE or DATETIME field (which you need in order to run a search between dates) is formatted as "YYYY-MM-DD", not the other way around. now, there may be a workaround for you, but it also may cause some unnecessary overhead on your server. try something like this out and see what happens:

[code]
$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);
[/code]

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.

Share this post


Link to post
Share on other sites
Another thing to go with obsidian's post, If you are getting the current date and/or time from php and inserting it into mysql, there in no need to have the field a timestamp. Just make it a date and format your date as YYY-MM-DD

Ray

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.