[Solved] To Rebuild Binary Tree

[CreatixEA]

Hi,

I got trouble importing my own forums data to the phpBB3 forum system. So I wrote a script to repair the binary tree if broken:

Code: Select all

mysql_connect('localhost', 'username', 'password');
mysql_select_db('phpbb');

$c = -1;
$final = array();

function rebuild_children($id)
{
        global $c, $final;

        // LEFT
        $c++;
        $left = $c;

        $r = mysql_query("SELECT * FROM phpbb_forums WHERE parent_id = $id");

        while ($f = mysql_fetch_array($r))
        {
                rebuild_children($f['forum_id']);
        }

        // RIGHT
        $c++;
        $right = $c;

        $final[$id] = array('left' => $left, 'right' => $right);
}

rebuild_children(0);

foreach ($final as $k => $f)
{
        mysql_query("UPDATE phpbb_forums SET right_id = $f[right], left_id = $f[left] WHERE forum_id = $k");
}

[Tesla2k]

wow thank you man your script saved my day!

[goBlu]

Although the above code works .... it's painfully slow. It took about 10 minutes to rebuild a tree of 700 forums, the below code does it in 1 second.

Code: Select all

FixTree();

function ConnectToDB($SQL_Query, $mode) {
			
	$dbhost = "";
	$dbuser = "";
	$dbpass = "";
	$dbname = "";

	$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
	mysql_select_db($dbname) or die("Could not select" & $dbname);
	$result = mysql_query($SQL_Query);

	switch ($mode) {
		case 0: // return field results
			$num_rows = mysql_num_rows($result);
			for ($i = 0; $i < $num_rows; $i++) {
				$row[$i] = mysql_fetch_array($result,MYSQL_ASSOC);
			}
			return $row;
			break;
		case 1: // return how many rows returned
			$rows = mysql_num_rows($result);
			return $rows;
			break;
		case 2: // return id of the record that was just created
			$insert_id = mysql_insert_id();
			return $insert_id;
			break;
		default: // return -1
			return -1;
			break;
	}

}

function FixTree() {
	
	$SQL_Query = "SELECT parent_id, forum_id, forum_type FROM forums WHERE forum_type = 0 ORDER by parent_id, forum_name";
	$type_results = ConnectToDB($SQL_Query, 0);
	$First_Row = 1;
	$CurRow = $First_Row;
	foreach ($type_results as $row_type) {
		$SQL_Query = "SELECT parent_id, forum_id, forum_type FROM forums WHERE parent_id = " . $row_type['forum_id'] . " ORDER by forum_type, parent_id, forum_name";
		$result = ConnectToDB($SQL_Query, 0);
		if (sizeof($result) != 0) {
			foreach ($result as $row) {
				$SQL_Query = "UPDATE forums SET left_id = " . (++$CurRow) . ", right_id = " . (++$CurRow) . " WHERE forum_id = " . $row['forum_id'];
				ConnectToDB($SQL_Query, 2);
			}
		}
		$SQL_Query = "UPDATE forums SET left_id = " . $First_Row . ", right_id = " . (++$CurRow) . " WHERE forum_id = " . $row_type['forum_id'];
		ConnectToDB($SQL_Query, 2);
		$First_Row = ++$CurRow;
	}
}

[noisehosting]

Hi

Any help on how to get this working? I tried it without success. Can someone please help me solved this issue?

As my post here : http://www.phpbb.com/community/viewtopi ... 6&t=924885

This is my issue :

Please can someone give me advise on how to fix this issue.

After searching for info about the issue, i came across a post that says it has to do with the SQL left_id and right_id that's wrong?

I also read that to fix the issue, the binary tree must be rebuilt.

Can anyone PLEASE help me with this? Any help will be appreciated.

I must note that this issue started happening after we moved servers and upgraded the server from php4 to php5.Although SQL is still on version 4

Thanks

[CTXC]

This is a very helpful post, I've spent days trying to convert this obscure MSSQL/ASP.NET forum to phpBB3.

This post saved the day!

[derybero]

Hi

Any help on how to get this working? I tried it without success. Can someone please help me solved this issue?

As my post here : http://www.phpbb.com/community/viewtopi ... 6&t=924885

This is my issue :

Please can someone give me advise on how to fix this issue.

After searching for info about the issue, i came across a post that says it has to do with the SQL left_id and right_id that's wrong?

I also read that to fix the issue, the binary tree must be rebuilt.

Can anyone PLEASE help me with this? Any help will be appreciated.

I must note that this issue started happening after we moved servers and upgraded the server from php4 to php5.Although SQL is still on version 4

Thanks

I've got the same problem, and so far this post "[Solved] To Rebuild Binary Tree" might be the solution.

I don't know how to even run those codes, do you have to do it in an SQL Query?

Before:



After:

[ottom]

Dealt with this challenge too recently. So here are some more optimizations...

Theory and a general function found here:
http://www.sitepoint.com/print/hierarch ... -database/

Now adjusted to work with phpBB, using DBAL:

Code: Select all

function rebuild_tree($parent, $left) {
   global $db;
   
   
   // the right value of this node is the left value + 1 
   $right = $left+1; 

   // get all children of this node 
   $result = $db->sql_query("SELECT forum_id FROM phpbb_forums WHERE parent_id = $parent"); 

   while ($row = $db->sql_fetchrow($result)) { 
       $right = rebuild_tree($row['forum_id'], $right); 
   } 

   $db->sql_query("UPDATE phpbb_forums SET left_id = $left, right_id = $right WHERE forum_id = $parent"); 

   // return the right value of this node + 1 
   return $right+1; 
}

The function is recursive, which means it calls itself. You must start the function with the ID of the root forum, example:

Code: Select all

$root_forum_id = 1; // first root forum

$parent = $root_forum_id;
$left = $root_forum_id;
rebuild_tree($parent, $left)


$root_forum_id = 1; // second root forum

$parent = $root_forum_id;
$left = $root_forum_id;
rebuild_tree($parent, $left)

[ottom]

For those who do not like the recursive programming style, may use the function by goBlu. Here is ConnectToDB adjusted to work with DBAL:

Code: Select all

function ConnectToDB($SQL_Query, $mode) {

	global $db;
   
   $result = $db->sql_query($SQL_Query);

   switch ($mode) {
      case 0: // return field results
		while ($cur_row = $db->sql_fetchrow($result))
		{
				$row[$i] = $cur_row;
         }
         return $row;
         break;
      case 1: // return how many rows returned
         $num_rows = $result->numRows();
         return $rows;
         break;
      case 2: // return id of the record that was just created
         $insert_id = $db->sql_nextid();
         return $insert_id;
         break;
      default: // return -1
         return -1;
         break;
   }

}

[derybero]

The STK ships with a set of tools pre installed. In the latest version the pre installed tools are:
Support tools

This category contains tools that perform support related tasks.

* Fix Left/Right ID's Repairs the tree structure of the forums and modules.

I didn't know this existed, now I will take the time next when my binary tree gets messed up.

Good Luck!