Solved

Threaded sort/forum script

Posted on 2006-06-16
10
317 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now