SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    searching on date?

    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
    PHP Code:
    $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 " 
    success is not by chance, it is by choice.

  2. #2
    American't awestmoreland's Avatar
    Join Date
    Sep 2002
    Location
    Grand Rapids, MI
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    PHP Code:
    $search " DateJoined LIKE '".$newdate."%'"
    Andy
    From the English nation to a US location.

  3. #3
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    success is not by chance, it is by choice.

  4. #4
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    success is not by chance, it is by choice.

  5. #5
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    success is not by chance, it is by choice.

  6. #6
    American't awestmoreland's Avatar
    Join Date
    Sep 2002
    Location
    Grand Rapids, MI
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    From the English nation to a US location.

  7. #7
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    $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
    success is not by chance, it is by choice.

  9. #9
    SitePoint Member
    Join Date
    Oct 2001
    Location
    United Kingdom
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?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);
    ?>

  10. #10
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    success is not by chance, it is by choice.

  11. #11
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry, I meant I used BETWEEN instead
    success is not by chance, it is by choice.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •