CODEWITHSUNDEEP
phpmysqlcomments
Giorgio
Giorgio

Reputation: 1970

Sort comments by id and related answers in PHP-MySql

I have a MySql comments table. Some comments in this table are "simple" comments, some other are "replies" to comments. This table contains a "id" field and a "reply" field, to indicate which comment the reply refers to. In the following example, I have simple comments 1,4,7; comments 2,3,6 are replies to 1; comment 5 is a reply to 4.

unordered comments

My goal is to retrieve and display comments in PHP, ordered first by comment id and then by its replies. This way:

ordered by id and replies

I've thought to create a MySql query for each comment, to retrieve its replies; but I think it's not efficient because in this case I must query the DB many times (most of them without results, if comment doesn't have replies). Is there an efficient way to perform this goal? Maybe create a unique MySql query and then elaborate results in PHP? Thanks in advance.

Upvotes: 0

Views: 394

Answers (2)

Kickstart
Kickstart

Reputation: 21533

Should be able to order this in SQL like this (I am assuming the blank values of reply are nulls)

SELECT id, comment, reply
FROM comments_table
ORDER BY IF(reply IS NOT NULL, reply, id), id

Tested on sqlfiddle:-

http://www.sqlfiddle.com/#!2/bf75b/1

Upvotes: 3

Madhurendra Sachan
Madhurendra Sachan

Reputation: 760

You can use mysqli_result::fetch_array to get result to array. Then create an array of comments like:

//assuming $result has array of all comments sorted in assending order of number of comments.
//array(0=>array('id'=>1,'comment'=>'Nice article','reply'=>''), 1=>....
$comments=array();
foreach($result as $value){
if($value['reply']!='')
 $comments[$value['id']]=array('comment'=>$value['comment'],'reply' => array());
else
 $comments[$value['reply']]['reply'][$value['id']]=$value['comment']
}

This will created a array of comments containing an array of replies.

Upvotes: 0

Related Questions