Formatting mysql information by values

Okay, here is something slightly complicated and I am trying to figure out the fastest and smoothest way to do the following.

I couldnt post my mysql datat in a visible way on this forum, so im linking to a text file, the mysql structure and test data is at the top:

http://eli-white.com/files/table%20information.txt

The way I want to get things to display is:


Songs
->Genre
->Albums
->Artists
-->Green Day
-->Flyleaf
--->Singer
-->Atreyu
->Title
Movies
->Genre
->Actors


But for the sake of you understanding how things would get organized that way I added the rest of the corresponding row information after the formatting:


Name – ID – SORT ORDER – (Parent Ids) – (Child Ids)
Songs – 1 – 1 –( )- (2,3,5,9)
->Genre – 2 – 1 – (1) – ()
->Albums – 3 – 2 – (1) – ()
->Artists – 5 – 3 – (1) – (4,6)
-->Green Day – 6 – 1 – (5,1) – ()
-->Flyleaf – 4 – 2 – (5,1) – (8)
--->Singer – 8 – 1 – (4,5,1) – ()
-->Atreyu– 7 – 3 – (5,1) – ()
->Title – 9 – 4 – (1) – ()
Movies– 10 – 2 – () – (11,12)
->Genre – 11 – 1 – (10) – ()
->Actors– 12 – 2 – (10) – ()


So for example, it would find all the rows where is_cat is 1, then sort those by the sort order. Then, it would go to the first category it just found and find all the forums right under that (forums where id is in the category's child ids). Then after that it would continue to cascade through each forum finding all the forums where the id is in the parent forum's child ids.

If that made any sense then thats great, otherwise please ask for me to re-explain something.

*bump*

any thoughts?

Okay, I have come up with this class that does pretty much what I want, except for one issue.

class forum
{
public $forum_list=array();
function __construct()
{
}
function getcats()
{
global $super;
$cat_sql = "SELECT * FROM ".TBL_PREFIX."forums WHERE `is_cat`='1' ORDER BY sort_order ASC";
$cat_query = $super->db->query($cat_sql);
while ($cat = $super->db->fetch_assoc($cat_query))
{
$this->forum_list[] = $cat['name'];
$this->get_forums($cat['child_ids']);
}
}
function get_forums($ids)
{
global $super;
$array = explode(",", $ids);

foreach ($array as $row)
{
$forum_sql = "SELECT * FROM ".TBL_PREFIX."forums WHERE `id`='".$row."'";
$forum_query = $super->db->query($forum_sql);
while($forum = $super->db->fetch_assoc($forum_query))
{
$parent_ids = explode(",", $forum['parent_ids']);

$deep = count($parent_ids);
//echo "Number Dashes: ".$deep;
$dashes = str_repeat("--",$deep);
$dashes = $dashes;
$this->forum_list[] = $dashes.$forum['name']."<br />";
$this->get_forums($forum['child_ids']);
}
}
}

}

This code does not order the children by the sort order. The children's order is at the mercy of whatever order the children id's are in the parent forums.

Eg:
Songs has the children id's "2,3,5,9"

The forums 2,3,5,9 have the field sort_order set so that happens to be the way they show up.

However, if Songs had children id's: "5,2,9,3"

I would still want them to show up in the same order because of the sort order they have.

What do I need to change in order to do this, or even what would be a better way to go about this?

*bump*

This is not a direct answer to you question, but it will help lead you to the answer. Your database is not designed very well and should be reworked (normalized). In particular, I'm referring to the the parent_ids and child_ids fields. It's never good practice to have an individual field that is made up of comma separated values.

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

So you think it would be easier to just have a single parent id for the parent directly above it?