How to order by section and subsection numbers in MySQL?

Question

I have a table like this:

+-----+-------------------------------------+--------------------------+---------------+
| id  | parent_id |  comment_text           | date_posted              |
+-----+-------------------------------------+--------------------------+---------------+
|   1 | 0         |  1                      | 2020-01-08 20:40:00      | 
|   2 | 1         |  1.1                    | 2020-01-08 20:41:00      | 
|   3 | 0         |  2                      | 2020-01-08 20:42:00      | 
|   4 | 0         |  3                      | 2020-01-08 20:43:00      | 
|   5 | 3         |  2.1                    | 2020-01-08 20:44:00      | 
|   6 | 2         |  1.1.1                  | 2020-01-08 20:45:00      |
|   7 | 1         |  1.2                    | 2020-01-08 20:46:00      | 

How do I get ordered comments like this? (ORDER BY date_posted DESC in each comment level and using one MySQL query because I need paging them using limit and offset).

comment_text           
    3
    2
       2.1
    1
       1.2
       1.1
          1.1.1

Answer 1

Seeing as you need all of the comments, and not just some of them, I wouldn't worry about the MySQL part of this, which would simply be SELECT * FROM Table ORDER BY Table.date_posted DESC. You could use WHERE, as well, but based on your example, you will need all of the comments.

Fully working example here: IDEOne Demo

Fully working example's output:

3
2
    2.1
1
    1.2
    1.1
        1.1.1

In PHP, simply build a hash, where we store a comments as so parent_id => $commentobj...

<?php

    $comment_hash = [];

    foreach($comments as $comment) {
        if(!$comment_hash[$comment['parent_id']]) {
            $comment_hash[$comment['parent_id']] = [];
        }

        $comment_hash[$comment['parent_id']][] = $comment;
    }

?>

We know that primary comments have a parent_id of 0, so we iterate over them. I use a recursive function, since comments can respond to other comments infinitely, or at least three layers according to your notes.

Our recursive display function.

<?php

    function displayComments($comments, $comment_hash, $depth) {
        foreach($comments as $comment) {
            print(str_repeat(' ', 4 * $depth));
            print($comment['comment_text']);
            print("\n");

            displayComments($comment_hash[$comment['id']], $comment_hash, $depth + 1);
        }
    }

?>

Run the recursive display function on top-level (i.e., parentid=0) comments...

<?php

    displayComments($comment_hash[0], $comment_hash, 0);

?>

Answer 2

It is not really efficient but this gets what you are wanting:

SELECT * 
FROM stuff
ORDER BY SUBSTRING_INDEX(comment_text, '.', 1), 
         SUBSTRING_INDEX(comment_text, '.', 2), 
         comment_text

Answer 3

SQL

SELECT * FROM tbl
ORDER BY
CAST(SUBSTRING_INDEX(comment_text, '.', 1) AS UNSIGNED) DESC, -- Up to first dot (if any)
CASE WHEN LOCATE('.', comment_text) = 0 -- No dots
     THEN 4294967295                    -- Place higher when no dots
     WHEN CHAR_LENGTH(comment_text) - CHAR_LENGTH(REPLACE(comment_text, '.', '')) = 1
     THEN CAST(SUBSTRING_INDEX(comment_text, '.', -1) AS UNSIGNED) -- Number after dot
     ELSE CAST(SUBSTRING(SUBSTRING_INDEX(comment_text, '.', -2),   -- Between 2 dots
                         1,
                         LOCATE('.', SUBSTRING_INDEX(comment_text, '.', -2))) AS UNSIGNED)
END DESC,
CASE WHEN CHAR_LENGTH(comment_text) - CHAR_LENGTH(REPLACE(comment_text, '.', '')) < 2
     THEN 4294967295                    -- Place higher when less than 2 dots
     ELSE CAST(SUBSTRING_INDEX(comment_text, '.', -1) AS UNSIGNED) -- After second dot
END DESC,
date_posted DESC;

Online Demo

Rextester demo: https://rextester.com/MPXMV17458 (Includes the example in the question + a further example with some repeated values and integers higher than 10).

Explanation

The query assumes there can be a maximum of two dots. There are three ORDER BY parts (other than the final date_posted ordering), representing the three possible integers in x.y.z. For the first two parts, if there is nothing beyond, the highest possible unsigned integer is picked - so x will be higher than x.y and x.y will be higher than x.y.z (or in reverse since the ordering is DESCending). The casting to unsigned integers ensures that the ordering will work beyond 9, since an alphabetical ordering would place 10 between 1 and 2.