How to run a SELECT query within while loop in PHP? Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as

$result1 = $mysqli-&gt;query("SELECT * FROM table1");

while ( $row = $result-&gt;fetch_assoc() ) {

if ( $row['X'] == 'X' ) {
$result2 = $mysqli-&gt;query("SELECT * FROM table2");
.....
}

}


but this does not work. I cannot JOIN the tables, as the if statement is complicated to perform SELECT from different tables.
This is for all intents and purposes, a Cartesian Join.

It is definitely out of the question to join every row and pass through the data a single time since you do not know the running time or the amount of temp table space needed.

Perhaps you could do the following:


If table1 is small and table2 is small

Run SELECT * FROM table1; and load it into an array in memory
Run SELECT * FROM table2; and load it into an array in memory
Iterate the outer and inner loop over the arrays only

If table1 is large and table2 is small

Run SELECT * FROM table2; and load it into an array in memory
The outer for loop could still pass through table1 but the inner loop reads the data from table2 from the array.

If table1 is large and table2 is large

Run SELECT * FROM table1; and load it into a local text file
Run SELECT * FROM table2; and load it into a local text file
Iterate the outer and inner loop over the text files

If the DB Server has 60G of RAM (RISKY)

Performs the Cartesian Join anyway
SELECT A.*,B.* FROM table1 A,table2 B;



I also suggest not using SELECT * but using SELECT col1,col2,... reading only needed columns. This would significant reduce round trip times for queries.

The point behind these suggestions is to avoid a process of opening and close threads of data that is fetch repeatedly.

UPDATE 2013-06-04 18:47 EDT

Perhaps you could write a stored procedure, run the SP to collect all the data on the server side, then simply read the data in one pass. Again, this will reduce rounds trips of calls, opening and closing DB Connections. All the PHP logic would thus be done is the SP.