SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Searching database for multiple items in one column

    I am working on an advanced search form. A few of the items in the form are multiple select fields. At first I had it working so that if you just choose one field, it will give the correct results. But now I'd like it so that if you select multiple fields it searches through that column in the database for each selection. For the example, I'd like to use my state_province[] form field.

    The variable $state_province is passed in the URL as via the $_GET method. I then grab the variable
    PHP Code:
    if ($_GET['state_province[]'])
               
    $state_province implode(', '$_GET['state_province[]']); 
    Then when I start creating my sql search I do this
    PHP Code:
    if(strlen($state_province) > 0){
        if(
    $state1$sql .= " AND";
        foreach (
    $_GET['state_province[]'] as $state_province) {
            
    $sql .= " MATCH state_province AGAINST ('$state_province')";
        } 
        
    $state2 true;

    I've been reading about this for a while and I tried messing with changing field types to TEXT, ENUM, SET, BLOB, and VARCHAR of course, but I just can't seem to get anything working. I searched on the forums here but mostly just found stuff on sorting by relevance, which is not what I want. It's just not returning any results for me whatsoever, even if I just search for one item in the list.

    Any help would be greatly appreciated, I'm really confused!

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But now I'd like it so that if you select multiple fields it searches through that column in the database for each selection.
    Post an example of a record in your table and an example of what you want to retrieve.

  3. #3
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 7stud
    Post an example of a record in your table and an example of what you want to retrieve.
    There are about 30 or 40 fields per record, so that may not be the best idea. Let me try to explain it a little better. I have a table in a database that holds classified ad information. One of the fields contains the state that the item is located in. In my search field I would like the user to be able to select multiple states to search for items in. For example, they can select Michigan, New Mexico, and California and all items located in Michigan, all items located in New Mexico, and all items in California would be in the search results. How do I make that happen?

    P.S. I'll try to get a screen shot or something of one of the records.

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this:
    PHP Code:
    $sql 'SELECT something WHERE';

    $states = array('Texas''Maine');
    foreach(
    $states as $state)
    {
        
    $sql .= " state='$state' OR";
    }

    $sql rtrim($sql"OR");
    echo 
    $sql

  5. #5
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i have a feeling this:
    $_GET['state_province[]']

    should be this

    $_GET['state_province']


    if your input names are like so

    name="state_province[]"

    then you access it like
    $_GET['state_province']

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The fastes way would be to retrieve the id of the state.

    In your select input make sure you have "multiple" as an attribute.

    <select multiple name="state_province[]">
    <option value="234">New Mexico</option>
    <option value="534">Texas</option>
    </select>

    Then in your query you could so something like:

    PHP Code:
    $sql "SELECT * FROM table INNER JOIN states ON states.id = table.states_id
    WHERE states.id IN ("
    .implode(','$_GET[state_province]).")" 
    Obviously you would want to filter the $_GET content first.
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  7. #7
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I decided to test it on a multiple select field with fewer options than the state_province one. So I'm doing this with horse gender.

    search_form
    PHP Code:
    <select name="horse_sex[]" style="width:180px;" multiple size="4">
                    <
    option value="">-- Any Sex --</option>
                    <
    option value="Gelding">Gelding</option>
                    <
    option value="Mare">Mare</option>
                    <
    option value="Stallion">Stallion</option>
                  </
    select
    search_results
    PHP Code:
    $sex $_GET['horse_sex[]'];
    $sexes = array('Gelding''Mare''Stallion');

    if(
    strlen($sex) > 0){
        if(
    $state1 OR $state2 OR $state3 OR $state4$sql .= " AND";
        foreach(
    $sexes as $sex)
    {
        
    $sql .= " horse_sex = '$sex' OR";
    }
        
    $sql .= rtrim($sql"OR"); 
        
    $state5 true;

    Searching for one selected item works fine. When I select two items, it returns search results only for the item in the bottom of the selected list. For example, if I want to search for mares and stallions and select those two, it only returns results for stallions. So something isn't working right in the foreach loop.

    What do you think?

  8. #8
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Reread this:
    Quote Originally Posted by clamcrusher
    i have a feeling this:
    $_GET['state_province[]']

    should be this

    $_GET['state_province']


    if your input names are like so

    name="state_province[]"

    then you access it like
    $_GET['state_province']
    i.e. $anArrayContainingTheSelectedStates = $_GET['state_province'];

  9. #9
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried that and it still does the same thing

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried that and it still does the same thing
    What do you get when you choose a couple of options from the select you posted and the action page contains this php:
    PHP Code:
    $sex $_GET['horse_sex'];

    echo 
    "<pre>";
    print_r($sex);
    echo 
    "</pre>"

  11. #11
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 7stud
    What do you get when you choose a couple of options from the select you posted and the action page contains this php:
    PHP Code:
    $sex $_GET['horse_sex'];

    echo 
    "<pre>";
    print_r($sex);
    echo 
    "</pre>"
    It give me this..

    Array
    (
    [0] => Mare
    [1] => Stallion
    )

    But also returns no results. It will return "stallion" related results if I use $_GET['horse_sex[]'] but nothing at all for $_GET['horse_sex']

  12. #12
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It give me this..

    Array
    (
    [0] => Mare
    [1] => Stallion
    )
    That means $sex is an array. Should you be searching your table for an array?

  13. #13
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think i've got it narrowed down. It's having trouble with the OR in this part
    PHP Code:
        foreach($sexes as $sex)
    {
        
    $sql .= " horse_sex = '$sex' OR";
    }
        
    $sql .= rtrim($sql"OR"); 
    It's now telling me

    "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORSELECT * FROM text_horse WHERE horse_sex = 'Gelding' OR horse"

  14. #14
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can echo your sql statement out before you query your database so that you can examine it and find any errors. However, you should be able to see the errors in your sql statement from the erorr message:

    'ORSELECT * FROM text_horse WHERE horse_sex = 'Gelding' OR horse"
    Why don't you try writing out the sql statement you want based on a couple of chosen options, query your database to make sure the sql statement works correctly, and then try to put together the sql statement with php code. Don't query the database with the sql statement that you put together with php code--just echo it out. Once you get the exact same query with php code as the one you wrote out and tested, then you can add code to query your database.

  15. #15
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, I've been trying that a bit off an on today, but I just can't get it to work. I guess maybe I'm not understanding the rtrim function properly. How do I make sure that as it doesn't tack on an "OR" at the end if there isn't another selected option?

  16. #16
    SitePoint Enthusiast
    Join Date
    Sep 2005
    Posts
    44
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sunnydayz
    Alright, I've been trying that a bit off an on today, but I just can't get it to work. I guess maybe I'm not understanding the rtrim function properly. How do I make sure that as it doesn't tack on an "OR" at the end if there isn't another selected option?
    What you have done is essentially right. I can't see any error in the code you posted, but I think there is a problem with the code before or after it.

    The rtrim() function will remove any characters from the right of a string that matches the specified characters.
    so if you have $str = "HelloORRROOOORRR";

    echo rtrim($str, 'OR'); // will print "Hello".

    In your case this should not be a problem and should still work as intended.

    The "ORSELECT" from your code suggests there is a probelm with what preceeds it. And the fact that you have "horse" at the end suggests there is another problem after it.

    Try this code
    PHP Code:
    foreach($sexes as $sex)
    {
        
    $sql .= " horse_sex = '$sex' OR";
        echo 
    "$sql<br>";
    }
        
    $sql .= rtrim($sql"OR"); 
        echo 
    "$sql<br>"
    And come back and post the output here.
    www.SQLrecipes.com A free cookbook for SQL recipes.

    I didn't believe someone could make over $19,000 a month...
    ...with Google Adsense, until I read this.

  17. #17
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where does the 'OR' in 'ORSELECT' comes from at all?
    Doesn't that mean that the SELECT is within a (pre-)loop?
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  18. #18
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, here's what I've got now

    PHP Code:
    $sex $_GET['horse_sex'];
    $sexes = array('Gelding''Mare''Stallion');
    if(
    strlen($sex) > 0){
        if(
    $state1 OR $state2 OR $state3 OR $state4$sql .= " AND";
        foreach(
    $sexes as $sex)
    {
        
    $sql .= " horse_sex = '$sex' OR";
    }
        
    $sql rtrim($sql"OR");
        
    $state5 true;

    The query echoed is
    "SELECT * FROM text_horse WHERE horse_sex = 'Gelding' OR horse_sex = 'Mare' OR horse_sex = 'Stallion'"
    which is not good, since with that run I only selected mare.

    The problem now is that no matter how many items I select, it will search for results that contain at least one of all 3 options. So if I select "mare" it will give me all geldings, mares, and stallions, instead of just giving me mares only. So there's got to be something wrong in that for each loop because it's not checking to make sure that what's selected is actually in the $_GET variable.

  19. #19
    SitePoint Enthusiast sunnydayz's Avatar
    Join Date
    Feb 2006
    Location
    Iowa
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally got it to work!

    Here's what I ended up with
    PHP Code:
    $sex $_GET['horse_sex'];
    $sexes = array("Gelding""Mare""Stallion");
    if(
    strlen($sex) > AND $sex !=""){
        if(
    $state1 OR $state2 OR $state3 OR $state4$sql .= " AND";
         
    $sexes = array('Gelding''Mare''Stallion');
    // check that all elements in $_GET['horse_sex'] are in $sexes
    if (count(array_diff($_GET['horse_sex'], $sexes)) == 0) {
        
    $sql .= " horse_sex IN ('" implode("','"$_GET['horse_sex']) . "')";
    }
        
    $state5 true;

    Thank you so much for everyone's help. I greatly appreciate it!


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
  •