Hello,
MY database looks like (pligg cms, sample data)
id catID parentID catName
1 1 0 location
2 2 0 color
3 3 1 USA
4 4 3 Illinois
5 5 3 Chicago
6 6 2 Black
7 7 2 Red
Let say, how do i get top parentID of chicago, it should be location.
Do i have write recursive function in php or is this doable in mysql?
Please help me, been trying to figure out for hours..
Thank you.
This website has a really nice overview of the different methods for storing hierarchical data in mysql and PHP. To answer your question, the easiest way is to use php and recursion. There are other methods you could use such as the modified preorder transversal, which don't require multiple database queries. But this method can be more complex to implement when dealing with a lot of insertions and updates.
Another really cool method and my personal favorite is the so called "closure table" / "adjency relation" mentioned in http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree
Regarding your comment you basically have to make a loop or a recusrive function that selects the parent of chicago, then the parent of the parent and so forth.
$stack = array();
$parent = 3;
while($parent != 0){
$data = (put your mysql to get the row with parentID = $parent)
$parent = data['parentID'];
$stack[] = $data;
}
$stack = array_reverse($stack);
Stack will then contain the parents of Chicago, (ie. location, USA)
As mysql doesnt yet support features such as connect by (oracle) or common table expressions (sql server) most of the examples you'll come across will require multiple calls from php into mysql - one call per level in the hierachy. If you have trees with many levels and lots of concurrent users this soon adds up e.g a tree of depth 20 with 1000 concurent users would require 20K calls into the database. The following method uses a stored procedure and for the same scenario only requires 1000 calls (1 per user). What you do with the resultset is up to you: you might generate an XML DOM, load it into an array or just output it as HTML, the important point is that you have the entire tree in a resultset in a single call.
EDIT: added a category_parent stored procedure having initially misread your question.
Example mysql calls
call category_hier(1);
call category_hier(2);
call category_parent(5);
call category_parent(7);
Example php script
<?php
$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
$result = $conn->query(sprintf("call category_parent(%d)", 5));
$row = $result->fetch_assoc();
$result->close();
$conn->close();
echo sprintf("parent category is : cat_id = %s category_name = %s",
$row["cat_id"], $row["category_name"]);
?>
<?php
$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
$result = $conn->query(sprintf("call category_hier(%d)", 1));
echo "<table border='1'>
<tr><th>cat_id</th><th>category_name</th><th>parent_cat_id</th>
<th>parent_category_name</th><th>depth</th></tr>";
while($row = $result->fetch_assoc()){
echo sprintf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>",
$row["cat_id"],$row["category_name"],$row["parent_cat_id"],
$row["parent_category_name"],$row["depth"]);
}
echo "</table>";
$result->close();
$conn->close();
?>
full script can be found here - http://pastie.org/1244582 or see below:
-- TABLES
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_parent;
delimiter #
create procedure category_parent
(
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;
/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
create temporary table tmp engine=memory select * from hier;
while not v_done do
if exists( select 1 from categories p inner join hier on p.cat_id = hier.parent_cat_id and hier.depth = v_depth) then
insert into hier
select p.parent_cat_id, p.cat_id, v_depth + 1 from categories p
inner join tmp on p.cat_id = tmp.parent_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
from
hier
inner join categories c on hier.cat_id = c.cat_id
where
hier.parent_cat_id is null;
drop temporary table if exists hier;
drop temporary table if exists tmp;
end #
delimiter ;
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;
/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */
create temporary table tmp engine=memory select * from hier;
while not v_done do
if exists( select 1 from categories p inner join hier on p.parent_cat_id = hier.cat_id and hier.depth = v_depth) then
insert into hier
select p.parent_cat_id, p.cat_id, v_depth + 1 from categories p
inner join tmp on p.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
p.cat_id,
p.name as category_name,
b.cat_id as parent_cat_id,
b.name as parent_category_name,
hier.depth
from
hier
inner join categories p on hier.cat_id = p.cat_id
left outer join categories b on hier.parent_cat_id = b.cat_id
order by
hier.depth, hier.cat_id;
drop temporary table if exists hier;
drop temporary table if exists tmp;
end #
delimiter ;
-- TESTING (call this stored procedure from php)
call category_hier(1);
call category_hier(2);
call category_parent(5);
call category_parent(7);
Hope this helps.