PHP: Select each value in Array

Question

So I have my form and want to retrive all rows that has been checked off. But I can't get my LIKE to work as i want to.. It only works of one checkbox is checked off. I want to show all the rows that has each value. So if you check off "Rock" and "Jazz", it will show all the rows whit either "Rock" Or "Jazz". And that rows whit "Rock, Electronic" also will show up.

Should i have a different aproach to this?

<?php
if(isset($_POST['submit'])){
  if (isset($_POST['genre'])) {

    $genre_str = implode(",", $_POST['genre']);

    $sql = $dbcon->prepare('SELECT * FROM user_profile WHERE user_genre LIKE :user_genre');
    $sql->bindvalue(':user_genre', "%".$genre_str."%");
    $sql->execute();

    foreach ($sql as $row){
    $row_id = htmlspecialchars($row['id']);
    $row_genre = $row_post['user_genre'];
    echo "id: " . $row_id . " = " . $row_genre;
    echo "<br/>";
    }
  }

}
?>

<form action="" method="post">
  <h4 class="text-center">Genre</h4>
  <div class="checkbox">
    <label class="checkbox-inline"><input type="checkbox" name="genre[]" value="Rock">Rock</label>
    <label class="checkbox-inline"><input type="checkbox" name="genre[]" value="Funk">Funk</label>
  </div>

  <div class="checkbox">
    <label class="checkbox-inline"><input type="checkbox" name="genre[]" value="Jazz">Jazz</label>
    <label class="checkbox-inline"><input type="checkbox" name="genre[]" value="Hip-Hop">Hip-Hop</label>
  </div>

  <div class="checkbox">
    <label class="checkbox-inline"><input type="checkbox" name="genre[]" value="Acoustic">Acoustic</label>
    <label class="checkbox-inline"><input type="checkbox" name="genre[]" value="Electronic">Electronic</label>
  </div>

  <div class="col-md-12">
    <input type="submit" name="submit" class="btn btn-block btn-success" value="Search">
  </div>
</form>

What types of values are stored in user_genre? Single values, or comma separated lists? — Don't Panic, Apr 19 '17 at 21:08
[Please read this.](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) LIKE can't work for this because it's going to look for the entire value between the wildcard characters, and unless that exact value is found somewhere in user_genre, it won't match. — Don't Panic, Apr 19 '17 at 21:15
you need to loop through each genre received via form and do like `user_genre LIKE %:each_user_genre%` and push result of all individual genre in an result array and then loop through the result array and print. % operator on start and end in where clause means any string on start and end but match with one that you get from your form — BetaDev, Apr 19 '17 at 21:17
I would recommend creating another table for user_genre, mapping multiple genre rows to each user_profile. It will make queries like this much easier. — Don't Panic, Apr 19 '17 at 21:18
If user_genre is defined as a SET (column type), you can use [`FIND_IN_SET()`](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set) — Dygnus, Apr 19 '17 at 21:18

Touheed Khan · Answer 1 · 2017-04-22T04:34:28.193

0

You can use find_in_set().

SELECT * FROM user_profile WHERE find_in_set('jazz', user_genre);

example:

SELECT *
FROM `test`
WHERE find_in_set( 'jazz', user_genre)
AND find_in_set( 'sufi', user_genre )

Tip : you can use loop for getting multiple find_in_set() depending on the number of checkbox selected.

edited Apr 22 '17 at 04:34

answered Apr 20 '17 at 04:34

Touheed Khan

2,149
16
26

PHP: Select each value in Array

1 Answers1