View Full Version : Making a category tree with array

06-22-2010, 09:44 PM
Dear friends,

I am trying to make a category tree out of a database table. let me make it simple. I will only have 2 type of categories, PARENT and CHILD . parents are not going to host articles, so they are only a way to sort the childeren. I want something like this"

- Electronics //PARENT 1

- Academic //CHILD 1

-Microprocessors //CHILD 2
- Hardware //PARENT 2

- Video cards //CHILD 1

and so on...

I want it in an array, and it should contain all the table content for each row.

and my table is:

Any help/suggestion is appriciated!

06-22-2010, 10:10 PM
Heh, this is something I've been throwing around for a while now, and I've just settled on a method I like.

First and simplest method is to keep with what you have right now. If you know the maximum depth (of children) just self join once per level and process the order in PHP (processing the order is a little harder, I haven't quite figured it out yet, but processing should be done with PHP anyway, not the DB).

Second method is one I used for a while. It makes working with existing forums very easy, but when you change a forum's location/order, or you add or delete a forum, it requires a large number of updates (one command, but a lot of updating). You basically nest forums by giving them a left and right bound, and children have bounds within the first (ie, a parent would be 1, 6, and 2 children within would be 2, 3 and 4, 5. If you find everything between 1 and 6, you have the children.) You can find information on it here

I've stopped using that method due to its upkeep issues. If you have a more or less static forum though, I personally like it a lot (very clean, easy to understand code, not much processing).

The last method, given to me by our very own Old Pendent is to store a left padded hierarchy in each row, ie, if we assume each of the example categories' ids are sequential in order:
Electronics: 001
Academic: 001-002
Microprocessors: 001-003

You can pull the hierarchy at any time or do a LIKE %001% to find the categories on the tree related to electronics.

I gave these methods in case you want to expand beyond 2 levels. If you're going to stick to 2 levels, I'd do something like this

SELECT * FROM `table` ORDER BY pid, `sort`
Then when you're extracting each row, you'll get the parent categories then the children categories. Store the parents into an array, then what I did is stored children as an element of the parent array (ie, $forums['children'] = arrayinfo).

I at least know of no direct way to sort it in mysql directly, but again, its a processing issue, which should be done in PHP.

06-22-2010, 10:49 PM
Thanks a lot Keleth, it helped me to understand it better. but would you please give some codes as well so I can get the idea better?!

06-22-2010, 10:51 PM
Thanks a lot Keleth, it helped me to understand it better. but would you please give some codes as well so I can get the idea better?!

For which method?

06-23-2010, 01:26 AM
For which method?

Well in my case I know that I will have only two type of categories, Parents, and childs. and a child only belongs to one parent. and there is no sub-child and also I am limited somehow to follow that kind of MySQL table I posted at first. whats your suggestion?


06-23-2010, 01:34 AM
I would select the details you want and order first by pid then sort.

Then as you retrieve the columns, you'll first get all the parents: store them into an array (these are the ones with pid = 0). Then you'll get the children. I would just add a array element that contains the child arrays.

06-23-2010, 02:03 AM
Thanks again, I get what do you mean but it's a bit funny that I can't figure the php code out for this task yet!

06-23-2010, 02:11 AM
Thanks again, I get what do you mean but it's a bit funny that I can't figure the php code out for this task yet!


$result = mysql_query('SELECT * FROM `table` ORDER BY pid, `sort`');
while ($info = mysql_fetch_array($result)) {
if ($info['pid'] == 0) $categories[] = $info;
else $categories[$info['pid']]['children'] = $info;

I'll leave you to figure out what to do with the results, should be obvious from here :-)

06-24-2010, 11:13 PM
After some thinking, I came to this Idea. I post my soloution here maybe it help other people. I also still have some question at the end of this post maybe somone can help!

http://img229.imageshack.us/img229/825/32082717.jpg (http://img229.imageshack.us/i/32082717.jpg/)

and here is the code, note that I used a MySQL connection class.

$parentCats = $_DB->Query("SELECT * FROM category WHERE parent IS NULL ORDER BY parent_sort ASC");
$parentCatsCount = $_DB->GetResultNumber($parentCats);

$childCats = $_DB->Query("SELECT * FROM category WHERE parent IS NOT NULL ORDER BY child_sort, parent_sort ASC");
$childCatsCount = $_DB->GetResultNumber($childCats);

$totalCats = $_DB->Query("SELECT * FROM category");
$totalCatsCount = $_DB->GetResultNumber($totalCats);

$articleCats = array();

for($i=0; $i<$parentCatsCount; $i++)
$parents[$i]["category_id"] = $_DB->GetResultValue($parentCats, $i, "category_id");
$parents[$i]["name"] = $_DB->GetResultValue($parentCats, $i, "name");
$parents[$i]["parent"] = $_DB->GetResultValue($parentCats, $i, "parent");
$parents[$i]["parent_sort"] = $_DB->GetResultValue($parentCats, $i, "parent_sort");
$parents[$i]["child_sort"] = $_DB->GetResultValue($parentCats, $i, "child_sort");
$parents[$i]["desc"] = $_DB->GetResultValue($parentCats, $i, "desc");
$parents[$i]["url"] = $_DB->GetResultValue($parentCats, $i, "url");
$parents[$i]["img"] = $_DB->GetResultValue($parentCats, $i, "img");
$parents[$i]["forum"] = $_DB->GetResultValue($parentCats, $i, "forum");

// Counting number of childs of the same paret
$query = $_DB->Query("SELECT * FROM category WHERE parent=".$parents[$i]["category_id"]." ORDER BY child_sort");
$parents[$i]["childs"] = $_DB->GetResultNumber($query);

if( ($parents[$i]["childs"] = $_DB->GetResultNumber($query)) != 0 )
for($j=0; $j < $_DB->GetResultNumber($query); $j++)

$parents[$i][$j]["category_id"] = $_DB->GetResultValue($query, $j, "category_id");
$parents[$i][$j]["name"] = $_DB->GetResultValue($query, $j, "name");
$parents[$i][$j]["parent"] = $_DB->GetResultValue($query, $j, "parent");
$parents[$i][$j]["parent_sort"] = $_DB->GetResultValue($query, $j, "parent_sort");
$parents[$i][$j]["child_sort"] = $_DB->GetResultValue($query, $j, "child_sort");
$parents[$i][$j]["desc"] = $_DB->GetResultValue($query, $j, "desc");
$parents[$i][$j]["url"] = $_DB->GetResultValue($query, $j, "url");
$parents[$i][$j]["img"] = $_DB->GetResultValue($query, $j, "img");
$parents[$i][$j]["forum"] = $_DB->GetResultValue($query, $j, "forum");

Now can you tell me how to get the data out of this array? I want to sort them and make every category name a hyperlink. like this:

- Electronics

- Academic

- Hardware

- Video cards


I think foreach will come handy here but how to do that for multidimensional arrays!


06-24-2010, 11:37 PM
I think the methodology is the same... I know of no way to do a subsort (select elements under a particular selection). Changing the sort just requires rewording. Again, I think the best way to do it is in PHP. Basically you've changed it to 3 queries now instead of 1, and you still have a decent chunk of php involved.

Also, I wouldn't start your ids at 0 then have a reference id as NULL (you're obviously not using int). Start at 1 then when there is no reference, go 0. This is however just a personal opinion (I think). If you're storing integers, have an integer col.