tree-view threaded message board ?

[Kagey]

Hey all,

This really applies to all web languages and databases:

Is it possible to display a set number of threads and their replies in a tree-view like style, without making a separate database query for each tree level of each thread (this would retrieve the messages whose parent id match the current id for each level of each thread). Or is this a situation where i can say goodbye to efficency

Thanks

[chrisjk]

Try this. It only makes 2 database calls regardless of number of replies

<?php

// connect to db
$mysql_db = mysql_connect('localhost', 'root', '');
$mysql_select = mysql_select_db('forum', $mysql_db);

$postid = $_REQUEST[postid];

// check PostID
if ($postid == '') {
    echo 'Please call script with a postid!';
    exit;
}

// get post
$post_result = mysql_query("SELECT * FROM posts WHERE PostID='$postid'");

// get posts with same parent id
$children_result = mysql_query("SELECT * FROM posts WHERE PostParentID='$postid'");

echo '<p>';
while ($post_row = mysql_fetch_array($post_result)) {
    // print post
    echo "Post ID: $post_row[PostID]<br>";
    echo "Subject: $post_row[Subject]<br>";
    echo "Message: $post_row[Message]";
}
echo '</p>';

echo '<p><b>Replies to this post:</b></p>
<p>';
while ($children_row = mysql_fetch_array($children_result)) {
    // print children
    echo "<a href=\"$_SERVER[PHP_SELF]?postid=$children_row[PostID]\">Subject: $children_row[PostSubject]</a><br>";
}
echo '</p>';

?>

This is the SQL for the table I used:

CREATE TABLE posts (
  PostID mediumint(9) NOT NULL auto_increment,
  PostParentID mediumint(9) NOT NULL default '0',
  PostSubject varchar(255) NOT NULL default '',
  PostMesaage varchar(255) NOT NULL default '',
  PRIMARY KEY  (PostID),
  KEY PostParentID (PostParentID)
) TYPE=MyISAM;

[Kagey]

Thanks chris,

What if there were replies to the replies of the original post:

- Original Post
|-One Reply
|-Two Reply
||-Reply to Two Reply
|||-third level reply
|third reply

something to that affect?

[The Hobo]

Personally, I don't like unlimited level boards like that. Its much easier and better, in my opinion, to have every reply a reply to the thread, and using stuff like quotes and subjects (ie "Re: this post") than having TreeViewing.

With the boards I'm working on, I only have one SQL statment:

SELECT * FROM vbsPosts WHERE id='$selectedtheadid' OR parentID='$selectedthreadid'

Then when you arrange by date, the first one should always be the thread starter. You don't even have to arrange, since the IDs should be lined up correct also.

But as for how to do what you want, I don't know if it'd be possible with only one SQL query.

[chrisjk]

Originally posted by Kagey
Thanks chris,

What if there were replies to the replies of the original post:

- Original Post
|-One Reply
|-Two Reply
||-Reply to Two Reply
|||-third level reply
|third reply

something to that affect?

I see your point

I'll think about it, but to be honest no, you'll have to loop through each child examining them for children and so on. Recursive function here we come

[Kagey]

yeah, i know how to do it with a recursive function, just check for messages with parent id's equal to the current id, and so on. I think i may just do what hobo said and keep it to 1 level, for efficiency sake.

hobo: what sql query do you use to count the amount of replies when displaying the top level thread list? do you create 2 instances of the same table and do a count() on one of them?

thanks

[The Hobo]

I used to actually store the number of replies in a few ("replies") in the post table. If the post was a reply, this field was blank. Then I realized that was a waste of space and now I just do something like:

$replies = mysql_num_rows(mysql_query("SELECT * FROM posts WHERE parentID='$ourthreadsID'"));

I'm not sure what you mean by "create 2 instances of the same table?"

I'll be making a beta release of my boards as soon as I get all the admin/mod options built in (and I have to get the search woked out too) so you can check out my code (even though it's not treeview style like you want)

[Kagey]

SELECT tbl1.msg_id, tb1.subjct, count(tb2.parent_id) AS reply_total FROM messages AS tb1, messages AS tb2 WHERE tb1.msg_id = tb2.parent_id GROUP BY tb2.parent_id

I don't know if that would work, but i remember seeing something like it a very long time ago.

do your forums have syntax highlighting for code? i want to figure out how to do that, its really starting to chew on me.

[The Hobo]

Originally posted by Kagey

do your forums have syntax highlighting for code? i want to figure out how to do that, its really starting to chew on me.

As of yet, no. But I implimented "posting hacks" so it'd be easy to set one up.

do you want like a [php] tag or a [Highlight=VB] tag? [code] doesn't have any syntax highlighting, but for PHP syntax highlighting look into the highlight_string() function.

For vbcode, check out this example I wrote on pscode: http://www.planet-source-code.com/vb...d=621&lngWId=8

[Kagey]

ok thanks, do you understand where i was going with the sql statement for counting replies?

also for the syntax higlighting, i want to make it so i can do any language. have a list of keywords and their colours (similar to some code editors, but not as much as that). then i could just go through the code and look for block comments or strings and ignore everything until i find the end character(s). and if i am not currently in a "searching for end character(s) mode" just see if the current word is in one of the lists. i think that is kinda what you did with your format vb code function.

[The Hobo]

Originally posted by Kagey
ok thanks, do you understand where i was going with the sql statement for counting replies?

No. I'm not really good with SQL functions. I'd have to give it a try and work with it.

Originally posted by Kagey
also for the syntax higlighting, i want to make it so i can do any language. have a list of keywords and their colours (similar to some code editors, but not as much as that). then i could just go through the code and look for block comments or strings and ignore everything until i find the end character(s). and if i am not currently in a "searching for end character(s) mode" just see if the current word is in one of the lists. i think that is kinda what you did with your format vb code function.

Yes, that's basically how I did it.