Which one is better?

[rushenas]

Hi all
I wanna know, which one of the following code is better:

Code1:

$query = "SELECT * FROM products, categories, options WHERE products.product_id = '".$_REQUEST["id"]."' AND categories.prod_id = products.product_id AND options.category_id = categories.category_id";
$result = mysql_query($query);

for ($i=0; $i<mysql_num_rows($result); $i++)
{
     $record = mysql_fetch_array($result);
     // Showing results ...
}

Code 2:

 
$query = "SELECT * FROM products WHERE products.product_id = '".$_REQUEST["id"]."'";
$result = mysql_query($query);

$record = mysql_fetch_array($result);
// Showing some results ...

$query = "SELECT * FROM categories WHERE categories.product_id = '".$_REQUEST["id"]."'";
$result = mysql_query($query);
for ($i=0; $i<mysql_num_rows($result); $i++)
{
     $record = mysql_fetch_array($result);
     $category = $record["category_id"]; 

 $query = "SELECT * FROM options WHERE options.category_id = '$category'";
 $result2 = mysql_query($query);
 for ($j=0; $j<mysql_num_rows($result2); $j++)
 {
      $row = mysql_fetch_array($result2);
      // Show results ...
 }
}

This is just a sample, of course I must have similar code for 5 tables (here is only 3 tables). So the first code will contain a more complicated query. But the second one contains more query. Which one is faster, and has better coding?

Maybe useful, products has some fields, including product_id. categories includes both product_id and category_id, and options contains option_id and category_id.

[tomhath]

The first, where you are using a JOIN, is better. It's very inefficient to do multiple selects as in the second.

Also, instead of SELECT *, only select the columns you actually need. Try to minimize the amount of data flowing between the database and your script.