Как я могу рекурсивно получить "родительский идентификатор" строк в этой таблице MySQL?
Моя база данных выглядит так (pligg cms, пример данных)
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
Допустим, как я могу получить top parentID из Чикаго, это должно быть местоположение.
Должен ли я написать рекурсивную функцию в PHP или это выполнимо в MySQL?
3 ответа
Этот веб-сайт имеет действительно хороший обзор различных методов хранения иерархических данных в MySQL и PHP. Чтобы ответить на ваш вопрос, проще всего использовать php и рекурсию. Есть и другие методы, которые вы можете использовать, такие как modified preorder transversal, которые не требуют нескольких запросов к базе данных. Но этот метод может быть более сложным для реализации при работе с большим количеством вставок и обновлений.
Другой действительно крутой метод, и мой личный фаворит - это так называемая "таблица замыкания" / "отношение смежности", упомянутая в разделе "Какой самый эффективный / элегантный способ разобрать плоскую таблицу в дерево?"
Что касается вашего комментария, вы должны сделать цикл или рекурсивную функцию, которая выбирает родителя Чикаго, потом родителя родителя и так далее.
$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);
Тогда в стеке будут содержаться родители из Чикаго (то есть из США)
Поскольку mysql еще не поддерживает такие функции, как connect by (oracle) или общие выражения таблиц (sql server), для большинства примеров, с которыми вы столкнетесь, потребуется несколько вызовов из php в mysql - по одному вызову на уровень в иерархии. Если у вас есть деревья с множеством уровней и множеством одновременных пользователей, это скоро сложится, например, дерево глубины 20 с 1000 одновременно работающих пользователей потребует 20 000 вызовов в базу данных. Следующий метод использует хранимую процедуру и для этого же сценария требуется только 1000 вызовов (1 на пользователя). Что вы делаете с результирующим набором, зависит от вас: вы можете сгенерировать DOM XML, загрузить его в массив или просто вывести его в виде HTML, важно то, что у вас есть все дерево в наборе результатов за один вызов.
РЕДАКТИРОВАТЬ: добавлена хранимая процедура category_parent, изначально неправильно прочитав ваш вопрос.
Пример вызова MySQL
call category_hier(1);
call category_hier(2);
call category_parent(5);
call category_parent(7);
Пример php скрипта
<?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();
?>
Полный сценарий можно найти здесь - http://pastie.org/1244582 или см. ниже:
-- 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);
Надеюсь это поможет.
Основано на ответе @GWW
function getLocationArray($start){
$link=dbConnect();//a function returning the link with your db
$stack = array();
$parent = $start;
while($parent != 0){
$query='SELECT catName,parentID from myTable where catId='.$parent;
$result = mysql_query($query,$link);
while($row = mysql_fetch_assoc($result)){
$parent=$row['parentID'];
$name=$row['catName'];
$stack[] = $name;
/*foreach($row as $cname => $cvalue){
}*/
}
}
$stack = array_reverse($stack);
return $stack;
}
var_dump($stack);