CODEWITHSUNDEEP
phpsqlselect
Jesica Arroyo Salvador
Jesica Arroyo Salvador

Reputation: 143

Ordering MySQL results to obey parent/child

I have a table like this:

ID   NAME                     TYPE     PARENT
1    Letters of Jane Austen   book     0
2    Chapter 1                chapter  1
3    Chapter 2                chapter  1
4    Chapter 3                chapter  1
5    Title 1                  title    2
6    Title 2                  title    2

I want to create a ordered form like this:

[]Letters of Jane Austen
  []Chapter 1
    []Title 1
    []Title 2
  []Chapter 2
  []Chapter 3
[]Another book...

In the database there are many books, not just one.

There any way to sort the query "SELECT" in this way? It is only a cosmetic issue and it does not need to do a multidimensional array or something, because I can put on the label their type and then to change their appearance depending on the type. But I need to fech the query in the right order.

P.D: sorry for my english :S

Upvotes: 3

Views: 243

Answers (3)

pbarney
pbarney

Reputation: 2843

This type of table uses what is called the Adjacency List Model. The following query will work for you:

SELECT
    b.`id`,
    b.`name`,
    b.`type`,
    b.`parent`
FROM `books` b
LEFT JOIN `books` b2 ON b.`parent` = b2.`id`
ORDER BY
    CASE
        WHEN b.`parent` = 0
        THEN b.`id`
        ELSE b.`parent`
    END,
    b.parent;

Upvotes: 0

Jesica Arroyo Salvador
Jesica Arroyo Salvador

Reputation: 143

I finally made ​​crazy, but it works, something like this:

select first type
foreach first type
__select second type where id = first type
__foreach second type

Upvotes: 0

Sergio
Sergio

Reputation: 6948

Don't think it's a good idea for database. Query would be kind of hard to it. Imo better - you select all books, write it to page in a streight order and just reorder it with js.

Html could be sort of:

<div id="books">
    <div id="book_1" class="book" parent="0">Letters of Jane Austen</div>
    <div id="book_2" class="book" parent="1">Chapter 1</div>
    <div id="book_3" class="book" parent="1">Chapter 2</div>
    <div id="book_4" class="book" parent="1">Chapter 3</div>
    <div id="book_5" class="book" parent="2">Title 1</div>
    <div id="book_6" class="book" parent="2">Title 2</div>
</div>

So js that does this hierarchy:

$(function () {
    $('#books .book').each(function () {
        var p = $(this).attr("parent");
        if ($('#book_' + p).length) {
            $(this).appendTo($('#book_' + p));
        }
    });
});

Check it at JSFiddle

Upd

And you can reorder it on a backend with php if you want it work right with non-js clients, but solution won't be that beautiful :D

Upvotes: 2

Related Questions