Multiple Form checkbox results used in mysqli select

Question

I want to integrate checkboxes with my SQL select statement but I'm not sure how to convert the array into a usable select.

    <form method="post">
    <input name="searchterm" type="search" placeholder="Enter Search Terms Here" />
    <input name="searchbtn" type="submit" value="Karaoke Search" />
    <input name="checkbx[]" type="checkbox" checked value="Chartbuster" />
      <label>Chartbuster</label>
    <input name="checkbx[]" type="checkbox" checked value="Sound Choice" />
      <label>Sound Choice</label>
    <input name="checkbx[]" type="checkbox" checked value="DKKaraoke" />
      <label>DKKaraoke</label>
    <input name="checkbx[]" type="checkbox" checked value="Sunfly" />
      <label>Sunfly</label>
    <input name="checkbx[]" type="checkbox" checked value="Karaoke Hits" />
      <label>Karaoke Hits</label>
    <?php
      if(isset($_POST['searchterm']) and ($_POST['searchterm']!="")) {
        $searchterm=$_POST['searchterm'];
        $checkbx=$_POST['checkbx'];
        $searchresults=$db->query("SELECT *
                                   FROM 1KaraokeDJ
                                   WHERE Artist LIKE '%$searchterm%'
                                   GROUP BY Artist,
                                            Title,
                                            Brand  
                                   ORDER BY Artist,
                                            Title,
                                            Disc LIMIT 100");
      }
    ...
    ?>
     ...
</form>

Basically, limit the search where Brand in one of the selected checkboxes

just use mysql IN () statement like Brand IN (join(',', $checkbx)) link (http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause) — Nikhil Chaudhary, Nov 28 '15 at 15:31
SELECT * FROM 1KaraokeDJ WHERE Artist LIKE '%$searchterm%' AND Brand IN (join(',',$checkbx)) GROUP BY Artist, Title, Brand ORDER BY Artist, Title, Disc LIMIT 100 - produces no results — Daniel Yantis, Nov 28 '15 at 15:42
$checkbx = join(',',$checkbx); plus AND Brand IN $checkbx also produces no results - s — Daniel Yantis, Nov 28 '15 at 15:52

score 1 · Accepted Answer · answered Nov 28 '15 at 15:02

1

You can use this (although its not the best approach if you are concerned about security in your app)

$checkbx = join(',',$checkbx);

And then this variable should be suitable for your query.

Also please remove round brackets, they are not needed:

$searchterm=($_POST['searchterm']);
$checkbx=($_POST['checkbx']);

to become

$searchterm=$_POST['searchterm'];
$checkbx=$_POST['checkbx'];

answered Nov 28 '15 at 15:02

pavlovich

1,935
15
20

So basically you convert the array to a string combining with ",". I understand that, now the sql select should have Brand = $checkbx? But no that will not work... – Daniel Yantis Nov 28 '15 at 15:18
This conversion from array to a string is great, but adding AND Brand IN $checkbx to select produces no results. – Daniel Yantis Nov 28 '15 at 23:49

score 0 · Answer 2 · answered Nov 29 '15 at 13:43

After much headache I stumbled across something that is not added to any answer in similar questions...

Select IN ('a','b','c'...) requires "'" around each text option. In all the examples it assumes numbers!

$checkbx = join(',',$checkbx); did not work at first because each text item in the array must have "'" before joining.

Thus, the solution for me (maybe better solutions out there) was to add "'" to each of my checkbox values:

<input name="checkbx[]" type="checkbox" value="'Chartbuster'" />
  <label>Chartbuster</label>
<input name="checkbx[]" type="checkbox" value="'Sound Choice'" />
  <label>Sound Choice</label>
<input name="checkbx[]" type="checkbox" value="'DKKaraoke'" />
  <label>DKKaraoke</label>
<input name="checkbx[]" type="checkbox" value="'Sunfly'" />
  <label>Sunfly</label>
<input name="checkbx[]" type="checkbox" value="'Karaoke Hits'" />
  <label>Karaoke Hits</label>
<?php
  if(isset($_POST['searchterm']) and ($_POST['searchterm']!="") and isset($_POST['checkbx'])) {
    $searchterm=$_POST['searchterm'];
    $checkbx=join(',',$_POST['checkbx']);
    $searchresults=$db->query("SELECT *
                               FROM 1KaraokeDJ
                               WHERE Artist LIKE '%$searchterm%'
                               AND Brand IN ($checkbx)
                               GROUP BY Artist,
                                        Title,
                                        Brand  
                               ORDER BY Artist,
                                        Title,
                                        Disc
                               LIMIT 100");
      }
    ...
    ?>
     ...

Multiple Form checkbox results used in mysqli select

2 Answers2