I have this article about the method, but this method is not really good because its loop the sql query millions times.
Anyone knows any other method using the same MySQL structure?
CREATE TABLE IF NOT EXISTS `jooria_categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text COLLATE utf8_bin NOT NULL,
`sub` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;
There are four widely used methods to do this:
Adjacency list (the one in your post) is the most convenient however it requires a support for recursive queries which MySQL lacks, though it can be emulated to some extent:
There are several possibilities. You can store a reference to the parent of each subcategory in the row, just like syroin suggested. You can also use Modified Preorder Tree Traversal. It's more complicated, but many tree operations become much simpler to do in SQL. As a third alternative, some systems (e.g. CakePHP's TreeBehaviour) combine them both. They store a left and right field for MPTT and a parent field for easy reorganisation.
Take a look at Storing Hierarchical Data in a Database
This is more of a database design question than anything else, I believe. You're asking about how to create "Unlimited subcategories," but what you want, if I understand you correctly, is a normal tree structure (parent, child, grandchild).
The easiest way to model this is to have three columns: id, title, and parent. parent is a "foreign key" referencing the current table. If parent is set to null, the category is "top level;" if it isn't (and is set to an extant record in the table) it is a subcategory.
didn't read the article but I looked over your sql. A good pattern to do this generally is to keep track of an item's parent rather than it's sub-categories. I think that can get rid of some unnecessary iterations.
It's the same idea as the Composite Pattern.
You can use this structure and create an additional column which stores all the subcategories. With this column you will need only one query for most tasks I can think of.
Example:
a
b
d
c
This tree would be represented as:
id title parent parents
1 a 0 0
2 b 1 0,1
3 c 1 0,1
4 d 2 0,1,2
You can stick with the adjacency list implementation and do it with a single call from your app layer (php) to mysql if you use a stored procedure. The following stored procedure is iterative vs. recursive but still provides good performance in most cases and the adjacency list implementation has a multitude of advantages over other methods especially when it comes to maintaining the hierarchy:
Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
You can call the stored procedure from php as follows:
$result = $conn->query(sprintf("call category_hier(%d)", 1));
mysql> call category_hier(1);
+--------+---------------+---------------+----------------------+-------+
| cat_id | category_name | parent_cat_id | parent_category_name | depth |
+--------+---------------+---------------+----------------------+-------+
| 1 | Location | NULL | NULL | 0 |
| 3 | USA | 1 | Location | 1 |
| 4 | Illinois | 3 | USA | 2 |
| 5 | Chicago | 3 | USA | 2 |
+--------+---------------+---------------+----------------------+-------+
4 rows in set (0.00 sec)
Script
drop table if exists categories;
create table categories
(
cat_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
parent_cat_id smallint unsigned null,
key (parent_cat_id)
)
engine = innodb;
-- TEST DATA
insert into categories (name, parent_cat_id) values
('Location',null),
('Color',null),
('USA',1),
('Illinois',3),
('Chicago',3),
('Black',2),
('Red',2);
-- STORED PROCEDURES
drop procedure if exists category_hier;
delimiter #
create procedure category_hier
(
in p_cat_id smallint unsigned
)
begin
declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;
create temporary table hier(
parent_cat_id smallint unsigned,
cat_id smallint unsigned,
depth smallint unsigned default 0
)engine = memory;
insert into hier select parent_cat_id, cat_id, v_depth from categories where cat_id = p_cat_id;
create temporary table tmp engine=memory select * from hier;
/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
while not v_done do
if exists( select 1 from categories c
inner join hier on c.parent_cat_id = hier.cat_id and hier.depth = v_depth) then
insert into hier select c.parent_cat_id, c.cat_id, v_depth + 1 from categories c
inner join tmp on c.parent_cat_id = tmp.cat_id and tmp.depth = v_depth;
set v_depth = v_depth + 1;
truncate table tmp;
insert into tmp select * from hier where depth = v_depth;
else
set v_done = 1;
end if;
end while;
select
c.cat_id,
c.name as category_name,
p.cat_id as parent_cat_id,
p.name as parent_category_name,
hier.depth
from
hier
inner join categories c on hier.cat_id = c.cat_id
left outer join categories p on hier.parent_cat_id = p.cat_id
order by
hier.depth;
drop temporary table if exists hier;
drop temporary table if exists tmp;
end #
delimiter ;
-- call from php
call category_hier(1);
call category_hier(2);
