Solved

Threaded sort/forum script

Posted on 2006-06-16
10
320 Views
Last Modified: 2013-12-12
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.
0
Comment
Question by:aling
  • 3
  • 3
10 Comments
 
LVL 3

Expert Comment

by:NewJorg
ID: 16920234
Take a look at my comments at
http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/Q_21882386.html
and
http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/Q_21882152.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.
0
 
LVL 10

Expert Comment

by:Khanh Doan
ID: 16920746
0
 

Author Comment

by:aling
ID: 16921362
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...
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 10

Expert Comment

by:Khanh Doan
ID: 16922199
<?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.
0
 

Author Comment

by:aling
ID: 16932601
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
0
 
LVL 10

Accepted Solution

by:
Khanh Doan earned 500 total points
ID: 16932719
Change $vbulletin->db->
to
mysql

Goodluck.
Bonmat86.
0
 

Author Comment

by:aling
ID: 16949434
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question