A sql query..

I have just jumped into the world of php/mysql and of course my first proect has already stumpped me..

I desperatly need help with a query..

I am trying to convert a cd cataloging utility from vb/cdo to php/mysql..

I have a data structure setup and I am getting confused with the query to retreive the data from the 3 tables it is spread accross..

Basicly the 3 tables are CD, DIRECTORY and FILES .. my problem comes into play because a directory can contain a directory and be numerous levels down.. (we all know what a directory tree looks like)..

Any ideas??

Abysmal - Dazzed and Confused in SQL Hell..

Each CD will only point to one directory, the root directory. It will also have such information as the CD title, etc.

You directory table stores a directories and possibly a link to the parent directory (if one exists). Then you have an associative table from directory to itself, with links from directories to directories.

In your files table you have all the files and a key to the directory to which it belongs.

Then when you want to find what is on a CD, you simply get it's directory code and then get the directories and files that belong to it. Do you have a problm with this query? It isn't too difficult, as long as you understand what the tables are for.

select * from subdir where parent_dirid=10;
select * from files where dirid=10;