Geek Answers Handbook

How to order / sort a list in which several users are inserted in different positions?

I am creating an application in which several users can post comments above or below other comments. This is not a thread type structure. This is more like collaborating with a Word document. I am having trouble creating a method for sorting these records.

Using mySQL and PHP, sorting by input time does not work, and also does not sort by comment position, because the position changes if the user enters data between other comments. I don’t want to re-serialize the comment positions for each new post (what if thousands of posts and dozens of users do the same).

What is the best way to develop it?

+5
source share
3 answers

What you are describing is a linked list . The problem is that they are usually hard to get using only SQL. My solution is to use PHP to sort by search.

Your table will look something like this:

CREATE TABLE page {
   page_id INT,
   first_comment_id INT
}

CREATE TABLE comment {
   comment_id INT PRIMARY KEY AUTOINCREMENT,
   page_id INT,
   next_comment_id INT
}

Your request is simple:

SELECT comment_id, next_comment_id 
FROM comment 
WHERE page_id = $page_id 
ORDER BY comment_id DESC

An important step is to massage the results from mysql_fetch_assoc () into an array that is indexed according to the comment:

$result = mysql_query($sql);
$indexed_list = array();
while ($row = mysql_fetch_assoc($result)) 
{
    $indexed_list[$row['comment_id']] = $row;
}

The result is in an array like this:

$indexed_list = array(
    1 => array("comment_id"=>1, "next_comment_id"=>2),
    2 => array("comment_id"=>2, "next_comment_id"=>5),
    3 => array("comment_id"=>3, "next_comment_id"=>4),
    4 => array("comment_id"=>4, "next_comment_id"=>0),
    5 => array("comment_id"=>5, "next_comment_id"=>3));

The PHP function to sort them in a visible order is simple:

function llsort($indexed_list, $first_comment_id) 
{
    $sorted_list = array();

    $node = $indexed_list[$first_comment_id];
    array_push($sorted_list, $node);

    do
    {
        $node = $indexed_list[$node['next_comment_id']];
        array_push($sorted_list, $node);
    } while ($node['next_comment_id'] != 0 
        AND isset($indexed_list[$node['next_comment_id']]) );

    return $sorted_list;
}

first_comment_id . , node a node, . .

MySQL:

+1

MPTT, . . .

, . Googling , . , .

0

. - update. , concurrency; , , ( pk, , ).

, , . , . ( , - , , , , .)

-1

More articles:


All Articles