PHP / MySQL Threaded Discussion: One Query

[ensnare]

I'm attempting to create a threaded comment system involving PHP /
MySQL. Currently, I have a table called comments which looks like:

Table Comments: (comment_id, comment_root_id , comment_parent_ id, text,
datetime_create d).

Most of the tutorials that I've found online discuss how to thread the
results from the database with recursive query calls. This is all to
the good; however, with lengthy discussion, this solution becomes slow
and unacceptable.

Is there a way to retrieve all the comments from the database in one
query (e.g. SELECT * FROM comments WHERE comment_root_id = $id) and
then to do the ordering/threading in php with some function (i'm
guessing recursive to handle infinite depths)?

What would such a function look like?

I found a function online that recurses through an array. Perhaps
something like this could be modified to thread the comments?

///============
//Sample thread
$thread_info_ar r['message_id'] = array(154, 155, 156, 157, 161, 163);
$thread_info_ar r['reply_id'] = array(0, 154, 155, 154, 155, 157);
$thread_info_ar r['message_title'] = array('Post 154 (initial post)',
'Post 155 (reply to post 154)', 'Post 156 (reply to post 155)', 'Post
157 (reply to post 154)', 'Post 161 (reply to post 155)', 'Post 163
(reply to post 157)');
$thread_info_ar r['date_posted'] = array('2004-06-01', '2004-06-02',
'2004-06-03', '2004-06-04', '2004-06-05', '2004-06-06');

$iteration = 0;

reord_thread($t hread_info_arr['message_id']);

function reord_thread(&$ data)
{
global $thread_info_ar r, $iteration;

if (is_array($data ))
{
array_walk($dat a, 'reord_thread') ;
}
else
{
echo 'Date: ' . $thread_info_ar r['date_posted'][$iteration] . ' Title:
' . $thread_info_ar r['message_title'][$iteration] . '<br>';
$iteration++;
}
}
///============

Thanks in advance. Any help would be greatly appreciated. Enjoy your
day.

[John Unleaded Smith]

You're close.

Have a look at my large post about this I made a year or so ago (Bless
Google ;)).

http://groups.google.com.au/groups?q...ine.net&rnum=1

It's an example with code.

Other threads of interest:
http://groups.google.com.au/groups?h...line.net#link1
http://groups.google.com.au/groups?h...erlin.de#link1

Cheers.

ensnare wrote:

I'm attempting to create a threaded comment system involving PHP /
MySQL. Currently, I have a table called comments which looks like:

Table Comments: (comment_id, comment_root_id , comment_parent_ id, text,
datetime_create d).

Most of the tutorials that I've found online discuss how to thread the
results from the database with recursive query calls. This is all to
the good; however, with lengthy discussion, this solution becomes slow
and unacceptable.

Is there a way to retrieve all the comments from the database in one
query (e.g. SELECT * FROM comments WHERE comment_root_id = $id) and
then to do the ordering/threading in php with some function (i'm
guessing recursive to handle infinite depths)?

What would such a function look like?

I found a function online that recurses through an array. Perhaps
something like this could be modified to thread the comments?

