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.
alingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NewJorgCommented:
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
Khanh DoanSenior DeveloperCommented:
0
alingAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Khanh DoanSenior DeveloperCommented:
<?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
alingAuthor Commented:
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
Khanh DoanSenior DeveloperCommented:
Change $vbulletin->db->
to
mysql

Goodluck.
Bonmat86.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alingAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.