Selecting all relevant records at once

[SyncViews]

Each of my records stores the id of the "parent" record or 0 if it's a top level record. Is there an easy way to select every record in this chain rather than this that makes tons of calls for long chains.

 

from my new topic script

...$parent_id = id of forum for topic to be created in
<?php
//get parent forum list
$getparent = $sql->prepare("SELECT forum_name, parent_id FROM forums WHERE forum_id=?");
while($parent_id != 0)
{
	$forum_id = $parent_id;
	$getparent->bind_param("i", $parent_id);
	$getparent->execute();
	$getparent->bind_result($forum_name, $parent_id);
	$getparent->fetch();
	$parents[] = array('id' => $forum_id, 'name' => $forum_name);
}
if($parents)$smarty->assign('parent_forums', array_reverse($parents));
$getparent->close();

...

//update forums
$parents_str = '';
foreach($parents as $i=>$parent)
{
	if($i==0)$parents_str .=  $parent["id"];
	else     $parents_str .= ", $parent[id]";
}

$sql->query("
	UPDATE forums
	SET	topics=topics+1, posts=posts+1,
	last_topic_name='$subject', last_topic_id=$topic_id,
	last_post_id=$post_id, last_post_time=$time,
	last_user_id=$user_id, last_user_name='$user_name'
	WHERE forum_id IN ($parents_str)
") or exit(__LINE__.':'.$sql->error);
?>

[trq]

I would suggest looking into nested sets. Theres a decent tutorial in the mysql site.

[SyncViews]

I was goign through that today using the "nested set model" from that page but I'm having some trobel with an update statement. It needs to update the last post/topic details of all the forums from the current one to the root but I'm getting an error and I'm not sure why

 

The query thats sent to the server:

UPDATE forums
SET topics=topics+1, posts=posts+1,
last_topic_name='Test topic...', last_topic_id=14,
last_post_id=19, last_post_time=1209662581,
last_user_id=1, last_user_name='SyncViews'
WHERE forum_id IN
(
SELECT parent.forum_id
FROM forums AS forum, forums AS parent
WHERE forum.left BETWEEN parent.left AND parent.right
AND forum.forum_id = 5
)

The error:

You can't specify target table 'forums' for update in FROM clause