So this was kind of a pain because of the fact that we had to have the hierarchy stored in the DB. Because of this, each item can have multiple children, and each child can have multiple parents.
This second part means we cannot simply loop through the list once and be done with it. We might have to insert an item in multiple places in the hierarchy. While you probably won't actually structure your data that way, the database schema you've described supports this scenario, so the code must support it too.
Here's a high-level version of the algorithm:
- Query both tables
- Create a map (array) of a parent (number) to its children (another array)
- Create a set of items that are children (array of numbers)
- Create a function that displays a single item, indenting it to the desired depth.
If that item has children, this function increases the depth by one, and calls itself recursively
- Loop through all items that aren't children (root items).
Call the function for each of those items, with a desired depth of 0
(no indent).
Here's two hours work. Enjoy :)
Note that I stuck it within a <pre>
block, so you might have to mess with how the indentation is done (output something other than two spaces, mess with the style of the divs, etc).
<?php
$con = mysql_connect("localhost", "test_user", "your_password");
if(!$con)
{
die("could not connect to DB: " . mysql_error());
}
mysql_select_db("your_db", $con);
// get chapters
$chapters = array();
$result = mysql_query("SELECT * FROM chapters");
while($row = mysql_fetch_array($result))
{
$id = $row["id"];
$name = $row["name"];
$chapters[$id] = $name;
}
// get chapters_chapters - We'll call it "parent/child" instead of "master/slave"
$parent_child_map = array();
$is_child = array();
$result = mysql_query("SELECT master_id, slave_id FROM chapters_chapters");
while($row = mysql_fetch_array($result))
{
$parent_id = $row["master_id"];
$child_id = $row["slave_id"];
$children = $parent_child_map[$parent_id];
if($children == null)
{
$children = array();
}
$children[] = $child_id;
$parent_child_map[$parent_id] = $children;
$is_child[$child_id] = true;
}
// display item hierarchically
$display_item_and_children = function($id, $name, $depth)
use ($chapters, $parent_child_map, &$display_item_and_children)
{
echo "<div><pre>";
// indent up to depth
for($i = 0; $i < $depth; $i++)
{
echo " ";
}
echo "id: " . $id
. " name: " . $name
. "</pre></div>";
// if there are children, display them recursively
$children = $parent_child_map[$id];
if($children != null)
{
foreach($children as $child_id)
{
$child_name = $chapters[$child_id];
$display_item_and_children($child_id, $child_name, $depth + 1);
}
}
};
// display all top-level items hierarchically
foreach($chapters as $id => $name)
{
// if it is a top-level item, display it
if($is_child[$id] != true)
{
$display_item_and_children($id, $name, 0);
}
}
mysql_close($con);
?>
And here's a screenshot: