[SOLVED] Recursion question (or something else if you know an alternative)

[phil88]

Basically, I have the following structure in a mysql database for a simple "post & reply" type system;

+-------+---------+------+----+
|post_id|parent_id|author|post|
+-------+---------+------+----+

 

So, when someone makes a post, it is added to the database, if it's a root post (not a reply to anything) then it's parent_id will be 0. If the post is a reply to another post, the parent_id will correspond with the post_id of the post that it's replying to.

 

 

I'm a bit stumped as to how to get the posts out though; here's what I've come out with so far, which doesn't work;

function get_child($id){
$query = mysql_query("	SELECT * FROM `posts` WHERE `parent_id` = '$id'	") or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
	get_child($row['id']);
	echo $row['author'] ."-". $row['post'] ."<br/>";
}
}

 

 

I'm thinking recursion is the way forward as there would be potentially an unlimited number of child posts coming from each root post.

 

 

Any ideas?

[HuggieBear]

Phil,

 

How did you solve this?  Did you use a self-join?

 

Regards

Huggie

[phil88]

No, I did it a totally different way;

function show_posts($id){
$query = mysql_query("	SELECT * FROM `posts` WHERE `parent_id` = '$id'") or die(mysql_error());
while($row = mysql_fetch_assoc($query)){
	$parent_id = $row['parent_id'];
	$query2 = mysql_query("	SELECT * FROM `posts` WHERE `post_id` = '$parent_id'	ORDER BY `post_id` DESC") or die(mysql_error());
	$row2 = mysql_fetch_array($query2);
	$reply_string = $row2[2] . " at " . date('h:i A \- m\/d\/y', $row2[5]);

                #
                # echo formatted output here
                #
	get_roots($row['post_id']);
}
}

 

Apologies if the variable names aren't particularly meaningful, I was just tinkering with stuff and then it suddenly worked so figured I'd just leave it be