SQL query with list in WHERE

Asked

Viewed 651 times

0

I have a query that searches all id’s of a comic. I make a WHILE and put them in $retorno['dados']. Since there is nothing separating them, they are all together. If you 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 into WHILE again, as I will receive all data from various lines.

I did a search, but I didn’t get any answers. What would be the way to do this search?

I have two tables:

First comic

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

Segundo BD

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

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

Receiving for example neighborhood 1, I know companies with id 10 and 25 are in this neighborhood. Soon I need to pick up Segundo BD all company information with id 10 and 25.

These are the two querys I’m wearing.

$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

  • Try to explain better what you want with all this.

  • @Edilson, I edited the question. I think it’s clearer now.

  • select e.empresa from tbl1 as b join tbl2 as e on b.idEmpresa = e.id where b.bairro = "bairro 1"; you can use Join to do that, in the end you just end up with the attribute empresa of the second table. And in the end you end up using only one loop while.

  • @daltongonzaloFuentes: When adding the name of the neighborhood, beware of SQL Injection.

2 answers

2

You can put everything in a single query (with subconsulta).

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

-1

I managed to solve it. I leave here the solution.

$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 consultation with quotation marks and comma:

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

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

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

And it worked!

Browser other questions tagged php sql

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