Query within while loop?

Anon

It is possible to query a mysql database whilst in a while loop that has alrady just queried the database...

eg:
//query one
$sql=mysql_query(SELECT * FROM $table WHERE blah='$blah'");
while($row=mysql_fetch_array($sql))
{
//query two
$sql2=mysql_query("SELECT FROM $table2 WHERE blah='$blah'")
while($row2=mysql_fetch_array($sql2))
{
print ("$row2[0];");
}

}

or is it too taxing on the server.

rgds,
scott d~

Anon

Don't worry about it!

Anon

I've had code like these written by the programmer who created the prototype of the software I am currently developing. It used the results from the outer SELECT to create the inner SELECT. I changed this to work as a JOIN and now queries that used to take 5 minutes take about 5 seconds.

In terms of client/server efficiency nested SELECT loops is a very bad idea. You should try to move as much of the work as possible to the SQL server - it has better info than you on how to optimize the join. It also reduces the dependency on network speed (if your server is on a separate host) and frees network resources for other applications to use.

As for nested UPDATE calls, the story is a bit different. If you have updates involving a large record set over many fields, the transaction could explode your transaction log. The solution to this is to either keep the update at the single record level or limit the number of records in the resulting recordset (e.g. by using the TOP qualifier in MS SQL Server or by specifying record set size limit in your API call parameters.

Hope this helps.

Anon

Why not save yourself some performance and doe a join command in the SQL statement?

Cheers

Anon

Maybe I'm missing something but why not:

Select * from $table1 a, $table2 b
where a.blah = $blah and b.blah = $blah;

Unless maybe you are wanting to base the select clause of the second query on some result of the first query. But then couldn't you just do a subselect?

Anon

If both tables have a unique field, let's say ID, you can select the asked results from the first table, as you say, and then join the other table.
Let's say you want to select all rows from table 1 that contain the value 'PHP' in it's field language.
Select * from table1 where language='PHP'

Now you want to add the rows from table 2 to it, that match the id of table one. Both tables must have a field that is unique in each row, let's say both have the field ID.

SELECT * from table1,table2 JOIN on table1.id=table2.id where table1.language='PHP'

Hope this helps

Anon

Hey guys, using joins all sounds fine and dandy, BUT the inner while loop terminates the outer while loop once it has completed looping itself.

As a result you will only ever see the first result from your outer loop, but all the results of the inner.

I am currently having this problem, Can anybody help??