Searching database for multiple items in one column

[sunnydayz]

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

if ($_GET['state_province[]'])
           $state_province = implode(', ', $_GET['state_province[]']); 

Then when I start creating my sql search I do this

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!

[7stud]

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.

[sunnydayz]

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.

[7stud]

Try something like this:

$sql = 'SELECT something WHERE';

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

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

[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']

[wwms]

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:

$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.

[sunnydayz]

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

<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

$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?

[7stud]

Reread this:

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'];

[sunnydayz]

I tried that and it still does the same thing

[7stud]

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:

$sex = $_GET['horse_sex'];

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

[sunnydayz]

What do you get when you choose a couple of options from the select you posted and the action page contains this php:

$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']

[7stud]

It give me this..

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

That means $sex is an array. Should you be searching your table for an array?

[sunnydayz]

I think i've got it narrowed down. It's having trouble with the OR in this part

    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"

[7stud]

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.

[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?

[wwms]

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

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.

[frezno]

Where does the 'OR' in 'ORSELECT' comes from at all?
Doesn't that mean that the SELECT is within a (pre-)loop?

[sunnydayz]

Alright, here's what I've got now

$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.

[sunnydayz]

Finally got it to work!

Here's what I ended up with

$sex = $_GET['horse_sex'];
$sexes = array("Gelding", "Mare", "Stallion");
if(strlen($sex) > 0 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!