php sql loop method

Question

I need a query that loops data from two tables. The two tables I have are VEHICLES and VEHICLES_DB.

The VEHICLES_DB table is a list of all the different types of vehicles in the database, including their speeds, picture, name, etc.

The VEHICLES table is a list of all vehicles, and which member it belongs too. It just contains the columns id, type, and member. The type column relates to which vehicle it is from VEHICLES_DB.

I have a page that shows you all the vehicles you own, grouped by each type. Then for each grouped type, it would have to refer to the VEHICLES_DB table to get that vehicles info. Something like:

$sql_result = mysql_query("SELECT * FROM vehicles WHERE member='$your_id' GROUP BY type", $db); 
while ($rs = mysql_fetch_array($sql_result)) {
    $sql_result2 = mysql_query("SELECT * FROM vehicles_db WHERE type='$rs[type]'", $db);
    $rs2 = mysql_fetch_array($sql_result2);

    // display vehicle 

    }

This method seems sloppy, for each vehicle type you own its querying the vehicles_db database, is there a better way to do it?

PS: Im not very good or knowledgeable with JOINs yet.

Answer 1

2 ways:

1) Join your vehicule DB to vehicules and display it

2) Cache your vehicule types

Starting with #2 since it's easier to show:

$result = mysql_query("SELECT * FROM vehicles_db", $db);
while($row = mysql_fetch_assoc($result)){
     $typecache[$row['id']] = $row;
}

$sql_result = mysql_query("SELECT * FROM vehicles WHERE member='$your_id' GROUP BY type", $db);
while ($rs = mysql_fetch_array($sql_result)) {

    // display vehicle
    var_dump($typecache[$rs['type']]);
    var_dump($rs);

}

The second one (solution #1) would be to join your vehicule info (much better in many cases)

$sql_result = mysql_query("SELECT * FROM vehicles INNER JOIN vehicles_db USING(`type`) WHERE member='$your_id' GROUP BY type", $db);
while ($rs = mysql_fetch_array($sql_result)) {

    // display vehicle
    var_dump($rs);

}

Both have advantages and disadvantages, i use them at different situation while #1 is the one i'd say i use the most

Answer 2

$sql_result = mysql_query("SELECT v.*,vd.* FROM vehicles v LEFT JOIN vehicles_db vd ON (vd.type = v.type) WHERE v.member='$your_id' GROUP BY v.type", $db); 
while ($rs = mysql_fetch_array($sql_result)) {
     // display vehicle 
}

So you have all fields in $rs associative array.

Answer 3

Why not using an sql JOIN ? This would do exactly what you want :)

Answer 4

The conventional approach is to leave out the GROUP BY in your first query and JOIN on type. Which means looping on type in your PHP probably.