Selecting Data from Multiple rows and sorting it

Question

[   id    -   title   -   content  -   class]
[    1    -     t1    -     p1     -     1  ]
[    2    -     t2    -     p6     -     1  ]
[    3    -     t3    -     p5     -     2  ]
[    4    -     t4    -     p8     -     3  ]

For this table, How can i use 1 query to SELECT all classes DISTINCTLY to become this HTML

<label>Class: 1</label>
<div>t1, p1</div>
<div>t2, p6</div>

<label>Class: 2</label>
<div>t3, p5</div>

<label>Class: 3</label>
<div>t4, p8</div>

I really can't figure out a way to SELECT all the data and sort it at the same time at all.

Answer 1

SELECT DISTINCT
    title, content, class
FROM
    data
ORDER BY
    class, title

Answer 2

It's easy enough to order by class and then title

SELECT title, content, class FROM your_table ORDER BY class, title

But the query isn't going to be able to produce that HTML. You'll need some logic in your PHP code to determine when the class changes.

$previous_class = null;  // $previous_class will store the class of the previous row. It's
                         // initialized to null because there's no previous row at first.

while ($row = some_fetch_function()) {

    // compare the current row's class to the previous row's class, and
    // output the class label if it has changed.
    if ($row['class'] !== $previous_class) {
        echo "<label>Class: $row[class]</label>";
    }
    $previous_class = $row['class'];
    echo "<div>$row[title], $row[content]</div>";
}

---

To answer your further questions about how it works, it's probably best to just step through the iterations of the loop.

First, you need to understand that the query results will already be sorted the way you need them to be, and the PHP is just providing a mechanism to output the class labels at the appropriate time. When you use the ORDER BY class, title clause in your query, the results will be sorted first by class, then by title for any rows that have the same class. So for your example, you'll get the rows in the same order you have them shown in your question.

So after $previous_class is initialized to null, this is what happens in the while loop:

1. First iteration: $row = ['id' => 1, 'title' => 't1', 'content' => 'p1', 'class' => 1]

   • $row['class'] is 1, and $previous_class is null, so the label is printed.
   • $previous_class is set to 1.
   • <div>t1, p1</div> is printed

2. Second iteration: $row = ['id' => 2, 'title' => 't2', 'content' => 'p6', 'class' => 1]

   • $row['class'] is 1, and $previous_class is 1, so the label is not printed.
   • $previous_class is set to 1 (again).
   • <div>t2, p6</div> is printed

3. Third iteration: $row = ['id' => 3, 'title' => 't3', 'content' => 'p5', 'class' => 2]

   • $row['class'] is 2, and $previous_class is 1, so the label is printed.
   • $previous_class is set to 2.
   • <div>t3, p5</div> is printed

4. Foruth iteration: $row = ['id' => 4, 'title' => 't4', 'content' => 'p8', 'class' => 3]

   • $row['class'] is 3, and $previous_class is 2, so the label is printed.
   • $previous_class is set to 3.
   • <div>t4, p8</div> is printed