best way to prgram php and mysql query

Question

most of times in our pages we need to print only 1 field value of table in 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']; ?>
 }
 ?>

above is one PHP code just for example where we think that $row['prof'] carry value of profession ID and we print profession of each person.

for example we have 5000+ record in table and above loop will execute for 5000+ times

what will be the best way to print value of industry field from table table_industry

1. Same code which i write above ?
2. any other php code suggestion for faster execution and less use of resources?

Thanks

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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...