Development [DB/PHP] Categories, subcategories...

For a group project I'm working on I want to have a category tree which allows for a tree like structure to be built. I know and have done the DB side of this with a Categories table with (catID, catName, parentID). Now the tricky part is the PHP. I don't expect anyone to do this for me but if you could give me some hints it would be much appreciated as I have no idea where to begin.

What I want is basically what amazon has. You click on a category and products are listed on the right if they are in THAT category or ANY OF ITS SUBCATEGORIES. And at the same time the tree structure is changed showing the subcategories of the one you clicked on so you can refine a search in this way. Hints please?

 

Query the DB and return an indicator as to whether this is an Item or a subcategory, based on that, indent the tree using nested UL/OL groups...

The rest is easy, provided your DB structure is right. You can select items where "categoryId IN ([list/subquery])".

Might be misunderstanding this though...

 

It heavily depends what you use for your database connection. In case of simple SQL, you will have to create a recursive method doing the queries for each subcategory and put the results in tree structure (arrays of arrays of arrays of arrays of ...).

If you are using Doctrine, your task is much easier.
In case of Doctrine 1.2 you have support for hierarchical data (tree structures) :
http://www.doctrine-project.org/documentation/manual/1_2/nl/hierarchical-data
In case of Doctrine 2.0 you have a custom extension until there will be a official support of hierarchical data :
http://www.devcomments.com/Any-hierarchical-data-support-in-Doctrine-2-at251409.htm

 

Im using MySQL and PHP.

OK I'm gonna try starting with something simple and building it up from there. For the tree-structure which is basicaly like what you'd see on amazon:
Books(Fiction(....), Non-Fiction(...)), Electronics(TVs(...), MP3(...))
Where you'd only see sub trees when you click on a specific category Id need to start by displaying all the categories with no parent (i.e. the top level categories)
When a user clicks on one of these then the sub-tree for that category should be displayed, and so on so the MySQL query for this seems simple to me but right now what Im not sure of is the PHP that displays the tree. So how does this sound:

A php function for displaying the tree which (as mentioned by faugus, although I dont understand his method completly right now) uses arrays of arrays. When a user clicks on a category, I query the DB for its sub-cats (... WHERE parentID == catID) and replace the value currently at that place in the array, which would just be the cats ID with another array containing its sub-cats IDs. Then as mentioned a php function would handle the displaying of the tree by getting the appropriate names from the IDs and indenting them based on their level within the tree. I think is is good so far for the displaying of the tree structure, say on the left as on amazon. What do you think?

Now the tricky part I think is to have any "items" which are "below" the current place in the tree at which the user has navigated to (hope that makes sense) are dispalyed. And I think this is the recursive function mentioned comes in but have no idea how to go about it.

Let's see:
(Ill be using currentID to represent where in the tree the user is at)
(1) I'd have to select all "items" with catID == currentID
(2) Then get each sub-cat (i.e. cats with parentID == currentID)
(3) Repeat (1) replacing currentID with the sub-cats ID
....

Wow Im surprised by how much is is starting to make sense (assuming this will work). Im getting tired though so Ill take a break and let you guys tell me if Im going in the direction before I do any more lol

 

Well, there is NO need to permanently poll the database. You cna theoretically make the navigation using nested UL's and then use CSS and maybe some Javascript/jquery to show and hide it... thus reducing callbacks and refreshes whenever the user clicks on a list item.
look for CSS menu tutorails to see what I mean...

 

I was thinking about something in this way :

1. Database table TREE :
ID INT NOT NULL AUTOINCREMENT
NAME VARCHAR(100) NOT NULL
PARENT_ID INT

For root items set PARENT_ID set as NULL.

2. Database table PRODUCT :
ID INT NOT NULL
TREE_ID INT NOT NULL
... other attributes...

3. Then you will have method like this ($db is a abstraction class, but you will get the point):

Code:

function getTree($rootId) {
  $data = array();
  if (is_null($rootId)) {
    $query = "SELECT * FROM tree WHERE parent_id IS NULL";
  } else {
    $query = "SELECT * FROM tree WHERE parent_id = $rootId";
  }
  $db->query($query);
  while ($db->next_record()) {
     $id = $db->fetch("id");
     $data[$id]["name"] = $db->fetch("name");
     $data[$id]["subitems"] = getTree($id);
  }
  return $data;
}

The result of such method will be a array of root tree items, each having a name map key and subitems map key, where under subitems map key you will have another array with same structure for the subtree bellow that tree part.

Then you can call the method with getTree(NULL) way, which will give you the tree structure from the root, or with getTree($someId) which will give you the tree from and bellow $someId.

The harder task is to get all the products from subtrees, you will have to create the list of all TREE_ID's starting from your root and select the data from PRODUCT table using IN (...id list...) SQL operator.

@BentAnat: of course caching, javascript, ajax are nice thing to do, but at first he needs to know how to structure the data and how to get the data from database.

 

well, assuming you have three tables

Code:

[B]Categories[/B] (ID, CatName)

[B]SubCats[/B] (ID, CatId, CatName)

[B]Items[/B] (ID, SubCatId, CatId, Name)

I would PROBABLY query them like so:

Code:

