Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Searching date from and to...

    I am trying to set up a php search page, I have had no issues with searching my FirstName and LastName fields in my db... But I need to do a search for MembersLogin from 12/12/2006 to 12/12/2007, the dates would be able to be filled in by a user and submitted. However someone told me it's really complicated to do this as my column data type is a DATE field... Apparently a UNIX TIMESTAMP is alot easier to do this kind if search ???

    Anyway it's too late to edit the database now so if anyone has any ideas please let me know!

    Thanks in advance!

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,049
    Thanks
    2
    Thanked 317 Times in 309 Posts
    If your database contains a DATE type column, this could not be any easier. Use the mysql STR_TO_DATE() function with the proper format string to convert your mm/dd/yyyy dates into a DATE type and use the results in a WHERE comparison in your query - http://dev.mysql.com/doc/refman/5.0/...on_str-to-date
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hey cool... Ok well I read the whole page and took a bit in, but I really struggle with php so if you can bear with me would be appreciated...

    My Search Page Currently

    PHP Code:
    <?php

    //connect to db
    include 'library/config.php';
    include 
    'library/opendb.php';

    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <title>Untitled Document</title>
    </head>

    <body>
    <form id="form1" name="form1" method="post" action="memberResults.php">
    <table width="900" border="0" cellpadding="0" cellspacing="0">
      <tr>
        <td>Email
          <input type="text" name="email" /></td>
        <td>&nbsp;</td>
        <td>State 
          <select name="state">
            <option value="nsw">NSW</option>
            <option value="vic">VIC</option>
            <option value="qld">QLD</option>
            <option value="tas">TAS</option>
            <option value="sa">SA</option>
            <option value="act">ACT</option>
            <option value="wa">WA</option>
            <option value="nt">NT</option>
          </select></td>
      </tr>
      <tr>
        <td colspan="3">Below this does not work so don't worry about it</td>
      </tr>
      <tr>
        <td>JoinDateFrom 
          <input type="text" name="joindatefrom" /> (format of yyyy/mm/dd)</td>
        <td>JoinDateTo 
          <input type="text" name="joindateto" /> (format of yyyy/mm/dd)</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td>ApprovedDateFrom 
          <input type="text" name="approveddatefrom" /> (format of yyyy/mm/dd)</td>
        <td>ApprovedDateTo 
          <input type="text" name="approveddateto" /> (format of yyyy/mm/dd)</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td>LastLoginDateFrom 
          <input type="text" name="loginfrom" /> (format of yyyy/mm/dd)</td>
        <td>LastLoginDateTo 
          <input type="text" name="loginto" /> (format of yyyy/mm/dd)</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td><input type="submit" name="Submit" value="Search" /></td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
    </table>
    </form>
    </body>
    </html>

    <?php
    //close the connection
    include 'library/closedb.php'

    ?>
    And my result page... Or atleast up too the query...


    PHP Code:
    /*set varibles from form */
    $firstName trim($_POST['firstname']);
    $lastName trim($_POST['lastname']);
    $email trim($_POST['email']);
    $rep $_POST['rep'];
    $state $_POST['state'];
    $status $_POST['status'];


    $results mysql_query("SELECT * FROM `tblmembers` WHERE `FirstName` LIKE '%$firstName%' AND `LastName` LIKE '%$lastName%` AND `Email` LIKE '%$email%' AND `State`='$state' AND `mng`='$rep' AND `MemberApproved`='$status' AND `loginDateTime` LIKE ('$joinDateTo'-'$joinDateFrom')") or die ('Error: '.mysql_error ());


    /* Display the results found and what column it was found in */
    ?> 
    Hmm yea I'm not getting it... I read this post and thought I could make sense of it but no luck... Is my query above kind of thing on the right track? I was working from the below example...

    PHP Code:
    SELECT nick FROM `usersWHERE TO_DAYSNOW( ) ) - TO_DAYSlast_login ) <=AND DATE_FORMATCURRENT_TIMESTAMP( ) , '%H%i' ) - DATE_FORMATlast_login'%H%i' ) <=5 ORDER BY `nickASC

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Anyone? I'm kind of desperate...

  • #5
    New Coder
    Join Date
    Jul 2007
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I use a simple greater than and less than, wouldn't something like the following work for you?

    PHP Code:
    $early_date "2006-12-12";
    $later_date "2007-12-12";
    $sql mysql_query("SELECT * FROM users WHERE date > '$early_date' && date < '$later_date'");

    //this next part is just to show how many rows for testing
    $rows mysql_num_rows($sql);
    echo 
    $rows
    Thats what I use.


    so your code might be:


    PHP Code:
    $results mysql_query("SELECT * FROM `tblmembers` WHERE `FirstName` LIKE '%$firstName%' AND `LastName` LIKE '%$lastName%` AND `Email` LIKE '%$email%' AND `State`='$state' AND `mng`='$rep' AND `MemberApproved`='$status' AND `loginDateTime` > '$joinDateTo' AND 'loginDateTime' < '$joinDateFrom'") or die ('Error: '.mysql_error ()); 
    Last edited by phpandmysql; 07-10-2007 at 03:50 PM.
    PHP and MYSQL
    $string = "3Ip*hKEpanKI#8dmUys&*KqlIJ*P8D";
    $new_string = ereg_replace("[^a-z]", "", $string);
    echo "<a href=http://www.$new_string.org>$new_string</a>";

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    hey there that's great! i was so excited when i got it to work... a couple of questions though... say if i have this query just as a test

    PHP Code:
    /*set varibles from form */
    $early_date "2005-08-22"
    $later_date "2005-08-24"
    $sql mysql_query("SELECT * FROM `tblmembers` WHERE (`JoinDate` > '$early_date' && `JoinDate` < '$later_date')"); 

    //this next part is just to show how many rows for testing 
    $rows mysql_num_rows($sql); 
    echo 
    $rows
    it gives me the results for 2005-08-23, is there any way to include the early date and later date in the range?

  • #7
    Regular Coder
    Join Date
    Apr 2009
    Posts
    135
    Thanks
    83
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tomyknoker View Post
    hey there that's great! i was so excited when i got it to work... a couple of questions though... say if i have this query just as a test

    PHP Code:
    /*set varibles from form */
    $early_date "2005-08-22"
    $later_date "2005-08-24"
    $sql mysql_query("SELECT * FROM `tblmembers` WHERE (`JoinDate` > '$early_date' && `JoinDate` < '$later_date')"); 

    //this next part is just to show how many rows for testing 
    $rows mysql_num_rows($sql); 
    echo 
    $rows
    it gives me the results for 2005-08-23, is there any way to include the early date and later date in the range?
    PHP Code:

    $sql mysql_query("SELECT * FROM `tblmembers` WHERE (`JoinDate` >= '$early_date' && `JoinDate` <= '$later_date')"); 


  •  

    Posting Permissions

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