What is the syntax for searching with array as parameter in Mysql?

Question

What is the correct syntax, if possible, to pass an array as parameter, using the FIND_IN_SET more or less in this idea:

SELECT c.cpf FROM tbl_cliente as c WHERE FIND_IN_SET(array, c.cpf);

Answer 1

The function FIND_IN_SET() query a text (first parameter) in a comma-separated list of elements declared in another String (second parameter).

Example:

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2

So if your array for a String with elements separated by commas, use it as follows:

SELECT c.cpf FROM tbl_cliente as c WHERE FIND_IN_SET(c.cpf, array);

or

SELECT c.cpf FROM tbl_cliente as c WHERE FIND_IN_SET(c.cpf, 'cpf1,cpf2,cpf3');

---

On the other hand, if you have a table with Cpfs and want to check if the client’s CPF is in this table, use the clause IN, that accepts comma or subselect separated value lists. Example:

SELECT c.cpf FROM tbl_cliente as c WHERE c.cpf in ('xxx.xxx.xxx-xx', 'yyy.yyy.yyy-yy')

Or else:

SELECT c.cpf FROM tbl_cliente as c WHERE c.cpf in (select cpf from tbl_outratabela)

Answer 2

A solution with php would generate a query with several ? to the IN(). and then pass the numbers on execute()of the PDO

$sql ='SELECT c.cpf FROM tbl_cliente as c WHERE c.cpf IN(';

$qtd_cpfs = count($cpfs);
$in = str_repeat('?', $qtd_cpfs);
$in = preg_replace('/,$/', '', $in);

$sql .= $in .')';

//depois
$stmt = $db->prepare($sql);
$stmt->execute($cpfs);