Refining php/mysql results sets

selecting from selection

[benihana]

Hi
I need to create a selection that selects only from the results of a previous selection in php/mysql.

so i have this:

$query="select * from TABLE where ARGUMENTS";
$result=mysql_query($query);
while ($myrow=mysql_fetch_array($result)){

I then want to say:

$query2="select * from $result ARGUMENTS";
$result2=mysql_query($query2);
while ($myrow2=mysql_fetch_array($result2)){
echo ('etc...

in order to refine the selection. How do i go about instructing mysql to select only from the results of the first query?

thanks

[Nick_W]

You could loop through the id's returned with the original resuts doing a query for each id?

There's probably a more elegant solution though...

Nick

[andreasfriedrich]

Switch to a real RDBMS or MySQL [mysql.com] 4 which will allow subselects.

Alternatively you could write the results of the first query to a temporary table and then query that temporary table. Thatīs still way faster than retrieving the first result and process it using PHP. Although that will depend on what that processing would involve. It it is rather simple just go with Nickīs suggestion.

Andreas

[benihana]

sorry - im new to this.
I guess iwas hoping to do something along the lines of nick_w suggestion, but dont really know how to construct the second select statement.
Ben

[jatar_k]

What about in your first while loop, only echo based on your criteria for the second query.

$query="select * from TABLE where ARGUMENTS";
$result=mysql_query($query);
while ($myrow=mysql_fetch_array($result)){
if ($result == "someval") echo "whatever";
}

or could be more complex if needed

[lorax]

Nominally OT,

Andreas, you bring up a point I've been wondering about for a while now. Optimization of a web app for speed. Where does one go (book title, web page, etc...) to get a handle on speed of one method versus another? In reading through the MySQL site and PHP.net I haven't really come across a good explanation or suggested practices.

[Nick_W]

What about in your first while loop, only echo based on your criteria for the second query.

Ahhhhh.... there's the more elegant approach I knew would come ;)

Nick

[jatar_k]

lorax, benchmark it and see.

Some things make sense, queries that reurn more rows, nested loops etc, but others are more subtle. Testing for different apps seems to be the best way.

[lorax]

jatar_k,
Re: benchmarking: I agree but there must some sort of documentation for "Best Practices" when it comes to certain situations.

For instance, Andreas noted that it's faster to create a temporary table and query that than it is to use PHP to parse through the result set of the first query. I would have thought the opposite to be true!

[benihana]

thanks for the input everybody.
i think the way the database is designed could be better and eliminate the need for all the php, so im going to chalk it up as experience and start agin.

cheers