asked on

Threaded sort/forum script

Hi there,

I'm looking for a way to display a threaded forum/message board with php and mysql.

I have a working script, but it 'costs' a query per reply. My idea was to fetch all posts in a topic, sort them and assign a 'level' to them...

id=1 parent=0 (top post)
id=2 parent=1 (reply on id1)
id=3 parent=1 (another reply on id1)
id=4 parent=2 (reply on id2)

Should become:

id1
|--id2
|--|--id4
|--id3

This is my current php script:

$RESULT[0]=dbQuery("SELECT * FROM nested WHERE parent=0");
while ($ARRAY[0]=mysql_fetch_array($RESULT[0])) { // as long as there are top-posts
echo "Post id: ".$ARRAY[0]['id']." \"".$ARRAY[0]['text']."\"<br>"; // echo the top-post
$LEVEL=1;
while (1) {
$RESULT[$LEVEL]=dbQuery("SELECT * FROM nested WHERE parent=".$ARRAY[$LEVEL-1]['id']); // select all leafs of the post
while (1) { // as lons as there are items in the current level
$ARRAY[$LEVEL]=mysql_fetch_array($RESULT[$LEVEL]);
$AANTAL[$LEVEL]=mysql_num_rows($RESULT[$LEVEL]);
(!isset($TELLER[$LEVEL])) ? $TELLER[$LEVEL]=1 : $TELLER[$LEVEL]++;
if ($TELLER[$LEVEL]<=$AANTAL[$LEVEL]) {
echo createPost($LEVEL,$ARRAY[$LEVEL]);
$RESULT[$LEVEL+1]=dbQuery("SELECT * FROM nested WHERE parent=".$ARRAY[$LEVEL]['id']); // check if the post has replies
if (mysql_num_rows($RESULT[$LEVEL+1])>0) { // are there replies?
$LEVEL++; // got to them!
}
}
else $LEVEL--;
if ($LEVEL<1) break;
}
unset($TELLER);
if ($LEVEL<1) break;
}
echo "<br>";
}

-tnx-

Harold.

NewJorg

Take a look at my comments at
https://www.experts-exchange.com/questions/21882386/Recursive-Tree-PHP-and-Breadcrumbs.html
and
https://www.experts-exchange.com/questions/21882152/Need-help-with-query.html

In both I used an recursive function to display a tree and it uses only one query because it all get loaded into a variable. In a forum you should look for a way to only select all data for one thread.

Khanh Doan

https://www.experts-exchange.com/questions/21887611/Urgent-need-to-display-multiple-dimensions.html

you can see my comment here

Bonmat86.

aling

ASKER

http://www.tourbase.ru/zink/ultratreedemo.html uses 501 queries in it's example, so it's unusable...

@NewJorg and Bonmat: do you have a copy/pastable thread creation script (which doesn't use a query per item) somewhere? The suggested Q's only have snippets of code...

Not to be lazy, but i want to see the script working -easely- or I will continue my search...

Khanh Doan

<?php
$totalthread = '1';
$maxid = '1';

$query = $vbulletin->db->query("SELECT * FROM nested ORDER BY id ASC");

while ($threadtemp = $vbulletin->db->fetch_array($query))
{
      $id = $threadtemp['id'];
      if ($maxid < $id) $maxid = $id;

      $thread[$totalthread]['id'] = $threadtemp['id'];
      $thread[$totalthread]['parent_id'] = $threadtemp['parent_id'];
      $thread[$totalthread]['name'] = $threadtemp['name'];
      $totalthread++;
}

if ($totalthread == '1')
{
      $thread_list_bit = $vbphrase['music_nocat'];
}
else
{
      for (tid = '1'; $tid <= $maxid; $tid++)
      {
            if ($thread[$cid]['parent_id'] == '0')
            {
                  $thread_id = $cat[$tid]['cat_id'];
                  $thread_name = $cat[$tid]['name'];
                  $par_num = '0';
                  $thread_list_sub = subcat($thread_id, $thread, $maxid);

                  $thread_list_bit .= $thread_name . "<br>";
                  $thread_list_bit .= $sub_thread_sub;
            }
      }
}

$thread_list = $thread_list_bit;

echo $thread_list;

//########################### Sub Thread ###########################//
function subcat($parent_id, $thread, $maxid)
{
for ($sub_id = 0; $sub_id <= $maxid; $sub_id++)
{
if ($thread[$sub_id]['parent_id'] == $parent_id)
{
$thread_id = $thread[$sub_id]['id'];
$thread_name = $thread[$sub_id]['title'];

for ($n = '1'; $n <= $par_num; $n++) $sub_thread_ .= '<img src="clear.gif" width="15" border="0" class="inlineimg">';

$sub_thread_sub = subcat($thread_id, $thread, $maxid);

$sub_thread .= $sub_thread_ . $thread_name . "<br>" . $sub_thread_sub;
}
}

return $sub_thread;
}
?>

is this ok for you ?
Please change the field name to make it work with your database.
<img src="clear.gif" width="15" border="0" class="inlineimg"> // You can replace it with |-- or something else you want.

Bonmat86.

aling

ASKER

Checking it out right now...

Oops:

Notice: Undefined variable: vbulletin in /var/www/sait.nl/*****/public/test.php on line 67

Notice: Trying to get property of non-object in /var/www/sait.nl/*****/public/test.php on line 67

Fatal error: Call to a member function query() on a non-object in /var/www/sait.nl/******/public/test.php on line 67

ASKER CERTIFIED SOLUTION

Khanh Doan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aling

ASKER

I've rewritten parts of my origional script to use arrays instead of single queries.

The number of queries have been brought down to 1 for all top-posts and 1 per thread. Parse times with 5 items is more or less equal, but with 20 items it's already twice as fast!

I now use this method:

loop this as long as there are top-posts {
fill an array with all posts belonging to this top-post
level=1
loop this forever {
create an array with all posts belonging to parent (level-1)
count the items in that array
if the counter <= items in array
echo the post and go one level higher
else go one level lower
if level<1 break
}
}

if somebody is interested in the code, just let me know!

Cheers!

Harold.