| ensnare 2004-06-29, 3:56 am |
| Thanks Phil,
This is brilliant.
How would you handle INSERTS? How would you get the id value for the
path before you insert the row?
I like this a lot better. Thanks so much.
Phil Roberts <phil.roberts@googlemail.com> wrote in message news:<Xns9516A398A13C4philroberts@216.196.97.132>...
> ensnare@gmail.com (ensnare) emerged reluctantly from the curtain
> and staggered drunkenly up to the mic. In a cracked and slurred
> voice he muttered:
>
>
> I use a system which is simple and doesn't rely on recursion. I
> basically store the entire thread "path" as a row field.
>
> So basically I would have the following table structure:
>
> ID | Name | Path
> ----+-------------------------+----------
> 0 | Category One | 00
> 1 | Sub-category of cat one | 00_01
> 2 | Sub-cat of cat id #1 | 00_01_02
> 3 | Category Two | 00
> 4 | Category Three | 00
> 5 | Sub-cat of cat three | 00_04
> 6 | Sub-sub cat of cat #3 | 00_04_05
>
> Want to get the entire tree structure? Just do an ORDER BY on the
> path column and use some PHP code like the following for
> formatting:
>
> while ($result = mysql_fetch_assoc($query)) {
> $nesting_depth = count(explode("_", $result['category_path']));
> $branch = str_repeat("--", $nesting_depth);
> echo "| $branch {$result['name']}";
> }
>
> This should result in:
>
> | Category One
> | -- Sub-category of cat one
> | ---- Sub-cat of cat id #1
> | Category Two
> | Category Three
> | -- Sub-cat of cat three
> | ---- Sub-sub cat of cat #3
>
> Want to get a particular tree branch? Just run a "SELECT * FROM
> table WHERE path LIKE '00_01%' ORDER BY path ASC" query.
>
> How you choose to store the path field data is up to you. I'm using
> this type of system for a photo gallery system and so far it's
> working fine.
|