How can I make two queries in one?

Question

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.");";

Don't use variable interpolation to create query strings. Rather, use parameterized queries. The former is susceptible to SQL injection. — Assaf Lavie, Apr 21 '09 at 12:21

score 2 · Accepted Answer · answered Apr 21 '09 at 11:50

2

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.

answered Apr 21 '09 at 11:50

pgb

24,813
12
83
113

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

score 2 · Answer 2 · answered Apr 21 '09 at 12:01

2

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}'";

answered Apr 21 '09 at 12:01

Peter Smit

27,696
33
111
170

score 0 · Answer 3 · edited May 23 '17 at 12:13

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:

score 0 · Answer 4 · answered Apr 21 '09 at 12:01

0

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

answered Apr 21 '09 at 12:01

kishore

407
3
10

How can I make two queries in one?

4 Answers4