I have table structure like this:
id_comment
body
date_created
date_updated
user_id
parent_id
and for now this function:
public function comments_list($comments)
{
$comments_list = "";
foreach ($comments as $c) :
if($c['parent_id'] == 0) :
$comments_list .= '<div class="comment level-0">';
$comments_list .= $c['body'];
$comments_list .= '</div>';
endif;
endforeach;
}
$comments is the array with all the comments (2d array). $c['parent_id'] == 0 is parent comment. Child comment have parent_id set to id of the parent comment. How can I create comment list with parent/child relationship?
Comments structure:
array(5) {
[0]=>
array(9) {
["id_comment"]=>
string(1) "1"
["body"]=>
string(532) "Odio lectus hac odio ultrices vel diam hac elementum amet et scelerisque porttitor nunc elementum. Sit nunc turpis! Porttitor, odio massa nisi turpis porttitor aliquam aliquam proin! Tincidunt dapibus adipiscing sagittis adipiscing odio velit eros nisi tortor porttitor ut elit enim amet egestas porta. Massa. Nec tortor aliquet sed ultrices dignissim massa! Adipiscing tortor, pulvinar ultricies, lectus adipiscing sagittis, a tortor sit magna integer. Scelerisque hac amet amet habitasse et turpis integer nisi sagittis ridiculus."
["date_created"]=>
string(19) "2014-07-01 00:00:00"
["date_updated"]=>
string(19) "2014-07-02 02:34:51"
["post_id"]=>
string(1) "4"
["user_id"]=>
string(1) "1"
["parent_id"]=>
string(1) "0"
["good_vote"]=>
string(1) "0"
["bad_vote"]=>
string(1) "0"
}
[1]=>
array(9) {
["id_comment"]=>
string(1) "2"
["body"]=>
string(532) "Odio lectus hac odio ultrices vel diam hac elementum amet et scelerisque porttitor nunc elementum. Sit nunc turpis! Porttitor, odio massa nisi turpis porttitor aliquam aliquam proin! Tincidunt dapibus adipiscing sagittis adipiscing odio velit eros nisi tortor porttitor ut elit enim amet egestas porta. Massa. Nec tortor aliquet sed ultrices dignissim massa! Adipiscing tortor, pulvinar ultricies, lectus adipiscing sagittis, a tortor sit magna integer. Scelerisque hac amet amet habitasse et turpis integer nisi sagittis ridiculus."
["date_created"]=>
string(19) "0000-00-00 00:00:00"
["date_updated"]=>
string(19) "2014-07-02 02:34:54"
["post_id"]=>
string(1) "4"
["user_id"]=>
string(1) "1"
["parent_id"]=>
string(1) "2"
["good_vote"]=>
string(1) "0"
["bad_vote"]=>
string(1) "0"
}
[2]=>
array(9) {
["id_comment"]=>
string(1) "3"
["body"]=>
string(532) "Odio lectus hac odio ultrices vel diam hac elementum amet et scelerisque porttitor nunc elementum. Sit nunc turpis! Porttitor, odio massa nisi turpis porttitor aliquam aliquam proin! Tincidunt dapibus adipiscing sagittis adipiscing odio velit eros nisi tortor porttitor ut elit enim amet egestas porta. Massa. Nec tortor aliquet sed ultrices dignissim massa! Adipiscing tortor, pulvinar ultricies, lectus adipiscing sagittis, a tortor sit magna integer. Scelerisque hac amet amet habitasse et turpis integer nisi sagittis ridiculus."
["date_created"]=>
string(19) "0000-00-00 00:00:00"
["date_updated"]=>
string(19) "2014-07-02 02:34:56"
["post_id"]=>
string(1) "4"
["user_id"]=>
string(1) "1"
["parent_id"]=>
string(1) "3"
["good_vote"]=>
string(1) "0"
["bad_vote"]=>
string(1) "0"
}
[3]=>
array(9) {
["id_comment"]=>
string(1) "4"
["body"]=>
string(532) "Odio lectus hac odio ultrices vel diam hac elementum amet et scelerisque porttitor nunc elementum. Sit nunc turpis! Porttitor, odio massa nisi turpis porttitor aliquam aliquam proin! Tincidunt dapibus adipiscing sagittis adipiscing odio velit eros nisi tortor porttitor ut elit enim amet egestas porta. Massa. Nec tortor aliquet sed ultrices dignissim massa! Adipiscing tortor, pulvinar ultricies, lectus adipiscing sagittis, a tortor sit magna integer. Scelerisque hac amet amet habitasse et turpis integer nisi sagittis ridiculus."
["date_created"]=>
string(19) "0000-00-00 00:00:00"
["date_updated"]=>
string(19) "2014-07-02 02:34:59"
["post_id"]=>
string(1) "4"
["user_id"]=>
string(1) "0"
["parent_id"]=>
string(1) "1"
["good_vote"]=>
string(1) "1"
["bad_vote"]=>
string(1) "0"
}
[4]=>
array(9) {
["id_comment"]=>
string(1) "5"
["body"]=>
string(532) "Odio lectus hac odio ultrices vel diam hac elementum amet et scelerisque porttitor nunc elementum. Sit nunc turpis! Porttitor, odio massa nisi turpis porttitor aliquam aliquam proin! Tincidunt dapibus adipiscing sagittis adipiscing odio velit eros nisi tortor porttitor ut elit enim amet egestas porta. Massa. Nec tortor aliquet sed ultrices dignissim massa! Adipiscing tortor, pulvinar ultricies, lectus adipiscing sagittis, a tortor sit magna integer. Scelerisque hac amet amet habitasse et turpis integer nisi sagittis ridiculus."
["date_created"]=>
string(19) "0000-00-00 00:00:00"
["date_updated"]=>
string(19) "2014-07-02 02:35:02"
["post_id"]=>
string(1) "4"
["user_id"]=>
string(1) "1"
["parent_id"]=>
string(1) "0"
["good_vote"]=>
string(1) "0"
["bad_vote"]=>
string(1) "0"
}
}
The basic query you need is this:
SELECT * FROM `comments` WHERE `parent_id` = 0 ORDER BY `date_created` DESC
Assuming the parent of the comments you're looking for has the ID 0.
And at first, only grab the "root"-comments:
SELECT * FROM `comments` WHERE `parent_id` IS NULL ORDER BY `date_created` DESC
Assuming a "root" comment has a null-value as it's parent id (you need something there to distinguish it from a sub-comment).
EDIT
Ok I think I understand what your problem is ;) If not, please clarify!
You should take 2 steps: 1) grab the root comments and iterate over them, inserting them in the DOM on the way. 2) at each iteration, grab the 'sub-comments' and iterate over them, inserting them in the DOM
And if you want multi-level comments you should use recursion.
Example:
$query = 'SELECT * FROM `comments` WHERE `parent_id` IS NULL ORDER BY `date_created` DESC';
$comments = fetch_results_with_your_own_database_function($query);
foreach($comments as $comment) {
echo '<div class="comment">';
echo $comment->body;
echo subCommentList($comment->id);
echo '</div>';
}
function subCommentList($parent_id) {
$query = 'SELECT * FROM `comments` WHERE `parent_id` = '. $parent_id .' ORDER BY `date_created` DESC';
$comments = fetch_results_with_your_own_database_function($query);
foreach($comments as $comment) {
echo '<div class="subcomment">';
echo $comment->body;
echo subCommentList($comment->id);
echo '</div>';
}
}
Please not that the function is calling itself with the current id as the parent of it's next call. Every time it will go deeper in your column stack (forgive me bad use of terms).
WARNING including the ID as-is in the query is very unsafe. I trust you use a good database library with prepared-statements etc. If you don't use it (yet), please google
I think you're actually looking for a left join on the same table:
SELECT comments AS child, parent_id AS parent, date_created AS datecreated FROM comments children LEFT JOIN comments parents ON children.parent_id = parents.id ORDER BY parent, datecreated
This should give you a list of parent comments with children right under them, ordered by date created (presumably the order they were created in):
| child | parent_id | datecreated |
| comment1 | | time1 | <-- root comment
| comment2 | id1 | time2 |
| comment3 | id1 | time3 |
| comment4 | id2 | time4 |
| comment5 | id3 | time5 |
Then maybe write a loop to group each of these into correct arrays? Just brainstorming. This should get you thinking on the right track at least.
Check out Sql Joining a Table To Itself it explains self joins pretty well and gives a nice tutorial about how to do it.