How to run a SELECT query within while loop in PHP?

Question

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.

Answer 1

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.

• Googlebot: as I said, the problem is that there not two tables only. Table 1 is the main table in the query, the second query can be from table2, table3, table4, table5, table6, .... The problem is that PHP arrays are not very efficient for data processing to catch a large set of data into arrays.