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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Aug 2013
    Posts
    149
    Thanks
    21
    Thanked 0 Times in 0 Posts

    Creating search query

    I have a table with a day, month and year as well as a name. I have a drop down menu that grabs every name from the db as well as all the days/months/years.

    How do I create a query that when you select a day, month, year and name from the drop down menu that it queries and shows only the entries on that specific day, month and year by a specific person.

    Thanks

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    You really have day, month, and year in *SEPARATE* fields in your table???

    Why???

    Yes, that works for selecting a *specific* day/month/year. But it makes queries for (say) "any date from July 17, 2013, through September 3, 2013" really really really tough to do!

    You should have a *SINGLE* field of type DATE!

    Code:
    CREATE TABLE events (
        eventid INT AUTO_INCREMENT PRIMARY KEY,
        eventdate DATE,
        plannerName VARCHAR(50),
        eventName VARCHAR(200),
        ...other fields ...
    ) ENGINE INNODB
    And now, yes, you might have day/month/year as separate fields in your <form>. Perhaps thus:
    Code:
    <form action="seach.php" method="post">
    Day: <input name="day"/> Month: <input name="month"/> Year: <input name="year"/>
    <br/>
    Planner name: <input name="planner"/>
    <br/>
    <input type="submit" value="Search"/>
    </form>
    (And, yes, any and all of those fields I showed there as <input> [only for simplicity] could be <select>s instead, of course. Won't make any difference to PHP/MySQL.)

    And then your PHP code:
    Code:
    <?php
    ... make your db connection ...
    $yr = $_POST["year"];
    $mo = $_POST["month"];
    $dy = $_POST["day"];
    $name = $_POST["planner"]
    if ( ! is_numeric($yr) || ! is_numeric($mo) || ! is_numeric($dy) )
    {
        echo "Year, month, and day must be numbers";
        exit( );
    }
    // MySQL needs dates as Y-M-D:
    $dt = ((int)$yr) . "-" . ((int)$mo) . "-" . ((int)$dy);
    $name = mysql_real_escape_string($name); // or use mysqli equivalent
    
    $sql = "SELECT * FROM events WHERE eventDate = '$dt' AND plannerName='$name'";
    ... and make the query ...
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    7daytheory (08-22-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2013
    Posts
    149
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Im getting an undefined index with all the variables.
    $yr = $_POST["year"];

    So I tried
    if(isset($_POST['preshift_year'])){ $yr = $_POST['preshift_year']; }

    and on that Im getting an undefined variable.

    Been a long day Im missing something obvious you are going to point out... ?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Can't possibly guess the cause without seeing your <form> and its contents.

    My use of $_POST["year"] was just an example, only would work with the example <form> I also provided.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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