Using PHP array to search a column in SQL DB that is separated by commas

TaLeNT Source

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.



answered 3 months ago arczinosek #1

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.

answered 3 months ago user3783243 #2

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.

comments powered by Disqus