multiselect query db

Question

I overthought a recent post and deleted it. Hopefully this version is a little clearer.

I have a multiselect dropdown, which I want to be able to filter a database table with.

So if I have the following drop down with roughly 70 entries:

<select multiple="multiple" id="filter" name="filter">
  <optgroup label="category1"> <!--jquery ui plugin allows for grouping -->
    <option value="sub1">Subcategory 1</option>
    <option value="sub2">Subcategory 2</option>
    <option value="sub3">Subcategory 3</option>
  </optgroup>
  <optgroup label="category2">
    <option value="sub4">Subcategory 4</option>
    <option value="sub5">Subcategory 5</option>
    <option value="sub6">Subcategory 6</option>

...

And I have 6 categories I need to search through in my sql statement, thus so far I have:

<?php
    include ("connect.php");

    $filter = $_POST["filter"];

        $result = mysql_query("SELECT * FROM edt_images
                                WHERE category1= '$filter'
                                OR category2= '$filter'
                                OR category3 = '$filter'
                                OR category4 = '$filter'
                                OR category5 = '$filter'
                                OR category6 = '$filter'")
            or die(mysql_error());
        while ($row = mysql_fetch_array($result)) {
            echo "<img src='files/small/thumb0_".$row['item_name'].".".$row['file_extension']."' border='0'/>"; 
        }
?>

Right now it works if I only select one item from the dropdown because it searches the six columns for that entry. So my question is how would I search these six columns for multiple entries?

Thanks in advance

**WARNING!** Your code contains an [SQL injection vulnerability](http://en.wikipedia.org/wiki/SQL_injection) -- you are passing raw, unfiltered, unvalidated user input directly into an SQL string. SQL injection is [very easy to fix](http://stackoverflow.com/q/60174/168868). Consider [switching to PDO](http://php.net/book.pdo) or [mysqli](http://php.net/book.mysqli) so you can use [prepared statements with parameterized queries](http://en.wikipedia.org/wiki/Prepared_statement). — Charles, Jan 08 '13 at 03:42
yep well aware, looking for more help with the concept. This isn't near final code. — Owen, Jan 08 '13 at 03:43
Also have swapped to PDO, I just find it easier to learn with the old format, and then change it over to PDO — Owen, Jan 08 '13 at 03:43

Jim · Accepted Answer · 2013-01-08T04:09:11.187

2

If filter is an array of chosen categories you can use IN in your query:

$filterIn = implode("','",$filter);

SELECT * FROM edt_images
        WHERE category1 IN ('$filterIn')
        OR category2 IN ('$filterIn')
        OR category3 IN ('$filterIn')
        OR category4 IN ('$filterIn')
        OR category5 IN ('$filterIn')
        OR category6 IN ('$filterIn')

Note however you shouldn't be using unsanitised user input in an SQL query.

edited Jan 08 '13 at 04:09

answered Jan 08 '13 at 03:49

Jim

22,354
6
52
80

1

Helpful answer. One note: I think Owen will also need to change the select to ` – DWright Jan 08 '13 at 04:00
Thanks for this Jim, but how would I go about getting the selections into an array? – Owen Jan 08 '13 at 04:05
Sorry missed DWrights post. however I am receiving: undefined function implode – Owen Jan 08 '13 at 04:07
@Owen If you make the changes DWright and peterm suggested then `$_POST["filter"]` should be an array of chosen categories. – Jim Jan 08 '13 at 04:07
@Owen Bah, I mispelled implode in my post. Fixed. – Jim Jan 08 '13 at 04:09

score 0 · Answer 2 · answered Jan 08 '13 at 04:00

0

IMHO first of all you need to allow to return multiple values from your multiselect. To do so you need to add brackets to name attribute like so:

<select multiple="multiple" id="filter" name="filter[]">

answered Jan 08 '13 at 04:00

peterm

91,357
15
148
157

Ok saw that in some other posts wasn't sure why, so thanks for that – Owen Jan 08 '13 at 04:03

multiselect query db

2 Answers2