Nested/child query

I'm trying to write a highly dynamic navigation generator with PHP and mySQL.

I'm travelling pretty well, but I've come to a point where I just don't know where to go.

I've got all the PHP code to create a multi-dimensional array, then to generate the HTML. This is working fine.

The way my database is structured is as follows: (I have omitted certain rows that are irrelevant.. Please re开发者_运维技巧member this when answering this question..)

tbl: navigation

id     |name       |parent    |handle
1      | home      | 0        | 1
2      | about     | 0        | 2
3      | contact   | 0        | 4
4      | products  | 0        | 3
5      | computers | 4        | 3

tbl: handles

id     |handle      |redirect_to
1      | home       | 0
2      | about      | 0
3      | contact-us | 0
4      | products   | 0
5      | products/computers   | 0

What I want to achieve is a print_r() resulting in something similar to this per row in navigation...

Array (
  [id]=>1
  [name]=>home
  [parent]=>0
  [handle]=>Array (
       [id]=>1
       [handle]=>home
       [redirect_to]=>0
)

Is it possible to do this at a mySQL level, or will I have to do this with PHP?

there is no built-in notion of hierarchy in MySQL (not that I am aware of), so I suppose you'll have to do this in php. If you're having problems when writing your queries, consider using the nested set model.

Just use a join in MySQL to to fetch the correct handle, all the fields selected from handles will show up in your array with the correct key/value specified in the database.

SELECT 
 `navigation`.*,
 `handles`.`redirect_to`,
 `handles`.`handle`
FROM `navigation` 
INNER JOIN `handles` ON 
 `navigation`.`handle` = `handles`.`id`

This is just the tip of the iceberg when it comes to organizing and fetching data from relational databases like MySQL. Knowing where to use what query type can be incredibly powerful. If you ever have queries embedded in loops of other queries, you can probably fetch all the data at once.

You can't do this straight in mysql, it requires some PHP as well.

SELECT 
  n.id as nav_id, 
  n.name as nav_name,
  n.parent as nav_parent,
  n.handle as nav_handle,
  h.id as handle_id,
  h.handle as handle_handle,
  h.redirect_to as handle_redirect_to
FROM navigation n
  LEFT JOIN handle h ON n.handle_id = h.id

$nav = array();
foreach($results as $data) {
  array_filter($data); // removes null values

  $link = array(
    'id' => $data['nav_name'],
    'name' => $data['nav_name'],
    'parent' => $data['nav_parent']
  );

  if (isset($data['handle_id'])) {
    $link['handle'] = array(
      'id' => $data['handle_id'],
      'handle' => $data['handle_handle'],
      'redirect_to' => $data['handle_redirect_to']
    );
  }
}