mysql_list_table () + query...expensive?

4rxsid

im trying to list each table in a db, then do a query on each table returned to display the results of each query on each table
there are 25 tables, and i get a minimum 500 hits to this code per day:

//$connection already established
$result = mysql_list_tables($dbname);

//get tables
while ($table = mysql_fetch_row($result)) {

  $sql = "select a_field from $table[0]";
  $sql_result = mysql_query ($sql, $connection) or die("Couldnt execute query");

  //display the result - a single number
  if ($row = mysql_fetch_array($sql_result)) {
      do {
           $the_data = $row["a_field"];
     } while ($row = mysql_fetch_array($sql_result)); 
  }

} //END WHILE

is this expensive? how can i optimize this code??

sarahk

hitting multiple forums huh? tut tut!

You might be best to test this out yourself.

Could you put a "limit" on the number of rows returned per table?

As for the query, you will have 26 hits to the database. The only other way around that might be to have a union but then you'd need to know that "a_field" is always the same datatype.

Also, what is the benefit of selecting just the first field, why not select * from ...

Could you not display the table names and only show the data (in a pop up, or frame perhaps) if the user clicks on the table name.

If they do all need to be loaded then maybe use iframes

<TABLE width='100%'>
<tr><td>Table 1</td><td><iframe src='getData.php?table=table1'></td></tr>
<tr><td>Table 2</td><td><iframe src='getData.php?table=table2'></td></tr>
</table>

that way each iframe call will have it's own processing space and will load independantly. Won't be any faster or less expensive but might be a better user experience.

Personally, I'd check to see if they really need the data displayed up front and only show if they clicked on the table name.

4rxsid

i've thought about a limit...as you mentioned, that probably would be a good first start (i.e. LIMIT 1).

the data returned from that column (smallint datatype) in each of the tables would be a numeric value, so the union you mention might be good...only thing is that if it doesn't expedite the query, i probably don't need to use it. i just don't know enough about unions though to be sure.

hmmm...I'll try the LIMIT first.