Mysql – How to run a SELECT query within while loop in PHP

MySQLmysql-5.5PHPselect

Within a SELECT query via PHP while loop, I need to run a mysql SELECT query as

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

while ( $row = $result->fetch_assoc() ) {

if ( $row['X'] == 'X' ) {
$result2 = $mysqli->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.

Best Answer

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.

Related Question