<?php

/*
mysql> desc dir;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| did   | tinyint(3) unsigned |      | PRI | NULL    | auto_increment |
| pid   | tinyint(3) unsigned |      |     | 0       |                |
| name  | char(16) binary     |      |     |         |                |
| title | char(32)            |      |     |         |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> show index from dir;
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| dir   |          0 | PRIMARY  |            1 | did         |
| dir   |          0 | uk       |            1 | did         |
| dir   |          0 | uk       |            2 | pid         |
| dir   |          0 | uk       |            3 | name        |
+-------+------------+----------+--------------+-------------+
4 rows in set (0.00 sec)

create table file (
  fid smallint unsigned not null auto_increment,
  did tinyint  unsigned not null,
  mid tinyint  unsigned not null,
  name char(16) binary,
  title char(32),
  primary key (fid),
  unique index uk (fid,did,mid)
);

mysql> desc file;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| fid   | smallint(5) unsigned |      | PRI | NULL    | auto_increment |
| did   | tinyint(3) unsigned  |      |     | 0       |                |
| mid   | tinyint(3) unsigned  |      |     | 0       |                |
| name  | char(16) binary      | YES  |     | NULL    |                |
| title | char(32)             | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> show index from file;
+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| file  |          0 | PRIMARY  |            1 | fid         |
| file  |          0 | uk       |            1 | fid         |
| file  |          0 | uk       |            2 | did         |
| file  |          0 | uk       |            3 | mid         |
+-------+------------+----------+--------------+-------------+
4 rows in set (0.00 sec)
*/
    // DB connect

    $db = include('db.php');
    $dir = 'hnav';

    $_s  = 'select file.name, media.ext, file.title';
    $_s .= ' from dir, file, media';
    $_s .= ' where dir.name=\'' . $dir . '\'';
    $_s .= ' and dir.did=file.did';
    $_s .= ' and file.mid=media.mid';
    $_s .= ' order by file.name';

    if ($_r = mysql_query($_s, $db))   { 
        while (list($name, $ext, $title) = mysql_fetch_row($_r))    {
            $file = $name . '.' . $ext;
            print "$file => $title\n";
        }
        mysql_free_result($_r);
    }
?>