///============
//Sample thread
$thread_info_ar r['message_id'] = array(154, 155, 156, 157, 161, 163);
$thread_info_ar r['reply_id'] = array(0, 154, 155, 154, 155, 157);
$thread_info_ar r['message_title'] = array('Post 154 (initial post)',
'Post 155 (reply to post 154)', 'Post 156 (reply to post 155)', 'Post
157 (reply to post 154)', 'Post 161 (reply to post 155)', 'Post 163
(reply to post 157)');
$thread_info_ar r['date_posted'] = array('2004-06-01', '2004-06-02',
'2004-06-03', '2004-06-04', '2004-06-05', '2004-06-06');

$iteration = 0;

reord_thread($t hread_info_arr['message_id']);

function reord_thread(&$ data)
{
global $thread_info_ar r, $iteration;

if (is_array($data ))
{
array_walk($dat a, 'reord_thread') ;
}
else
{
echo 'Date: ' . $thread_info_ar r['date_posted'][$iteration] . ' Title:
' . $thread_info_ar r['message_title'][$iteration] . '<br>';
$iteration++;
}
}
///============

Thanks in advance. Any help would be greatly appreciated. Enjoy your
day.

[Damien]

Hello,
I read a tutorial on another way to store "trees". Hmmmm... It was
called "interval representation" from what I gather. You can read a full
tutorial (in French, sorry) there :
http://sqlpro.developpez.com/Tree/SQL_tree.html

A bit more complex, but apprently efficient.

HTH, BR,
Damien

[Phil Roberts]

en*****@gmail.c om (ensnare) emerged reluctantly from the curtain
and staggered drunkenly up to the mic. In a cracked and slurred
voice he muttered:

Most of the tutorials that I've found online discuss how to
thread the results from the database with recursive query calls.
This is all to the good; however, with lengthy discussion, this
solution becomes slow and unacceptable.

I use a system which is simple and doesn't rely on recursion. I
basically store the entire thread "path" as a row field.

So basically I would have the following table structure:

ID | Name | Path
----+-------------------------+----------
0 | Category One | 00
1 | Sub-category of cat one | 00_01
2 | Sub-cat of cat id #1 | 00_01_02
3 | Category Two | 00
4 | Category Three | 00
5 | Sub-cat of cat three | 00_04
6 | Sub-sub cat of cat #3 | 00_04_05

Want to get the entire tree structure? Just do an ORDER BY on the
path column and use some PHP code like the following for
formatting:

while ($result = mysql_fetch_ass oc($query)) {
$nesting_depth = count(explode(" _", $result['category_path']));
$branch = str_repeat("--", $nesting_depth) ;
echo "| $branch {$result['name']}";
}

This should result in:

| Category One
| -- Sub-category of cat one
| ---- Sub-cat of cat id #1
| Category Two
| Category Three
| -- Sub-cat of cat three
| ---- Sub-sub cat of cat #3

Want to get a particular tree branch? Just run a "SELECT * FROM
table WHERE path LIKE '00_01%' ORDER BY path ASC" query.

How you choose to store the path field data is up to you. I'm using
this type of system for a photo gallery system and so far it's
working fine.

--
Phil Roberts | Without me its just aweso. | http://www.flatnet.net/

"Mankind differs from the animals only by a little,
and most people throw that away."
- Confucious

[Jochen Daum]

Hi,

On 27 Jun 2004 23:43:23 -0700, en*****@gmail.c om (ensnare) wrote:

I'm attempting to create a threaded comment system involving PHP /
MySQL. Currently, I have a table called comments which looks like:

Table Comments: (comment_id, comment_root_id , comment_parent_ id, text,
datetime_creat ed).

Most of the tutorials that I've found online discuss how to thread the
results from the database with recursive query calls. This is all to
the good; however, with lengthy discussion, this solution becomes slow
and unacceptable.

Its a bit long to explain out of the box, but look up "nested set
model", which is well described in books of Joe Celko. It needs only
one self referencing query for a whole tree (discussion)

HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Tim Van Wassenhove]

In article <om************ *************** *****@4ax.com>, Jochen Daum wrote:

Hi,

On 27 Jun 2004 23:43:23 -0700, en*****@gmail.c om (ensnare) wrote:

I'm attempting to create a threaded comment system involving PHP /
MySQL. Currently, I have a table called comments which looks like:

Table Comments: (comment_id, comment_root_id , comment_parent_ id, text,
datetime_crea ted).

Most of the tutorials that I've found online discuss how to thread the
results from the database with recursive query calls. This is all to
the good; however, with lengthy discussion, this solution becomes slow
and unacceptable.

Its a bit long to explain out of the box, but look up "nested set
model", which is well described in books of Joe Celko. It needs only
one self referencing query for a whole tree (discussion)

And when you've read that, surf to
http://pear.php.net/package/DB_NestedSet and find an implementation ;)

--
Tim Van Wassenhove <http://home.mysth.be/~timvw>