Jump to content


Photo

Date Search Help !!!


  • Please log in to reply
2 replies to this topic

#1 cheadirl

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

Thanks
Wayne

#2 obsidian

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.

<?php
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

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

Ray




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users