How to query MYSQL and PHP using an array as a WHERE pro condition

Asked

Viewed 1,049 times

1

I’m making a query in MYSQL that brings the emails that were sent. These emails are in the email column, separated by comma, as in the email table below.

emailsenviados
IDenvio|emails
1      |[email protected],[email protected],[email protected]
2      |[email protected]

Then I need to use these emails that I’m taking as an array to do another query that will bring the names of the people in these emails, who are in the users table

ID|  nome  |email
1 | João   |[email protected]
2 | Maria  |[email protected]
3 | José   |[email protected]

Is there any way I can get a single consult or is it two? At first what I thought was to make the first consultation and to make an explosion of the results to make a second consultation, but I stopped.

My query is made like this:

$query=("SELECT id FROM emailsenviados WHERE IDenvio=1");
$db -> setQuery($query);
$incs = $db->loadResult();

and the second:

$query=("SELECT name FROM users WHERE >>AQUI SERIA A CONDIÇÃO PARA ENCONTRAR OS EMAILS<<");
$db -> setQuery($query);
$results = $db -> loadObjectList();
foreach($results as $row){
echo $nome.'<br/>';
}
php mysql select

2 answers

3


  • Thank you David, I did it, but came the bame blank

  • The right is to come an array of Names right, one for each user who has e-mail inside the array of emails. It gave some error?

  • No, I made this query in PHPMYADMIN first to test, switching the variable by valve, then came blank without error: SELECT name FROM users WHERE email IN ("[email protected],[email protected],[email protected]")

  • I tested now instead of email, a number field, there it works, maybe you have to put something because it is text?

  • hmm, yes, the emails have to be in simple quotes, I will correct in the reply

  • now was, I did so to put the variable, is there another simpler way?: $array_emails='"'. $array_emails. '"'; $array_emails=str_replace(','","',$array_emails);

  • This code you made is a good way out.

Show 2 more comments

1

Mysql has its own function for this, the FIND_IN_SET.

The FIND_IN_SET( string1, string2 ) returns true if the first value (string1) is contained in a comma-separated list passed in string2

Applying to your case:

SELECT campos FROM tabela WHERE FIND_IN_SET( email, emails )

Handbook:

https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set

Note that this is the best way out if what will vary is the field where the emails are listed. For situations where the list is "fixed", David Alves' solution use a concatenation on the PHP side is good.

Browser other questions tagged php mysql select

You are not signed in. Login or sign up in order to post.