Моя база данных выглядит как (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 Скажем, как мне получить верхний идентификатор chicago, это должно быть местоположение.
У меня есть запись рекурсивной функции в php или это выполнимо в mysql?
На этом веб-сайте есть действительно хороший обзор различных методов хранения иерархических данных в mysql и PHP. Чтобы ответить на ваш вопрос, самый простой способ – использовать php и рекурсию. Существуют и другие методы, которые можно использовать, например, modified preorder transversal , которое не требует нескольких запросов к базе данных. Но этот метод может быть сложнее реализовать при работе с множеством вставок и обновлений.
Другой действительно классный метод, и мой личный фаворит – это так называемая «таблица закрытия» / «отношение смежности», упомянутая в « Что является наиболее эффективным / изящным способом разбора плоской таблицы в дерево?
Что касается вашего комментария, вы в основном должны сделать цикл или рекурсивную функцию, которая выбирает родителя chicago, потом родителя родителя и так далее.
$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-сервер), большинство примеров, с которыми вы столкнетесь, потребует нескольких вызовов от php в mysql – по одному вызову за уровень в иерархии. Если у вас есть деревья со многими уровнями и много одновременных пользователей, это скоро добавит, например, дерево глубины 20 с 1000 совместимыми пользователями потребует 20K вызовов в базу данных. Следующий метод использует хранимую процедуру, и для одного и того же сценария требуется только 1000 вызовов (1 на пользователя). То, что вы делаете с набором результатов, зависит от вас: вы можете создать XML DOM, загрузить его в массив или просто вывести его как HTML, важно то, что у вас есть все дерево в наборе результатов за один раз.
EDIT: добавлена хранимая процедура категории_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);