By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,594 Members | 3,680 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,594 IT Pros & Developers. It's quick & easy.

PHP / MySQL Threaded Discussion: One Query

P: n/a
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_created).

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_arr['message_id'] = array(154, 155, 156, 157, 161, 163);
$thread_info_arr['reply_id'] = array(0, 154, 155, 154, 155, 157);
$thread_info_arr['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_arr['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($thread_info_arr['message_id']);

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

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

Thanks in advance. Any help would be greatly appreciated. Enjoy your
day.
Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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_created).

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_arr['message_id'] = array(154, 155, 156, 157, 161, 163);
$thread_info_arr['reply_id'] = array(0, 154, 155, 154, 155, 157);
$thread_info_arr['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_arr['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($thread_info_arr['message_id']);

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

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

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

Jul 17 '05 #2

P: n/a
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
Jul 17 '05 #3

P: n/a
en*****@gmail.com (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_assoc($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
Jul 17 '05 #4

P: n/a
Hi,

On 27 Jun 2004 23:43:23 -0700, en*****@gmail.com (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_created).

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/
Jul 17 '05 #5

P: n/a
In article <om********************************@4ax.com>, Jochen Daum wrote:
Hi,

On 27 Jun 2004 23:43:23 -0700, en*****@gmail.com (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_created).

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>
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.