I would also go for ParentId and recursive function:
I had done something like this before. I don’t want to say that this is the efficient way. I would like to have some comments on this way too.
The table strcture is something like this:
CREATE TABLE `comments` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`parent_id` INT(10) NOT NULL DEFAULT '0',
`comment` TEXT NOT NULL,
`date_added` DATETIME NOT NULL,
`status` SMALLINT(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
);
And data will be stored something like this:
insert into `comments`(`id`,`parent_id`,`comment`,`date_added`,`status`) values
(1,0,'Comment 1','2010-02-15 08:40:45',1),
(2,0,'Comment 2','2010-02-15 08:41:15',1),
(3,0,'Comment 3','2010-02-15 08:42:05',1),
(4,1,'Reply 1','2010-02-15 08:44:05',1),
(5,4,'Reply 1.1','2010-02-15 08:40:55',1),
(6,4,'Reply 1.2','2010-02-15 09:10:55',1),
(7,6,'Reply 1.2.1','2010-02-15 10:20:25',1),
(8,6,'Reply 1.2.2','2010-02-15 11:30:15',1),
(9,2,'Reply 2','2010-02-14 11:00:00',1),
(10,9,'Reply 2.1','2010-02-14 10:00:00',1),
(11,9,'Reply 2.2','2010-02-14 12:00:00',1),
(12,10,'Reply 2.2.1','2010-02-14 13:30:00',1),
(13,10,'Reply 2.2.2','2010-02-14 14:00:00',1),
(14,3,'Reply 3','2010-02-13 07:00:00',1),
(15,14,'Reply 3.1','2010-02-13 07:30:00',1),
(16,14,'Reply 3.2','2010-02-13 08:00:00',1),
(17,15,'Reply 3.1.1','2010-02-13 08:20:00',1),
(18,16,'Reply 3.2.1','2010-02-13 08:40:00',1);
Now my PHP script is as below.
mysql_connect("localhost", "root", "raju") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());
$data = array();
$sql = "SELECT * FROM comments WHERE status=1 ORDER BY date_added DESC";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_object($result)){
$parent = !empty($row->parent_id) ? $row->parent_id : false;
if($parent)
$data["parent_" . $parent][] = $row;
else
$data["main"][] = $row;
}
foreach($data["main"] as $value){
echo $value->comment . '<br />';
if(count($data["parent_" . $value->id]) >= 1){
showReplies($data, $value->id);
}
}
function showReplies($data, $id, $counter = ''){
if(count($data["parent_" . $id]) >= 1){
$counter .= ' --';
foreach($data["parent_" . $id] as $value){
echo $counter . ' ' . $value->comment . '<br />';
showReplies($data, $value->id, $counter);
}
}
}