[Resolved] query in a loop -> more efficient - how?

[hatman]

Hi guys and gals - efficiency question:
(Sorry if this is redundant)
I have two tables, service_centers and vehicles.
I want to list all the centers and the assigned vehicles under each center. So I do a query for the centers and loop through the results.

$q_centers = "SELECT * FROM centers";
$r_centers = mysql_query($q_centers)
        or die(mysql_error());
while ($row_centers = mysql_fetch_row($r_centers)){
    $center_id = $row_centers[1];
// some other stuff to display about the center

Within the loop I do another query to find the vehicles for the center - hence another loop.

    $q_vehicles = "SELECT vin FROM vehicles WHERE center_id='$center_id'";
    $r_vehicles = mysql_query($q_vehicles)
            or die(mysql_error());
    while ($row_vehicles = mysql_fetch_row($r_vehicles)) {
            echo $row_vehicles[vin];
    }
// some more stuff about the center
}

So if there is about 80+ service centers and each have up to 40 vehicles it's an aful lot of queries within the loop.
I want to find out if there is more efficient way of doing this.
I could of course use LIMIT and break it down to several different pages but - any other way?

Thanks!

[Lars_Berg]

$q_vehicles =
 "SELECT vin ,c.*
FROM vehicles v inner join center c
on v.center_id= c.center_id";
$r_vehicles = mysql_query($q_vehicles)
            or die(mysql_error());
$pc = "";
while ($row_vehicles = mysql_fetch_array($r_vehicles)) {
  if ($pc != $row_vehicles['center_id']) {
//
// display center info
//
    $pc = $row_vehicles['center_id'];
  }
   echo $row_vehicles['vin'];
} 

[hatman]

-

[hatman]

ah inner join - cool!

See Uppsala - University town!