Using a mysql_query result in another query

Question

Below are the queries I am executing. But for some reason $MachineQuery is not getting a result. I assume it has something to do with using $q1 in the query but I don't know why. Any Ideas?

$q1 =mysql_query("SELECT DISTINCT SymptomID
 FROM Tree
 WHERE SymptomID NOT IN (SELECT DISTINCT SymptomID
                         FROM  Tree
                         WHERE LBNodeID = 0 OR RBNodeID = 0)")

$MachineQuery = mysql_query("SELECT DISTINCT M.MachineID, M.MachineName
                                 FROM Machines M, Systems Sys, Symptoms Sym
                                 WHERE M.MachineID = Sys.MachineID AND Sys.SystemID  
                                 =Sym.SystemID AND Sym.SymptomID IN ($q1)

Answer 1

i'd try

$MachineQuery = mysql_query("SELECT DISTINCT M.MachineID, M.MachineName FROM Machines M, Systems Sys, Symptoms Sym WHERE M.MachineID = Sys.MachineID AND Sys.SystemID
=Sym.SystemID AND Sym.SymptomID IN (SELECT DISTINCT SymptomID FROM Tree WHERE SymptomID NOT IN (SELECT DISTINCT SymptomID FROM Tree WHERE LBNodeID = 0 OR RBNodeID = 0))

Answer 2

$q1 is a MySQL resource variable, not a string or array. See the documentation for how to get the particular field(s) you want from that resource:

http://us2.php.net/mysql_query

http://us2.php.net/manual/en/function.mysql-fetch-array.php

Answer 3

The problem is that $q1 is a PHP resource. It is not a query, an array, a string, or anything else. As such, you need to "convert" the data to a type that is more usable by you.

Here is my solution:

$q1 =mysql_query("SELECT DISTINCT SymptomID
    FROM Tree
    WHERE SymptomID NOT IN (SELECT DISTINCT SymptomID
                         FROM  Tree
                         WHERE LBNodeID = 0 OR RBNodeID = 0)");
$list = array();
while ($row = mysql_fetch_array($q1)) {
    $list[] = $row[0];
}
$fullList = join(',', $list);

$MachineQuery = mysql_query("SELECT DISTINCT M.MachineID, M.MachineName
                                 FROM Machines M, Systems Sys, Symptoms Sym
                                 WHERE M.MachineID = Sys.MachineID AND Sys.SystemID  
                                 =Sym.SystemID AND Sym.SymptomID IN ($fullList)

That way you can reuse the results, and you don't have to run the query again.