SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    NYC
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help is needed with Date Search

    Helllo Friends,

    I have searched regarding this issue and have gotten some help from previously posted threads but still cannot get this going and would appreciate it if someone can help me with this.

    I would like to implement search by date. So the user would insert the search date in this format: mm/dd/yyyy.

    and the search field on the for is called 'search_date';
    My code:
    PHP Code:

    $conn 
    mysql_connect($dbhost$dbuser$dbpass) or die (mysql_error('CANNOT CONNECT'));
    $db mysql_select_db($dbname$conn) or die (mysql_error('CANNOT CONNECT TO DB'));

    //CURRENT DATE
    $curdate date("m-d-Y"); // current date in dd/mm/yyyy

    //SEARCH DATE ENTERED BY USER
    $search_date trim($search_date);

    //TERNARY CONDITION
    $search_date = isset($_GET['search_date']) ? $_GET['search_date'] : $curdate;

    // SELECT STATEMENT

    $sql "SELECT * FROM $tblname";

    //APPENDING THE BASE QUERY WITH USER SEARCH DATE

    $sql .= "WHERE `date_created`=" .$search_date;
    $result mysql_query($sql);

    //WHILE LOOP STARTS HERER        
            
    while ($rows mysql_fetch_array($result))
            {
    //$date_created being assigned to date_created db column

    $date_created $rows['date_created'];

    //DISPLAYING DATE IN FOLLOWING FORMAT

    $date_created date("m \- d \- Y"strtotime($rows['date_created']));

    echo 
    $date_created;

    Any help would highly be appreciated.

    Thanks a lot.

  2. #2
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Out of curiosity, why don't you just store all dates in the DB as a timestamp?

    Then you can use DATE_FORMAT() for searching
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  3. #3
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    NYC
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello Triexa,

    Yes, dates are being stored as timestamp.

  4. #4
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So $search_date is mm/dd/yyyy (like 10/04/2007 for today?)

    Then you just would use:
    Code:
    WHERE DATE_FORMAT(date_created, '%m/%d/%Y') = '$search_date'
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter

  5. #5
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    NYC
    Posts
    306
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey triexa,

    Actually, date is stored in $db in this format:

    2007-03-29 14:40:18

    This is what I have been trying now with your suggested code:

    PHP Code:
    $sql "SELECT * FROM `$tblname`";
    $sql .= "WHERE DATE_FORMAT(`date_created`, 'm/%d/%Y') = '$date'";    
    $result mysql_query($sql);

    while (
    $rows mysql_fetch_array($result))
    {
    $date_created $rows['date_created'];
    $date_created date("m \- d \- Y"strtotime($rows['date_created']));

    echo 
    $date_created

    Still no show.

  6. #6
    SitePoint Wizard triexa's Avatar
    Join Date
    Dec 2002
    Location
    Canada
    Posts
    2,476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are getting no rows?

    echo the "WHERE..." line and make sure everything looks as it should. Also, try just selecting the date_format (not searching for it), and see how it looks and if there even is any row that matches ur search...
    AskItOnline.com - Need answers? Ask it online.
    Create powerful online surveys with ease in minutes!
    Sign up for your FREE account today!
    Follow us on Twitter


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
  •