I've been playing around with this.
I added a "Vegetable" category to the SitePoint hierarchy because I wanted to make sure my script could locate more than two subcategories.

- mptt.png (7.48 KiB) Viewed 810 times
The new table data looks like this:
Code: Select all
+--------+-----------+-----+-----+
| parent | title | lft | rgt |
+--------+-----------+-----+-----+
| - | Food | 1 | 20 |
| Food | Fruit | 2 | 11 |
| Fruit | Red | 3 | 6 |
| Red | Cherry | 4 | 5 |
| Fruit | Yellow | 7 | 10 |
| Yellow | Banana | 8 | 9 |
| Food | Vegetable | 12 | 13 |
| Food | Meat | 14 | 19 |
| Meat | Beef | 15 | 16 |
| Meat | Pork | 17 | 18 |
+--------+-----------+-----+-----+
I hope you can understand what is happening in this script even though I have not included the classes necessary to make it run. The two classes are still works-in-progress, so I don't want to release them yet. Just ignore everything before line 16.
Code: Select all
<?php
error_reporting(E_ALL);
header('Content-Type: text/plain');
// These classes are not available
require_once 'EasyDB.class.php';
require_once 'TextTable.class.php';
// EasyDB's ->query() method runs a query and stores the result rows in ->data
// The database table name is "categories"
$db = new EasyDB('categories');
// TextTable makes formatted text tables
$tt = new TextTable();
// Where to start; The "lft" of the root node
$left = 1; // 1
// Gets the root category (Food)
$db->query('SELECT * FROM `category` WHERE `lft` = '.$left);
// The first node to the left of Food (Fruit)
$left = $db->data[0]['lft'] + 1; // 2
// The first node to the right of Food (Meat)
$right = $db->data[0]['rgt'] - 1; // 19
$children = array();
do {
// Gets the next child
$db->query('SELECT * FROM `category` WHERE `lft` = '.$left);
// Adds the child to the list of children
$children[] = $db->data[0];
// The next child is the child to the right of the current child
$left = $db->data[0]['rgt'] + 1; // 12, 14, 20
// The loop ends when the circle is closed
} while ($db->data[0]['rgt'] < $right); // (19 < 19) == FALSE
// Displays a text table of the root category's children
echo $tt->getTextTable($children);
?>
The script found the three children of the Food category:
Code: Select all
+--------+-----------+-----+-----+
| parent | title | lft | rgt |
+--------+-----------+-----+-----+
| Food | Fruit | 2 | 11 |
| Food | Vegetable | 12 | 13 |
| Food | Meat | 14 | 19 |
+--------+-----------+-----+-----+
If line 17 is changed to
the script will find the children of the Fruit category.
Code: Select all
+--------+--------+-----+-----+
| parent | title | lft | rgt |
+--------+--------+-----+-----+
| Fruit | Red | 3 | 6 |
| Fruit | Yellow | 7 | 10 |
+--------+--------+-----+-----+
If I didn't explain this very well or I'm not making sense, it's because I'm up past my bedtime.
Edit: This post was recovered from search engine cache.