Re: [PHP] creating a threaded message system--sorting messages
On Jun 30, 2006, at 12:05 AM, Larry Garfield wrote: I've written such a system before more than once. You do the sorting in SQL, and then traverse the data recursively in PHP to build the tree. It's a single SQL query. Check the archives for this list for about 3-4 weeks ago, I think. We were just discussing it, and I showed some basic code examples. :-) -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 Did you mean this thread, Larry? -- http://news.php.net/php.general/238478 (on menus and submenu generation) Seems pretty identical to what I'm trying to do. Thanks and disregard my previous post if this is so. - Ben smime.p7s Description: S/MIME cryptographic signature
Re: [PHP] creating a threaded message system--sorting messages
Hi Larry, Thanks for the help. I was just coming to a similar conclusion last night after reading the warnings on this thread about querying the database recursively. I guess my test data with 7 posts is not a rigorous simulation. :-) I've looked through the posts on the PHP general list going back about 3-6 weeks and can't find the thread you speak of. I searched by your name assuming that you posted into the thread and searched by the string recursive and recursion. If you could steer me to it in some other way (subject of the thread or subject word) I would appreciate it, but only if it is not too much trouble. I can take a go at it now that the basic approach is carved out: query once, recurse the data in PHP. Thanks, Ben On Jun 30, 2006, at 12:05 AM, Larry Garfield wrote: I've written such a system before more than once. You do the sorting in SQL, and then traverse the data recursively in PHP to build the tree. It's a single SQL query. Check the archives for this list for about 3-4 weeks ago, I think. We were just discussing it, and I showed some basic code examples. :-) -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 smime.p7s Description: S/MIME cryptographic signature
Re: [PHP] creating a threaded message system--sorting messages
On Friday 30 June 2006 07:19, Ben Liu wrote:
Hi Larry,
I've looked through the posts on the PHP general list going back
about 3-6 weeks and can't find the thread you speak of. I searched by
your name assuming that you posted into the thread and searched by
the string recursive and recursion. If you could steer me to it
in some other way (subject of the thread or subject word) I would
appreciate it, but only if it is not too much trouble. I can take a
go at it now that the basic approach is carved out: query once,
recurse the data in PHP.
Well I checked my Sent file and apparently it somehow got sent to just one
person rather than the entire list. I HATE it when that happens. Here's a
copy of it below. *sigh*
-- Forwarded Message --
Subject: Re: [PHP] How do I make a HTML tree for a set of nodes?
Date: Sunday 04 June 2006 12:38
From: Larry Garfield [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
On Sunday 04 June 2006 11:27, Niels wrote:
This sounds fine -- recursion is obviously the way to go. Where can I see
your function?
I tried several different solutions over the last couple of hours, and I've
settled on only using indentation (like a nested ul/ul list), not a
fancy graph with antialised branches. It's the simplest possible recursion
but I like the results, even if it's a bit harder to grasp without
connections clearly marked.
function tree($nodes, $start, $indent=-30) {
$indent+=30;
global $tree;
foreach ($nodes as $nodeID = $node) {
if ($node['parent']!=$start) {continue;}
$tree.=div
style='margin-left:{$indent}px'{$node['name']}/div; tree($nodes,
$nodeID, $indent);
}
return $tree;
}
Yep, you're on the right track here. If your tree is a directed acyclic
graph (read: every node has at most one parent, and never loops back on
itself), then it's quite easy to do with one SQL table and one SQL query.
(Or similar, if you're not using SQL, but I've done this many times in SQL.)
First, fetch a list of all nodes, in whatever sort order you want (say,
alphabetical), ignoring the nesting. You want an array of records (an SQL
result works, or an array of objects, or an array of arrays), where each
record has at minimum its own ID and its parent ID (0 meaning it's a root
node), and some sort of label.
Then, starting at parentid=0, iterate over the list and process just those
with that parent id. Process means, in this case, for example, to print
out a list item with the name, save the current cursor, and call the function
again recursively with the id of the active node as the new parent node.
When you return, reset the cursor. The cursor stuff is so that you can pass
the array by reference rather than by value and not waste a ton of memory.
So the pseudo-code would look something like:
function foo($nodes, $parentid) {
reset($nodes);
foreach ($nodes as $node) {
if ($node's parent is $parent id) {
$output .= li
. $node (or whatever portion of it you want)
. foo($nodes, $node's id)
. /li;
}
}
if ($output) return ul$output/ul;
}
$nodes = get_my_nodes();
$list = foo($nodes, 0);
There's probably a small bug in the above somewhere since I haven't tested
it, and it would vary a bit if you're operating on an SQL result directly,
but that's the basic idea. The advantage is that you get HTML that is
structurally representative of the relationship, as well as pre-styled for
you by default by any browser.
Thanks,
Ben
On Jun 30, 2006, at 12:05 AM, Larry Garfield wrote:
I've written such a system before more than once. You do the
sorting in SQL,
and then traverse the data recursively in PHP to build the tree.
It's a
single SQL query. Check the archives for this list for about 3-4
weeks ago,
I think. We were just discussing it, and I showed some basic code
examples. :-)
--
Larry Garfield AIM: LOLG42
[EMAIL PROTECTED] ICQ: 6817012
--
Larry Garfield AIM: LOLG42
[EMAIL PROTECTED] ICQ: 6817012
If nature has made any one thing less susceptible than all others of
exclusive property, it is the action of the thinking power called an idea,
which an individual may exclusively possess as long as he keeps it to
himself; but the moment it is divulged, it forces itself into the possession
of every one, and the receiver cannot dispossess himself of it. -- Thomas
Jefferson
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] creating a threaded message system--sorting messages
On Thu, June 29, 2006 9:11 am, Ben Liu wrote: I'm trying to present the messages to the users in threaded order rather than flat. I'm having a lot of trouble figuring out how to sort the posts so they appear in the correct threaded order. I don't think I can do this purely with a SQL query. You can. The thing to do is to store the pre-order traversal as a threaded tree in your database. If you Google for those terms along with SQL, you should find some articles that will help you tame this beast. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] creating a threaded message system--sorting messages
This question might deviate from PHP into the domain of MySQL but I thought best to post here first. I'm building a message board system with PHP/MySQL. I'm trying to present the messages to the users in threaded order rather than flat. I'm having a lot of trouble figuring out how to sort the posts so they appear in the correct threaded order. I don't think I can do this purely with a SQL query. If it can be done this way, please suggest how and I'll take this question to the MySQL list. I think I have figured out the basic logic, I just have no idea how to translate it into code. Also, I may have the logic wrong. Anyhow this is what I have so far: relevant data structure loosely: post_id (unique, autoincrement, primary index) parent_id (if the post is a child, this field contains the post_id of its parent) ... 1) Query the database for all messages under a certain topic, sort by parent_id then post_id 2) Somehow resort the data so that each group of children is directly after their parent. Do this in order of ascending parent_id. Can this be done with usort() and some programatic logic/algorithm? How do you sort groups of items together rather than comparing each array element to the next array element (ie: sorting one item at a time)? Should this be done with a recursive algorithm? Anyone with experience writing code for this type of message board, or implementing existing code? Thanks for any help in advance. - Ben -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] creating a threaded message system--sorting messages
On Thursday 29 June 2006 09:11, Ben Liu wrote: relevant data structure loosely: post_id (unique, autoincrement, primary index) parent_id (if the post is a child, this field contains the post_id of its parent) ... 1) Query the database for all messages under a certain topic, sort by parent_id then post_id 2) Somehow resort the data so that each group of children is directly after their parent. Do this in order of ascending parent_id. Can this be done with usort() and some programatic logic/algorithm? How do you sort groups of items together rather than comparing each array element to the next array element (ie: sorting one item at a time)? Should this be done with a recursive algorithm? Anyone with experience writing code for this type of message board, or implementing existing code? Thanks for any help in advance. - Ben I've written such a system before more than once. You do the sorting in SQL, and then traverse the data recursively in PHP to build the tree. It's a single SQL query. Check the archives for this list for about 3-4 weeks ago, I think. We were just discussing it, and I showed some basic code examples. :-) -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it. -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
