MySQL While Loop and Diriving

[Napster]

In my site I have a database that lists categories of movies books etc etc. Here is something of what the database looks like:

id name derivedfrom
1 movies 0
2 action 1
3 comedy 1
4 black hawk down 2
5 Books 0
6 Bourne Identity 2
7 Harry Potter 5

The derivedfrom column means which ccategory does that category belong to. The while loop I used to print them out onto the page is:

$cats = mysql_query("SELECT * FROM categories ORDER BY derivedfrom,name") or die(mysql_error());

while ($cat = mysql_fetch_object($cats)) {

  $loop = $cat->derivedfrom * 2;

  $x = 0;

  echo "<option value=\"" . $cat->id . "\">";

  while ($x < $loop) {

echo "&nbsp;";

$x++;

  }

  echo $cat->name;

}

The database is then printed out like this:

Books
Movies
  Action
  Comedy
    Black Hawk Down
    Bourne Identity
        Harry Potter

But I do not wanted it printed out like that. I know why it is doing it that way but I do not know how to fix it. This is how I want it:

Books
  Harry Potter
Movies
  Action
    Black Hawk Down
    Bourne Identity
  Comedy

How can I get the loop to order the rows like that so it is properly ordered and properly aligned. Thank in advance.

[tuf-web_co_uk]

you have to pick each of the seperate derivedfrom seperately.

<?php
$sql = "SELECT * FROM `categories` WHERE `derivedfrom`='0' ORDER BY `name`";
$cats = mysql_query($sql) or die(mysql_error()); 

while ($cat = mysql_fetch_object($cats)) {

echo "<option value=\"" . $cat->id . "\">";
echo $cat->name;
echo "</option>";

$sql_2 = "SELECT * FROM `categories` WHERE `derivedfrom`='".$cat->id."' ORDER BY `name`";
$cats2 = mysql_query($sql2) or die(mysql_error());

while ($cat2 = mysql_fetch_object($cats2)) {

	echo "<option value=\"" . $cat2->id . "\">";
	echo $cat2->name;
	echo "</option>";

	$sql_3 = "SELECT * FROM `categories` WHERE `derivedfrom`='".$cat2->id."' ORDER BY `name`";
  	$cats3 = mysql_query($sql3) or die(mysql_error());

  	while ($cat3 = mysql_fetch_object($cats)) {

  		echo "<option value=\"" . $cat3->id . "\">";
  		echo $cat3->name;
  		echo "</option>";

  	}

}

}
?>

this example is based on the fact that you only have 1 or 2 main categories, then the final item. like you displayed

hth

[Napster]

Well I dont know how many there are going to be since there could be hundreds of them inside of each other.