MYSQL Query question

[ltbaggz]

I have a general question to try and figure out the best way to handle multiple queries in php.  i have a page that queries 2 different tables and used data from both accessing it row at a time.  the problem is that they are using the same database connection so once i execute the other query the first query is no longer available.  Hopefully I explained this in a reasonable way. 

Now my question is what would be better for functionality if I do the first query run a loop and dump all the data into an array then do the next and continue that way, or use my mysql class and create 2 mysql objects one for each query?

i can get around the problem, just curious if one way is better than the other.

thanks
Drew

[fert]

just create 2 mysql resources and use one for each of the quries

[Psycho]

Or you might be able to run one query and get all the data you need from both tables in one go. Post the queries and the table structures for more advice.

[Barand]

If you have a situation like this, where you pull a value from the first table to use as a parameter to pull records from the second table
[code]
<?php
    $resA = mysql_query ("SELECT colA FROM tableA WHERE colB = '$someval'");
   
    while ($rowA = mysql_fetch_assoc($resA)) {
       
        $a = $rowA['colA'];
       
        $resB = mysql_query ("SELECT colname FROM tableB WHERE somecol = '$a' ");
   
        while ($rowB = mysql_fetch_assoc($resB)) {
           
            echo $rowB['colname'] . '<br>';
           
        }
    }
?>
[/code]

then you should be using a single joined query
[code]
<?php
    $res = mysql_query ("SELECT b.colname FROM tableB b
                        INNER JOIN tableA a
                        ON b.somecol = a.colA
                        WHERE a.colB = '$someval'");
   
    while ($row = mysql_fetch_assoc($res)) {
                 
            echo $row['colname'] . '<br>';
           
    }
?>

[/code]