searching on date?

[Sahajin]

I have members that get inserted into the db and all have a join date as a timestamp.

I want to be able search this field just by entering any of the following

2004
2004-4
2004-4-2

or any variation of those. how do I compare it to a timestamp? is it even possible.

I have this and it works but it only get all members for the date and teh time is 12AM, no other times in that day

$getdate = explode("-",$_POST['search_value']);
        $newdate = mktime(0,0,0,$getdate[1],$getdate[2],$getdate[0]);
        $search = " MATCH (DateJoined) AGAINST ('$newdate')";

"select * from members where $search " 

[awestmoreland]

I don't know whether this is good or bad from a performance perspective, but what happens if you change your $search to the following?

$search = " DateJoined LIKE '".$newdate."%'"; 

Andy

[Sahajin]

well, it actually does the samething. I think it has a lot to do with the fact that in mktime I do not set the hours or minutes.

[Sahajin]

this actually seems to work. but it still gets dates that are close to yesterday. if you search for 2004-4-15 it will give 04-14-2004 10:55:45 PM

so it maybe a couple of hours off but it is close enough. at least it works

[Sahajin]

well a coupld of hours plus a few days. it only returns the first day of the month if I enter 2004-04. it should get all of the mopnths members. but instead it gets jus tthe first day.

[awestmoreland]

The problem is that you're telling us what you're inputting rather than what you're trying to compare. I'm assuming that the contents of $newdate are something like 20040416012735.

Andy

[Owen]

Try the function strtotime (http://www.php.net/manual/en/function.strtotime.php). It will (very) intelligently convert user inputted dates and times into a format PHP can use. My favorite under-utilized PHP function.

Owen

[Sahajin]

read the very first line of this thread, I am comparing it to a timestamp. if you look I also used mktime which will convert it to a timestamp. strtotime doesn't work if the user only uses a year and month.

the contents of newdate will be a timestamp or a small version of it. I tried to substr() the timestamp so it will take off the last 3-4 numbers so I can get something to compare it to.

I am currently using

$getdate = explode("-",$_POST['search_value']);
        if($getdate[2]){            
            $timeformat = "\"%Y-%m-%d\"";
        } else {           
            $timeformat = "\"%Y-%m\"";
        }
$search = " FROM_UNIXTIME(DateJoined, $timeformat) = '".$_POST['search_value']."'";
$Q = "select * from $members where $search ";
    $db_view->query($Q); 

but this also produces the same effect. I search for 2004-04 it will return those dats but only dates that are at midnight. (12AM)

using strtotime on a user input of 2004-04 will not produce any results I can use

[rw22]

<?php
function getTimestamp($year, $month = 1, $day = 1, $hours = 0, $minutes = 0, $seconds = 0) {
    return mktime($hours, $minutes, $seconds, $month, $day, $year);
}

$getdate = explode("-", $_POST['search_value']); 

$year = isset($getdate[0]) ? $getdate[0] : false;
$month = isset($getdate[1]) ? $getdate[1] : false;
$day = isset($getdate[2]) ? $getdate[2] : false;

$start = getTimestamp($year, $month, $day);

if (!$month) {
  
  $end = getTimestamp(($year + 1));
} 
elseif (!$day) {
  
  $end = getTimestamp($year, ($month + 1));
}
else {
  
  $end = getTimestamp($year, $month, ($day + 1));
}

$search = 'DateJoined > ' . $start . ' AND DateJoined < ' . $end; 
$Q = "select * from $members where $search";
$db_view->query($Q);
?>

[Sahajin]

I think that will do tha tsamething. besides I have found that match...against is fast than comparing < and >. but I will have a go wit hit thanks.

[Sahajin]

sorry, I meant I used BETWEEN instead