Advanced Search

Results 1 to 3 of 3

Thread: searching multiple fields

  1. #1
    Join Date
    May 2010
    Location
    Sacramento, CA
    Posts
    91
    Thanks
    23
    Thanked 2 Times in 2 Posts

    Default searching multiple fields

    I have a form with drop menu's where i'd like to search a table for 3 things.
    -employee
    -location
    -date

    here is my code:
    Code:
    $query = "SELECT * FROM dar WHERE date='date' AND employee='$employee' AND location='$location'";
    It only works if you select a value for all 3 drop menu's.

    My question: How can i make the SELECT query still work if the user does not select a value for all of the drop menu's on the form? IE: I only pick a date and hit submit...

    Thanks for the help!

    Cory

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,156
    Thanks
    262
    Thanked 690 Times in 678 Posts

    Default

    You can't.
    You'll need to dynamically generate the conditions (everything after WHERE) based on what you have:
    PHP Code:
    $conditions = array('date','employee','location');

    foreach(
    $conditions as $c) {
      if isset($
    $c) {
        
    $condition[] = $c."='".$$c."'";
      }
    }
    $c = isset($c)?'WHERE '.implode(' AND ',$c):'';

    $query 'blah blah blah'.$c.';'//becomes BLAH BLAH BLAH WHERE X=...........; 

    That's messy, but I think it will work.
    It uses the kinda ugly $$ format which references a dynamically named variable (or variable variable):
    $a = 'b';
    $b = 1;
    echo $$a; //echos '1'



    The best way to redesign all of this is to deal with the information already in an array then use implode like I did above. Simpler, but that should patch what you currently have.

    By the way, I set it up to handle nothing sent so you don't ahve a where statement at all. You could also refuse the query if no value was sent.

    Note also that I added a semicolon to end the query. While this isn't required for one-statement MySQL queries, it's a good idea for security reasons: it helps ensure that no one is injecting something malicious at the end of the query. (It's not foolproof, but it helps along with other things like escaping the data.)
    Daniel - Freelance Web Design | <?php?> | <html>| español | Deutsch | italiano | português | català | un peu de français | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  3. #3
    Join Date
    May 2010
    Location
    Sacramento, CA
    Posts
    91
    Thanks
    23
    Thanked 2 Times in 2 Posts

    Default

    i found a tutorial that explains 'prepared' statements really well.

    It is a short and sweat way to create dynamic query's, thanks for the input!

    http://forum.codecall.net/php-tutori...tatements.html

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
  •