Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>What you are describing is a <a href="http://en.wikipedia.org/wiki/Linked_list" rel="nofollow noreferrer">linked list</a>. The problem is that they are usually hard to retrieve using just SQL. My solution is to use PHP to do the sorting upon retrieval. </p> <p>Your table would look something like this:</p> <pre><code>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 } </code></pre> <p>Your query is simple:</p> <pre><code>SELECT comment_id, next_comment_id FROM comment WHERE page_id = $page_id ORDER BY comment_id DESC </code></pre> <p>The important step is to massage the results from mysql_fetch_assoc() into an array that is indexed according to comment_id:</p> <pre><code>$result = mysql_query($sql); $indexed_list = array(); while ($row = mysql_fetch_assoc($result)) { $indexed_list[$row['comment_id']] = $row; } </code></pre> <p>Resulting in an array similar to this one:</p> <pre><code>$indexed_list = array( 1 =&gt; array("comment_id"=&gt;1, "next_comment_id"=&gt;2), 2 =&gt; array("comment_id"=&gt;2, "next_comment_id"=&gt;5), 3 =&gt; array("comment_id"=&gt;3, "next_comment_id"=&gt;4), 4 =&gt; array("comment_id"=&gt;4, "next_comment_id"=&gt;0), 5 =&gt; array("comment_id"=&gt;5, "next_comment_id"=&gt;3)); </code></pre> <p>The PHP function to sort them into displayable order is simple:</p> <pre><code>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; } </code></pre> <p>You get first_comment_id from the page table. Of course, you still have to implement functions to insert a node and delete a node, but those are left as exercises for the reader. Don't forget to use transactions for inserting and deleting nodes.</p> <p>More information on linked lists in MySQL:</p> <ul> <li><a href="https://stackoverflow.com/questions/675117/fetching-linked-list-in-mysql-database">Fetching linked list in MySQL database</a></li> <li><a href="https://stackoverflow.com/questions/3313041/creating-a-linked-list-or-similar-queue-in-mysql">Creating a linked list or similar queue in MySQL?</a></li> <li><a href="http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql" rel="nofollow noreferrer">Managing Hierarchical Data in MySQL</a></li> <li><a href="http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html" rel="nofollow noreferrer">Trees and Other Hierarchies in MySQL</a></li> </ul>
    singulars
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload