• Howdy! Welcome to our community of more than 130.000 members devoted to web hosting. This is a great place to get special offers from web hosts and post your own requests or ads. To start posting sign up here. Cheers! /Peo, FreeWebSpace.net
managed wordpress hosting

[PHP] Cats and Sub Cats

Chroder

Chroder

New Member
[RESOLVED]
PHP: 
 Cats and Sub Cats[/b]

I have my database, and my database holds categories. These categories can hold sub-cats, and the sub-cats can hold more sub-cats etc.

Look at the [url=http://chris.devbox.net/diagram.gif]attachment[/url] to see a visual on what I have - I spend quite a while making it :P Anyway, my current code produces the snip on the right, what I need my code to make is the code on the left.

Here's what I've got.

[php]<?php
define('ROOT_DIR', './');
include(ROOT_DIR.'includes/global.php');

$sql = "SELECT * FROM ".TP."categories ORDER BY title ASC";

$cats = $db->getAll($sql);

foreach($cats as $key => $val) {
    if($val['parent_id'] == 0) {
        echo '<b>'.$val['title'].'</b><br />';
        $id = $val['category_id'];
        unset($cats[$key]);

        getSubs($id, $cats);
    }
}

function getSubs($id, &$cats)
{
    if($id < 1)
        return;

    static $count;
    if(!isset($count))
        $count = 1;

    $tab = '';
    for($i = 0; $i < $count; $i++) {
        $tab .= '&nbsp;&nbsp;&nbsp;';
    }

    foreach($cats as $key => $val) {
        if($val['parent_id'] == $id) {
            echo $tab.$val['title'].'<br />';
            $sid = $val['category_id'];
            unset($cats[$key]);

            $count++;
            getSubs($sid, $cats);
            $count--;
        }
    }
}

?>

Any help appreciated :D

Visual Diagram
 
Last edited:
Seems its just when it comes to displaying multiple sub-cats. If I change the parent ID of test2 to the id of test, then I get the right output:
Code: 
test
---test2
------test3

Hmm :doh!:
 
Well, I got it =/ Changed the foreach loop to a for loop and ditched the unset's.

PHP: 
function getSubs($id, &$cats)
{
    /// ...

    for($i = 0; $i < count($cats); $i++) {
        if($cats[$i]['parent_id'] == $id) {
            echo $tab.$cats[$i]['title'].'<br />';
            $sid = $cats[$i]['category_id'];

            $count++;
            getSubs($sid, $cats);
            $count--;
        }
    }
}
 
Hmm...I've done simple cat's and subcats like this before. The trickiest part was if you delete the top cat, to get all the subcats to be removed as well. All you need is a little recursion, but it's somewhat tricky to wrap your head around it. It seems it should be fairly easy for you though, as you have already implemented recursion.

I believe ashben helped me with it...? Or maybe it was atlas(cgi)?

Anyways...is it doing what you want now?
 
It is :)

It's hard to think "dynamically" for me, so it was tricky indeed ;) Trial and error did it for me :)
 
I know you got it solved, but I wanted to offer a different approach I use.

The table would consist of 5 columns:
  1. CatID = identity / auto increment column
  2. ParentID = same as yours, holds the ID of the branch above the current item
  3. IdentLvl = not strictly necessary; contains the depth of the current item in the tree
  4. SortID = this is different from yours, it contains the position of the item in the whole tree
  5. Title = title of the category
    [/list=1]

    So your example table would look like:
    Code: 
    CatID  ParentID  IndentLvl  SortID  Title
    3         0          0       1  ASP & ASP.NET
    7         0          0       5  CSS
    9         0          0       6  HTML
   11         0          0       7  Javascript
   15         3          1       2  test
   16         3          1       3  test2
   17        16          2       4  test3

    The advantage is that you only need to execute 1 SQL statement to get the entire list.

    Code: 
    SELECT space(2 * IndentLvl) + Title FROM Category
ORDER BY SortID ASC
    immediatly returns:
    Code: 
    ASP & ASP.NET
  test
  test2
    test3
CSS
HTML
Javascript
    properly ordered and indented.

    Inserting a category does require more work since you need to make sure the SortID column is adjusted properly though, but that's fine since 99.9% of the time people will be going to your site and getting the category list and adding a new category is rare compared to that.

    You can keep the way you have it now but I thought this approach was worth pointing out as well :).
 
In this case it's the design of the table that makes the difference though. (In case you ever use that method: SortID should have an index associated with it)

Originally posted by Chroder
I think I need to pick up a good SQL book...
Click to expand...
You're not alone, I dislike databases a lot. They're powerful but so boring as well :p
I only got beyond the basic SELECT/INSERT/UPDATE after I started using stored procedures (which I just found out MySQL will be getting too, someday... :confused2).
You might be better off wading through the MySQL documentation though, every DBMS seems to have it own little syntax quirks.
If you just need something quick and simple, http://www.sqlcourse.com/ is a good tutorial.
 
If I'd ever get a book on SQL, it'd probably be specific to mssql - heard its powerful :D At least, that's what my C#.NET book says ;) (Thanks for link btw! Great site :)
 
Last edited:
Originally posted by Chroder
If I'd ever get a book on SQL, it'd probably be specific to mssql - heard its powerful :D At least, that's what my C#.NET book says ;) (Thanks for link btw! Great site :)
Click to expand...
mssql = Microsoft SQL server? or a typo for MySQL? :confused4
SQL server 2000 is more powerful then MySQL (and Yukon adds some nice features as well :)) but the two don't really compare fairly. MySQL wasn't designed to cope with the kind of databases SQL server can manage and it's stated in the documentation and it's not anything you're going to notice in normal application :).
It doesn't have things like stored procedure or triggers though which are very useful (they're planned for 5.0 whenever that will be ready).

If you need a database server for a .NET desktop application I'd suggest MSDE over MySQL though. MSDE is a slimmed down (most importantly free :)) version of SQL server. It has all of the same features as the full SQL server and with .NET you get the advantage that it can talk to SQL server natively and not have to go through another layer.
You can also use all of the other tools that come with SQL server with MSDE (enterprise manager, query analyzer and the profiler).
 
Originally posted by Chroder
Oh, I thought "mssql" was the SQL Server 2000. :eek:
Click to expand...
Could be :confused2:
I just never heard it being referred to as mssql though.. just always SQL server.
 
PgSQL is the most powerful. Holds up much better to higher loads then MySQL from what I hear. Plus since they are dropping MySQL support in PHP5 (its going to have to be loaded by a seperate library) it might be a good time to learn PgSQL ;)
 
You must log in or register to reply here.
Back
Top