MySQL: Search Multiple Values Without Using FIND_IN_SET()

Introduction

MySQL provides the FIND_IN_SET() function to search for a value within a comma-separated list.

For example:

FIND_IN_SET('a', 'a,b,c,d')

This works when searching for a single value. However, if we want to search for multiple values at the same time, FIND_IN_SET() cannot directly handle the search in the following format:

FIND_IN_SET('a,b,c,d', 'a,b,c,d')

One way to achieve this is by using multiple FIND_IN_SET() conditions with OR:

FIND_IN_SET('a', 'a,b,c,d')
OR FIND_IN_SET('b', 'a,b,c,d')
OR FIND_IN_SET('c', 'a,b,c,d')

However, when there are many values to search, maintaining this type of query can become difficult.

In this article, we will see a simple way to search multiple values without using FIND_IN_SET().

Prerequisites

Before proceeding, you should have:

• MySQL installed and running
• Basic knowledge of MySQL queries
• A database and table to test the query

Reference:
If you need help setting up MySQL, refer to our guide:

How to setup MySQL and create a user on Ubuntu 24.04?

Implementation

We can use CONCAT() together with REGEXP to search for multiple values.

For example:

SELECT *
FROM table_name
WHERE CONCAT(',', id, ',') REGEXP ',(1|2|3),';

In the above query, 1|2|3 represents the multiple values we want to search for.

The CONCAT() function adds commas before and after the value. This helps ensure that the query matches complete values instead of partial values.

PHP Example

In some cases, the values that need to be searched may come from a PHP array.

For example:

<?php

$a = array(10,12,13,14,15);

$test = "SELECT * FROM table_name WHERE";

$tot = count($a);
$counter = 1;

foreach($a as $val)
{
    $test .= " id=$val";

    if($counter != $tot)
    {
        $test .= " OR ";
    }

    $counter++;
}

echo $test;

mysql_query($test);

?>

For the following array:

$a = array(10,12,13,14,15);

the generated query will be:

SELECT *
FROM table_name
WHERE id = 10
OR id = 12
OR id = 13
OR id = 14
OR id = 15;

The query can then be executed to retrieve the matching records.

Conclusion

FIND_IN_SET() is useful when searching for a single value in a comma-separated list. When multiple values need to be searched, using REGEXP or dynamically generating multiple conditions can make the implementation easier.

For modern PHP applications, avoid the deprecated mysql_* functions used in the original example. Use MySQLi or PDO with prepared statements instead.