Best way to print values from mysql table by querying through PHP

Most of the times in my pages, I need to print the value of only one field in the table, by using a loop. For example:

 <?php

 for($i=1;$i<=mysql_num_rows($result);$i++)
 {
     echo $row['name'];
     $sql1="select industry from table_industry where profid='".$row['prof']."'";
     $result1=mysql_query($sql1);
     $row1=mysql_fetch_array($result1);
     echo $row1['industry']; ?>
 }
 ?>

For example, I have 5000+ record in table and the above loop will execute 5000+ times.

What would be the best way to print the value of the industry field from the table table_industry?

Is it the code I wrote above,开发者_StackOverflow中文版 or is there a method for faster execution?

1. Avoid looping over all 5000 records. Use a filtering or pagination
2. Even more avoid nested queries. Use power of JOIN.

My best guess is that JOIN will help you out here.

With join you can instruct he MySQL server to combine different tables and access them in a single query, for example:

SELECT
  table_prof.profid
, table_prof.name
, table_industry.industry 
FROM table_prof
JOIN table_industry USING ( profid )
ORDER BY table_prof.name ASC;

Generally speaking, querying the database in a loop is a very bad idea and should be avoided unless you know exactly why you are doing it. Querying in a loop can easily bring a database server to it's knees.

use JOIN

if you want to include only those rows with values in industry table then sql will be

    SELECT 
     table_prof.profid 
    , table_prof.name 
    , table_industry.industry  
      FROM table_prof 
      JOIN table_industry USING ( profid ) 
      ORDER BY table_prof.name ASC;

and if you want to include all values from table table_prof then sql will be

      SELECT 
     table_prof.profid 
    , table_prof.name 
    , table_industry.industry  
      FROM table_prof 
      LEFT JOIN table_industry USING ( profid ) 
      ORDER BY table_prof.name ASC;

i think this will help you...

To follow your logic, i would select all information load it in a hash and iterate in the hash, not killing the database.