Ben Liu wrote:
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
Just throwing an idea out there, but you can do the sorting entirely in
the SQL query. The trick is to figure out the best way.
The first idea that came to mind (and it sucks, but it works), is a text
field with padded numbers separated by dots, and the number is the
position in relation to the parent. So, with this:
Post 1
Post 3
Post 5
Post 6
Post 4
Post 7
Post 2
Post 8
Now, to the helper field would contain this for each post:
Post 1: 1
Post 2: 2
Post 3: 1.1
Post 4: 1.2
Post 5: 1.1.1
Post 6: 1.1.2
Post 7: 1.2.1
Post 8: 2.1
Now, by pure ascii sorting in that field, that would sort out to:
Post 1: 1
Post 3: 1.1
Post 5: 1.1.1
Post 6: 1.1.2
Post 4: 1.2
Post 7: 1.2.1
Post 2: 2
Post 8: 2.1
Which is the correct sort order. The depth of the post (how far to
indent it?) could be told in PHP by counting the number of periods, or
storing it in the database.
Now, how to figure out what to put in that field for each post? We need
to do two things. First, each post needs to store the number of
children. Next, when a new post is made, we do three things (Keeping in
mind that in real life I'd pad each "entry" in the sort helper field
with zeros on the left up to some large number):
1) Get the sort helper field of the parent and the parent's child count
field
2) Take the parent's sort help field, and add on a period and the
parent's child count plus one, insert the child.
3) Update the parent's child count.
OK, now, this method sucks. It's slow, and limits the number of child
posts to whatever you pad (In itself, not a big issue, if each post can
only have, say, a thousand direct childs (which each themselves can have
a thousand childs), not a huge issue). The slow part from ascii sorting
everything is the problem, I'd think.
I've never done threaded anything before, so I assume there's a better
solution. I'm just saying that the job CAN be done entirely with SQL
sorting. And probably faster than your proposed method of resorting
everything once PHP gets ahold of it. It should be noted that you'd need
each post to have a sort of superparent field that stored the topmost
parent so that you could do something simple like selecting ten
superparents and all their children.
Regards, Adam.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
