Хорошо, работая от бэкэнда к фронтэнду...
Вы можете вызвать одну нерекурсивную хранимую процедуру (sproc) из своего php-скрипта, которая генерирует для вас иерархию сообщений. Преимущество этого подхода заключается в том, что вам нужно сделать ОДИН РАЗ вызов из php в вашу базу данных, тогда как, если вы используете встроенный SQL, вы будете делать столько вызовов, сколько существует уровней (как минимум) . Еще одним преимуществом является то, что, поскольку это нерекурсивный sproc, он чрезвычайно эффективен, а также сохраняет ваш php-код красивым и чистым. Наконец, и я должен сказать это для протокола, что вызов хранимых процедур более безопасен и эффективен, чем любой другой метод, потому что вам нужно только GRANT разрешения на выполнение пользователю вашего приложения, а хранимые процедуры требуют меньше обращений к базе данных, чем любой другой метод. другие методы, включая параметризованные запросы, для которых требуется как минимум 2 вызова для одного запроса (1 для настройки шаблона запроса в БД, другой для заполнения параметров)
Итак, вот как можно вызвать хранимую процедуру из командной строки MySQL.
call message_hier(1);
и вот набор результатов, который он создает.
msg_id emp_msg parent_msg_id parent_msg depth
====== ======= ============= ========== =====
1 msg 1 NULL NULL 0
2 msg 1-1 1 msg 1 1
3 msg 1-2 1 msg 1 1
4 msg 1-2-1 3 msg 1-2 2
5 msg 1-2-2 3 msg 1-2 2
6 msg 1-2-2-1 5 msg 1-2-2 3
7 msg 1-2-2-1-1 6 msg 1-2-2-1 4
8 msg 1-2-2-1-2 6 msg 1-2-2-1 4
Итак, теперь у нас есть возможность получить полное или частичное дерево сообщений, просто вызвав нашу sproc с любым начальным узлом, который нам нужен, но что мы собираемся делать с набором результатов??
Что ж, в этом примере я решил, что мы собираемся сгенерировать XML DOM с его помощью, затем все, что мне нужно сделать, это преобразовать (XSLT) XML, и у нас будет вложенная веб-страница сообщений.
PHP-скрипт
PHP-скрипт довольно прост, он просто подключается к базе данных, вызывает sproc и зацикливает набор результатов для построения XML DOM. Помните, что мы обращаемся к БД только один раз.
<?php
// i am using the resultset to build an XML DOM but you can do whatever you like with it !
header("Content-type: text/xml");
$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);
// one non-recursive db call to get the message tree !
$result = $conn->query(sprintf("call message_hier(%d)", 1));
$xml = new DomDocument;
$xpath = new DOMXpath($xml);
$msgs = $xml->createElement("messages");
$xml->appendChild($msgs);
// loop and build the DOM
while($row = $result->fetch_assoc()){
$msg = $xml->createElement("message");
foreach($row as $col => $val) $msg->setAttribute($col, $val);
if(is_null($row["parent_msg_id"])){
$msgs->appendChild($msg);
}
else{
$qry = sprintf("//*[@msg_id = '%d']", $row["parent_msg_id"]);
$parent = $xpath->query($qry)->item(0);
if(!is_null($parent)) $parent->appendChild($msg);
}
}
$result->close();
$conn->close();
echo $xml->saveXML();
?>
XML-вывод
Это XML, который генерирует php-скрипт. Если вы сохраните этот XML в файл и откроете его в своем браузере, вы сможете разворачивать и сворачивать уровни.
<messages>
<message msg_id="1" emp_msg="msg 1" parent_msg_id="" parent_msg="" depth="0">
<message msg_id="2" emp_msg="msg 1-1" parent_msg_id="1" parent_msg="msg 1" depth="1"/>
<message msg_id="3" emp_msg="msg 1-2" parent_msg_id="1" parent_msg="msg 1" depth="1">
<message msg_id="4" emp_msg="msg 1-2-1" parent_msg_id="3" parent_msg="msg 1-2" depth="2"/>
<message msg_id="5" emp_msg="msg 1-2-2" parent_msg_id="3" parent_msg="msg 1-2" depth="2">
<message msg_id="6" emp_msg="msg 1-2-2-1" parent_msg_id="5" parent_msg="msg 1-2-2" depth="3">
<message msg_id="7" emp_msg="msg 1-2-2-1-1" parent_msg_id="6" parent_msg="msg 1-2-2-1" depth="4"/>
<message msg_id="8" emp_msg="msg 1-2-2-1-2" parent_msg_id="6" parent_msg="msg 1-2-2-1" depth="4"/>
</message>
</message>
</message>
</message>
</messages>
Теперь вы можете отказаться от создания XML DOM и использования XSL для отображения веб-страницы, если хотите, и, возможно, просто зациклить набор результатов и напрямую отображать сообщения. Я просто выбрал этот метод, чтобы сделать мой пример максимально полным и информативным.
Скрипт MySQL
Это полный скрипт, включающий таблицы, sprocs и тестовые данные.
drop table if exists messages;
create table messages
(
msg_id smallint unsigned not null auto_increment primary key,
msg varchar(255) not null,
parent_msg_id smallint unsigned null,
key (parent_msg_id)
)
engine = innodb;
insert into messages (msg, parent_msg_id) values
('msg 1',null),
('msg 1-1',1),
('msg 1-2',1),
('msg 1-2-1',3),
('msg 1-2-2',3),
('msg 1-2-2-1',5),
('msg 1-2-2-1-1',6),
('msg 1-2-2-1-2',6);
drop procedure if exists message_hier;
delimiter #
create procedure message_hier
(
in p_msg_id smallint unsigned
)
begin
declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);
create temporary table hier(
parent_msg_id smallint unsigned,
msg_id smallint unsigned,
depth smallint unsigned
)engine = memory;
insert into hier select parent_msg_id, msg_id, v_dpth from messages where msg_id = p_msg_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 messages e inner join hier on e.parent_msg_id = hier.msg_id and hier.depth = v_dpth) then
insert into hier select e.parent_msg_id, e.msg_id, v_dpth + 1
from messages e inner join tmp on e.parent_msg_id = tmp.msg_id and tmp.depth = v_dpth;
set v_dpth = v_dpth + 1;
truncate table tmp;
insert into tmp select * from hier where depth = v_dpth;
else
set v_done = 1;
end if;
end while;
select
m.msg_id,
m.msg as emp_msg,
p.msg_id as parent_msg_id,
p.msg as parent_msg,
hier.depth
from
hier
inner join messages m on hier.msg_id = m.msg_id
left outer join messages p on hier.parent_msg_id = p.msg_id;
drop temporary table if exists hier;
drop temporary table if exists tmp;
end #
delimiter ;
-- call this sproc from your php
call message_hier(1);
Полный источник этого ответа можно найти здесь: http://pastie.org/1336407. Как вы уже заметили, я пропустил XSLT, но вы, вероятно, не пойдете по пути XML, а если и пойдете, то в Интернете есть куча примеров.
Надеюсь, вы найдете это полезным :)
ИЗМЕНИТЬ:
Добавлено немного больше данных, чтобы у вас было более одного корневого сообщения (msg_ids 1,9,14).
truncate table messages;
insert into messages (msg, parent_msg_id) values
('msg 1',null), -- msg_id = 1
('msg 1-1',1),
('msg 1-2',1),
('msg 1-2-1',3),
('msg 1-2-2',3),
('msg 1-2-2-1',5),
('msg 1-2-2-1-1',6),
('msg 1-2-2-1-2',6),
('msg 2',null), -- msg_id = 9
('msg 2-1',9),
('msg 2-2',9),
('msg 2-3',9),
('msg 2-3-1',12),
('msg 3',null); -- msg_id = 14
Теперь, если вы хотите просто получить сообщения, относящиеся к корневому узлу (начальное сообщение), вы можете вызвать исходную хранимую процедуру, передав начальный msg_id нужного вам корня. Используя новые данные выше, это будет msg_ids 1,9,14.
call message_hier(1); -- returns all messages belonging to msg_id = 1
call message_hier(9); -- returns all messages belonging to msg_id = 9
call message_hier(14); -- returns all messages belonging to msg_id = 14
вы можете передать любой msg_id, который вам нравится, поэтому, если мне нужны все сообщения ниже msg 1-2-2-1, вы должны передать msg_id = 6:
call message_hier(6); -- returns all messages belonging to msg_id = 6
Однако, если вам нужны все сообщения для всех корней, вы можете вызвать эту новую sproc, которую я создал, следующим образом:
call message_hier_all(); -- returns all messages for all roots.
Основная проблема с этим заключается в том, что по мере роста вашей таблицы сообщений она будет возвращать много данных, поэтому я сосредоточился на более конкретной sproc, которая извлекает сообщения только для данного корневого узла или запускает msg_id.
Я не буду публиковать новый код sproc, так как он практически не отличается от исходного, но вы можете найти все изменения здесь: http://pastie.org/1339618
Последнее изменение, которое вам нужно внести, находится в php-скрипте, который теперь будет вызывать новый sproc следующим образом:
//$result = $conn->query(sprintf("call message_hier(%d)", 1)); // recommended call
$result = $conn->query("call message_hier_all()"); // new sproc call
Надеюсь это поможет :)
call message_hier_all();
person
Jon Black
schedule
30.11.2010