12
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);
12
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);
10
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)
But what is the syntax of the mysql array? BD has always been my weak...
@Kennyrafael No array in Mysql.
Vish...as would then the query to bring several specific CPF s that I have of a single time?
@Kennyrafael I updated the answer with a few examples. There really is no array in Mysql. But you can simulate one using a String with comma separated values or the IN clause by passing a SELECT or distinct values.
Yes yes, I slept now, just put my values separated by comma in the second parameter, that’s right...
@Kennyrafael In this case the IN
is preferable to FIND_IN_SET
, I believe the performance is better.
1
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);
How about "... IN('" . implode("'", $cpfs) . "')";
?
This is a better alternative if the values are available in a PHP array or other language and the query can be manipulated. But if the values are in another table, use the IN
with a subselect which will be much more efficient.
@bfavaretto, it is necessary to pass the ?
and not the Cpf value because the query is parameterized. an alternative would be to use, array_fill()
with implode()
I had not understood that it was parameterized. Where is the bind of values?
@bfavaretto, the bind
can be done with the methods bindValue()
, bindParam()
and execute()
.
I know, it’s just that this part is missing in the answer, to the point that it led me to think that you were building the content of direct IN.
The last line is the bind.
Browser other questions tagged mysql syntax
You are not signed in. Login or sign up in order to post.
Is the Cpf column multivariate? apparently a
IN()
solve this.– rray
no, my intention was to bring several results at once...
– Kenny Rafael
Can you give more details of the problem? It still seems that
IN
would be more appropriate.– bfavaretto
Where does the array come from? PHP?
– bfavaretto