Select all rows where all submitted checkbox values exist in comma separated strings in mySQL column

Question

I want to be able to SELECT only rows WHERE all the submitted checkbox values exist in keyWords. My code SELECTS rows with any of the submitted checkbox values.

I've got a mySQL database PhotoArchive with a table photos

| id  | title   |filePath      | keyWords              |
| 1   | mypic1  |imgs/pic1.jpg | Mono,Portrait (etc)   |
| 2   | mypic1  |imgs/pic2.jpg | Colour,Portrait (etc) |
| 3   | mypic2  |imgs/pic2.jpg | Mono,Landscape (etc)  |
| 4   | mypic2  |imgs/pic2.jpg | Colour,Landscape (etc)|

I've got a html page with a checkbox form

<form action="selectFromDb.php" method="get">
<input type="checkbox" value="Mono" name="category[]" id="OptMono" />
  <label for="OptMono"> Monochrome</label>
<input type="checkbox" value="Colour" name="category[]" id="OptColour" />
  <label for="OptColour"> Colour</label>
<input type="checkbox" value="Portraits" name="category[]" id="OptPortraits" />
  <label for="OptPortraits"> Portraits</label>
<input type="checkbox" value="Landscape" name="category[]" id="OptLandscape" />
  <label for="OptLandscape"> Landscape</label>     
<input type="submit" value="Submit" />

and selectFromDb.php file which uses this SELECT statement

 <? foreach($_GET['category'] as $category){
     $categories[] = $category;
   }
  $total_imgs_sql = "SELECT id, title, filePath FROM photos WHERE keyWords LIKE '%$category%'
 ?>

This returns the rows where any of the $category values exist in keyWords. I want to only return the rows WHERE all the $category keyWords exist.

How can this be achieved? (The full version has nearly 200 checkboxes and each row could have around 20 comma separated words in keyWords)
Thanks

Answer 1

You can do this by building a table of category values in your query, and then JOINing that to the photos table, and only selecting photos which return the same number of rows as are in the table of category values. As a sample query, to find photos with Colour and Portrait categories:

SELECT p.*
FROM (
  SELECT 'Colour' AS keyWord
  UNION ALL
  SELECT 'Portrait' AS keyWord
) k
JOIN photos p ON CONCAT(',', p.keyWords, ',') LIKE CONCAT('%,', k.keyWord, ',%')
GROUP BY p.id
HAVING COUNT(*) = 2

Query demo on db-fiddle

In PHP, you could generate this query like so:

$categories = array('Colour', 'Portrait');

$sql = "SELECT p.*\nFROM (\n  SELECT '";
$sql .= implode("' AS keyword\n  UNION ALL\n  SELECT '", $categories);
$sql .= "' AS keyWord\n) k\nJOIN photos p ON CONCAT(',', p.keyWords, ',') LIKE CONCAT('%,', k.keyWord, ',%')\n";
$sql .= "GROUP BY p.id\nHAVING COUNT(*) = " . count($categories);

PHP demo on 3v4l.org

Note I've left in \n to make the output query match the above for readability; you can replace those with (space).

Note also that you may be open to SQL injection, you should check the $category values to ensure they are valid (use a keyword whitelist), or convert to a prepared statement to avoid this. Here's how you can use a prepared statement (assuming you are using mysqli and your connection is in $conn):

$categories = array('Colour', 'Portrait');

$sql = "SELECT p.*\nFROM (\n  SELECT ";
$sql .= implode(" AS keyword\n  UNION ALL\n  SELECT ", array_fill(0, count($categories), '?'));
$sql .= " AS keyWord\n) k\nJOIN photos p ON CONCAT(',', p.keyWords, ',') LIKE CONCAT('%,', k.keyWord, ',%')\n";
$sql .= "GROUP BY p.id\nHAVING COUNT(*) = " . count($categories);

$stmt = $conn->prepare($sql);
$stmt->bind_param(str_repeat('s', count($categories)), ...$categories);
$stmt->execute();
$result = $stmt->get_result();
foreach ($result as $row) {
    print_r($row);
}

Note also that this would be simpler if you had keywords and photo_keywords tables with one line per photo/keyword combination in the latter, rather than using comma separated data (I would strongly suggest reading this Q&A).