tags:

views:

147

answers:

5

How can I make this two queries in one ?

$query = "SELECT * FROM `amb_prod` WHERE idAmbiente='".$ambId."';";
$result_set = mysql_query($query);

while($member = mysql_fetch_array($result_set)){
        $query2 = "SELECT * FROM `produto`, `pt` WHERE
     produto.refPT = pt.ref AND
     produto.refPT = $member['idProduto'] ;";
    $result_set2 = mysql_query($query2);
}

I have have tried this but it didn't work..

$query = "SELECT * FROM `produto`, `pt` WHERE
     produto.refPT = pt.ref AND
     produto.refPT = (SELECT `idProduto` FROM `amb_prod` WHERE idAmbiente='".$ambId.");";
+2  A: 

This should work:

$query = "SELECT * FROM `produto`, `pt` WHERE
        produto.refPT = pt.ref AND
        produto.refPT IN (SELECT `idProduto` FROM `amb_prod` WHERE idAmbiente='".$ambId.");";

I'm not sure about the table structure, but a join may work as well.

pgb
A little off topic, but don't concatenate SQL commands, use parametrized queries if possible (this code is potentially subject to SQL injection).
Lucero
If this one get's slow with bigger tables, chech my join solution
Peter Smit
A: 

You cannot have two cursors open in the same connection the same time. You need to open a second connection. I would strongly advise against that though; issuing a query for every row read would be a bit slow. If I were you, I would do this:

SELECT pr.*, pt.*
FROM "produto" pr, "pt" pt, amb_prod ap
WHERE produto.refPT = pt.ref
AND ap.idAmbiente = $ambId
AND produto.refPT = ap.idProduto

Ideally, you would convert this to a parametrized query, for security, maintainabilty and performance resons. I'm not sure how it is done in PHP but the MySQLi_STMT class looks like a good starting point:

DrJokepu
+1  A: 

With a join instead of subquery:

$query = "SELECT pr.*, pt.* 
FROM amb_prod ap 
JOIN producto pr ON (ap.idProduto = pr.refPT) 
JOIN pt ON (pr.refPT = pt.ref) 
WHERE idAmbiente='${ambId}'";
Peter Smit
A: 

SELECT * FROM produto, pt , amb_prod WHERE produto.refPT = pt.ref AND produto.refPT = amb_prod.idProduto AND amb_prod.idAmbiente='".$ambId."' ;

Based on the data, you may have to use distinct in the select clause

kishore
A: 

Don't use variable interpolation to create query strings. Rather, use parameterized queries. The former is susceptible to SQL injection.

Assaf Lavie