MySQL double query situation

Question

Is there another way to do this Query ?

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

The problem is my localhost have the MySQL version 5.1.30-community, but my Online server have the 5.0.67-community MySQL version.

And I think is that the reason why I get this error only when I try to run on the online server:

mysql_fetch_array(): supplied argument is not a valid MySQL result resource

Answer 1

well, it would make a lot of sense to install a matching version of MySQL on your test systems, so you can debug the error more easily.

You could also try something like this...

$query = "SELECT * FROM produto, pt, amb_prod WHERE
    produto.refPT = pt.ref AND
    produto.refPT = amb_prod.idProduto WHERE amb_prod.idAmbiente='1');";
$result_set = mysql_query($query);

Answer 2

At least to simplify your query, you could go with:

"Select * from produto,pt,amb_prod WHERE
       produto.refPT = pt.ref AND
       produto.refPT=amb_prod.idproduto AND
       amb_prod.idAmbiente='1';"

Although I'd recommend selecting fields rather than "*". I also have to wonder why you are quoting the '1', is that not a numeric field?

Answer 3

It makes live easier when you create a nice view of your Query, like:

$query = "SELECT *
          FROM produto, pt, amb_prod 
          WHERE produto.refPT = pt.ref
          AND produto.refPT = amb_prod.idProduto 
          WHERE amb_prod.idAmbiente='1';";
$result_set = mysql_query($query);

Answer 4

Get rid of the semi-colon at the end and see if that helps.

On some bridge implementations (the thing that connects your language to the MySQL server), your query string is actually two queries:

SELECT * 
FROM produto, pt 
WHERE
 produto.refPT = pt.ref AND
 produto.refPT IN (SELECT idProduto 
                   FROM amb_prod 
                   WHERE idAmbiente='1')

And...

[Blank]

Some bridges may get confused both about which result set to return (the result of the SQL or the blank query) and about whether a blank query is acceptable or not.