SELECT a.ID, a.Name, b.CatName, c.SubCatName
FROM Items AS a
JOIN Categories as b ON (a.CatId=b.Id)
JOIN SubCats c ON (a.SubCatId=c.ID)
ORDER BY b.CatName ASC, c.SubCatName ASC

Note;all pseudo code, and it actually makes relational data a bit rtedundant with regards to the Categories and SubCategories tables... but just as a rough example.

This should return a dataset that might go as follows:

Code:

a.ID, a.Name,  b.CatName, c.SubCatName
1, CoolItem1, Cat1, NULL
2, CoolItem2, Cat1, SubCat1
3, CoolItem3, Cat2, SubCat 15 

etc.

I'd then go hit up a PHP function that basically gets that recordset, and steps through it, deciding (based on the Cat and Subcat fields) whether to indent further or not, handling "filler lines" (i.e. folder icons or whatnot) based on that.

Reason for me handling it this way?
Simples:
1 SQL query is quicker than multiples.
ESPECIALLY considering the fact that the dataset will be what? 200 rows? 2000 rows?
That little join is quick to execute, and all the lifting gets done by PHP, rather than a loop going php->sql->php->sql multiple times.

Also, as was mentioned earlier, such a resultset could be cashed SQL side as well...

 

Also, based on that resultset, you could then (as described using PHP) bild a menu that looks like this:

Code:

<ul>
    <li>Cool!item1</li>
    <li>SubCat1
        <ul>
            <li>CoolItem2</li>
        </ul>
    </li>
</ul>

etc.
Which in turn brins us back to what I said earlier, that you can then use CSS and/or something like jQuery (to keep it simple) to show and hide items, rather than reloading the "current category" based on where the user clicked.
Again - performance boost in most cases.

The solution has weaknesses, sure, but it's easy to handle, and only shows real weakness when true realtime polling is required, in which case you're looking at completely different needs and issues anyway, thanks to the stateless nature of the web.

 

@BentAnat: The problem with your solution is that is limited to just 2 levels of categories.

@OP: By the way, if you are MySQL only, you should consider :
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

Not REAAAALY... as it astands right now, sure... it is...
BUT, with some thought put into it, one can easily make a menu category, the parent of another one. With dataset sorting, one can then establish which is a subcat of which.

example (done in MS SQL 2005,but don't see why it wouldn't work in MySQL)

Code:

CREATE TABLE [dbo].[nav](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Parent] [int] NULL,

CREATE TABLE [dbo].[items](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](200) NOT NULL,
	[Category] [int] NULL,

^^ Those would be tables.
it's not 100% normalised, but the principle would remain ,and this is one of the cases where one could easily overnormalize.

We can then fill the table as follows:

Code:

1	Top	NULL
2	Sub1	1
3	sub2	1
4	Top2	NULL
5	Sub4	4
6	SubSub1	5
NULL	NULL	NULL

Then we make a query as follows:

Code:

SELECT 
	a.ID,
	a.Description,
	b.name AS CatName,
	c.name AS ParentCatName
FROM
	Items as A
	JOIN nav as B on (a.Category=b.ID)
	JOIN (SELECT ID, Name FROM Nav WHERE ID IN (SELECT DISTINCT(Parent) FROM Nav)) AS c ON (b.Parent=c.Id)
ORDER BY CatName ASC, ParentCatName ASC, Description ASC

This will return a resultset like:

Code:

2	item2	Sub1	Top
3	Item3	Sub1	Top
4	Item4	sub2	Top
7	Item7	Sub4	Top2
8	Item8	SubSub1	Sub4
9	Item9	SubSub1	Sub4

This shows that it's possible to do it with mroe than 2 sublevels no issue, as the parentId can be any id in the nav table.
The Items themselves are linked to a category, either the root, or a subcat, regardless of whether the subcat is the sub-sub cat or the root category...

Based on that resultset, we can then loop through items:

PHP:

$result=mysql_query($sql) or die(mysql_error());
if($result && mysql_num_rows($result)>=1)
{
    $count=mysql_num_rows($result);
    $itemId="";
    $itemDesc="";
    $currentcat="";
    $lastcat="";
    $parent="";
    $lastparent=""
    for($i=0;$i<$count;$i++)
    {
        $itemId=mysql_result($result, $,0);
        $itemDesc=stripslashes(mysql_result($result, $,1));
        $currentcat=stripslashes(mysql_result($result, $,2));
        $parent=stripslashes(mysql_result($result, $,3));
        if($parent==$lastcat)
        {    
            //write lastparent. We're gonna need this to check when we have to reverse the indentation
            $lastparent=$parent;
            //indent further
            //we can be sure that this is when we need to indent, thanks to the sorting we're doing on the dataset
        }
        else
        {
            //might not need to indent, but we MIGHT need to de-dent.
            //remember that lastparent only gets set on indentation, and therefore stores the "grandparent" if you will of the current item.
            if($parent==$lastparent)
            {
                //reverse indentation.
            }
            else
            {
                //just write the item.
            }
        }
        
        $lastcat=$currentcat;
    }
}

Of course, this leaves us with the issue of the grandparent's parent, which could be addressed by iterating forwards and backwards through an array of parents, but I am sure you get the point.