-1

I have a column in my DB labeled providers. This column can have multiple values, i.e (1,2,3,4,5) or (14,2,9,87). I have an array that is also filled with similar values i.e (1,9,7,3) and so forth.

I am trying to query my DB and return results from the table where any of the values in the variable array match the values split by commas in the column.

This is what I have.

$variable = "1,9,3,4"; $sql = "SELECT id, provider FROM table_name WHERE FIND_IN_SET(provider, '$variable')";

However, this is not working. If the column in the DB has more then one value, it returns nothing. If the column only has one value, it returns it fine.

Aram Grigoryan
  • 740
  • 1
  • 6
  • 24
TaLeNT
  • 5
  • 3
  • 1
    This one of the reasons why you should [normalize your database](https://en.wikipedia.org/wiki/Database_normalization). – Jeff Jun 24 '18 at 20:28
  • 1
    [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Jeff Jun 24 '18 at 20:30
  • @Jeff Unfortunately, I did not ask about normalization. That is not an option currently but I am working on it. In the meantime, the question remains the same. – TaLeNT Jun 24 '18 at 20:34
  • 1
    @Jeff nevermind, i sat thinking about it and may have found a way to normalize. – TaLeNT Jun 24 '18 at 20:53
  • you will prevent yourself from a lot of struggle when you do that first! Happy coding! – Jeff Jun 24 '18 at 21:04

2 Answers2

0

I'm not sure, but LOCATE should solve your problem. Example:

$sql = "SELECT id, provider FROM table_name WHERE LOCATE('$variable', provider) = 1;";

but not works if order of ids is different.

arczinosek
  • 139
  • 5
  • LOCATE is not working. returns either every string in DB table, or nothing. – TaLeNT Jun 24 '18 at 20:33
  • or you can use FIND_IN_SET but for every one element and concat this with AND. `$fis = []; foreach(explode(',', $variable) as $id) $fis[] = "FIND_IN_SET($id, provider)"; $sql .= implode(' AND ', $fis);` – arczinosek Jun 24 '18 at 20:37
0

The CSV should be the second parameter of your find_in_set. The first should be the single value you are searching for. So you should split $variable into multiple values. Something like this:

$variable = "1,9,3,4";
$values = str_getcsv($variable);
foreach($values as $value) {
    $sql = "SELECT id, provider FROM table_name WHERE FIND_IN_SET($value, provider)";
    //execute $sql here
}

should do it.

With your previous approach the find_in_set was looking for 1,9,3,4, not 1, 9, 3, or 4, as you had wanted. The manual also states the behavior using the function that way won't work.

This function does not work properly if the first argument contains a comma (,) character.

You should update the table in the future when you have time so it is normalized.

user3783243
  • 5,368
  • 5
  • 22
  • 41