[RESOLVED] Query from database to pull items and any children

s0me0ne

I'm still learning databases so I figured doing cars is something I can understand because i know all the brands :p.

Lets say I have a table called models as such:

ID | MODEL     | PARENT
1  | ford      | 0
2  | gm        | 0
3  | chevrolet | 2
4  | chrysler  | 0
5  | jeep      | 4
6  | lincoln   | 1

and another table called cars as such:

ID | NAME           | MODEL_ID
1  | new yorker     | 4
2  | monte carlo    | 3
3  | mustang        | 1
4  | town car       | 6
5  | mark7          | 6
6  | grand wagoneer | 5

If the parent is 0, it is the top level for that model, otherwise the parent has a value of the model id that is its parent. That way I can just use one database call without having to have more tables.

Lets say the user picks ford for the model, then the database just need to show all cars with that model_id like:
SELECT cars.title, models.name FROM cars, models WHERE cars.model_id=models.id AND models.name='ford'.
I understand that part.

But lets say the user picked chrysler the database should show us any items under chrysler and its children. So the "new yorker and the "grand wagoneer" should show up.

I need a query thats smart enough to do both, to pick any items based on the model and also any that might be children under that model. Thats what I cant figure out.

jib0

Ford has also Lincoln as children....
Anyway, the folowing recursive function should inspire you for doing what you want:

function get_children($id, &$array) {
    if ($id != 0) {
      $res = mysql_query("SELECT models.id, cars.title FROM cars JOIN models ON (cars.model_id = models.id) WHERE parent = $id");
      while ($row = mysql_fetch_object($res)) {
        $array[] = $row->title;
        get_children($row->id, $array);
      }     

    }
  }

  function get_models($model) {
    $res = mysql_query("SELECT models.id, cars.title FROM cars JOIN models ON (cars.model_id = models.id) WHERE models.name = '$model';");
    $row = mysql_fetch_object($res);
    $array = array($row->title);
    get_children($row->id, $array);

return $array;
  }

  $a = get_models('chrysler');
  print_r($a);

s0me0ne

The code above works but I found a good post on the MYSQL site, as I was trying out different methods on how I want to do this:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

It has examples on how to do this with 1 query. The bottom stuff with lft/rgt options is more advanced but you can easily go as deep as you want without having to join a hard set number of self joins.