Jump to content


Date Search Help !!!

  • Please log in to reply
2 replies to this topic

#1 cheadirl

  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 05 May 2006 - 04:01 PM

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


#2 obsidian

  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 05 May 2006 - 04:38 PM

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:

$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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 craygo

  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 05 May 2006 - 04:51 PM

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


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users