SQL query with list in WHERE

Question

SQL query with list in WHERE

Navigation

#1 by (2 votes)
#2 by (-1 votes)

0

I have a query that searches all the id's for a DB. I make WHILE and put them in $retorno['dados'] . Since there is nothing separating them, they are all together. If I return the id's 41, 45, 50 , I get 414550 . Anyway, now I need to do another search:

SELECT * FROM restaurantes WHERE id=" id's que tenho em $retorno['dados'] " and put in WHILE again because I will receive all the data of several lines.

I checked one, but I could not find any answers. What would be the way to do this search?

I have two Tables:

First BD

id   |  idEmpresa  |   bairro
01   |      10     |  bairro 1
02   |      12     |  bairro 2
03   |      20     |  bairro 2
04   |      25     |  bairro 1

Second BD

id   |  empresa 
10   |  empresa a 
12   |  empresa b 
20   |  empresa c 
25   |  empresa d

I get information neighborhood and I consult which companies that serve in this neighborhood:

Receiving for example neighborhood 1 , I know that businesses with id 10 and 25 serve in that neighborhood. So I need to get the Second BD information from companies with id 10 and 25 .

These are the two queries I'm using.

$retorno = array();
$retorno['dados'] = '';
$retorno['dados2'] = '';

$sql2 = "SELECT * FROM endereco_atendimento WHERE bairro='Vila A'";
$buscar2 = $conexao->prepare($sql2);    
$buscar2->execute();
$buscar2->rowCount();
while($conteudo2 = $buscar2->fetchObject()){
    $retorno['dados2'] .= $conteudo2->idEmpresa;   
}
$lista = $retorno['dados2'];

$sql = "SELECT * FROM restaurantes WHERE id='$lista'";
$buscar = $conexao->prepare($sql);  
$buscar->execute();
$buscar->rowCount();
while($conteudo = $buscar->fetchObject()){
    $retorno['dados'] .= $conteudo->Nome;   
}

php sql

asked by anonymous 03.07.2017 / 07:30

2 answers

How to save the date in a table in mysql Error number_format

score 2 · Answer 1

You can put everything into a single query (with subquery).

-- código #1
SELECT Nome 
  from restaurantes
  where id in (SELECT idEmpresa
                 from endereco_atendimento
                 where bairro = 'nome bairro');

score -1 · Answer 2

I was able to solve it. I leave the solution here.

$retorno = array();
$retorno['dados'] = '';
$retorno['dados2'] = '';

$sql2 = "SELECT * FROM endereco_atendimento WHERE bairro='Centro'";
$buscar2 = $conexao->prepare($sql2);    
$buscar2->execute();
$buscar2->rowCount();
while($conteudo2 = $buscar2->fetchObject()){
    $retorno['dados2'] .= "'".$conteudo2->idEmpresa."',";
}
$lista = trim($retorno['dados2'], ',');;

$sql = "SELECT * FROM restaurantes WHERE id IN($lista)";
$buscar = $conexao->prepare($sql);  
$buscar->execute();
$buscar->rowCount();
while($conteudo = $buscar->fetchObject()){
    $retorno['dados'] .= $conteudo->Nome;   
}

I separated the id's from the first query with double quotes:

$retorno['dados2'] .= "'".$conteudo2->idEmpresa."',";

I had to take the last comma after the list was formed '41', '45', '41 ',' 45 ' because this last comma generated an error. In the second query I used IN in WHERE:

"SELECT * FROM restaurantes WHERE id IN($lista)";

And it worked!