Hierarchical MySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Hierarchical MySQL query

Post by WaldoMonster »

I have a MySQL database with a hierarchical genre_id (one letter is the first level, two letters the second level).
This is an example of the database the first item is the genre_id and the second is the genre:

Code: Select all

a - Pop
aa - Rock
ab - Alternative
b - Soul
ba - R&B
c - Dance
ca - Triphop
cb - Rap & Hiphop
cc - Ambient
How can I make a query that will give the following result?
Order first by hierarchy level and second by genre alphabetical.

Code: Select all

c - Dance
    cc - Ambient
    cb - Rap & Hiphop
    ca - Triphop
a - Pop
    ab - Alternative
    aa - Rock
b - Soul
    ba - R&B
<edit>changed layout only</edit>
Last edited by WaldoMonster on Tue Apr 20, 2004 6:00 am, edited 1 time in total.
User avatar
mrvanjohnson
Forum Contributor
Posts: 137
Joined: Wed May 28, 2003 11:38 am
Location: San Diego, CA

Post by mrvanjohnson »

I think the LIKE with a wildcard should do this for you. You will need to test it but something like
select * FROM `album_table` WHERE `genre` LIKE 'b%'
I think this would like everything in order of b
example
b
ba
bb
bc
bd

Like I said you would have to test it but the LIKE and Wildcard should get you what you need,
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

Thanks so far.
For one level I could use:

Code: Select all

SELECT genre_id, genre FROM table WHERE genre_id LIKE 'c_' ORDER BY genre
This will return:
  • cc - Ambient
    cb - Rap & Hiphop
    ca - Triphop
But how can I do it for all genres see previous post?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Not exactly what you were looking for, but you might get ideas from it:

Code: Select all

<pre>
<?php
$array = array(
    "a" => "Pop",
    "aa" => "Rock",
    "ab" => "Alternative",
    "b" => "Soul",
    "ba" => "R&B",
    "c" => "Dance",
    "ca" => "Triphop",
    "cb" => "Rap & Hiphop",
    "cc" => "Ambient"
);
print_r($array); // what you have sofar
foreach($array as $key => $val) {
    if (strlen($key) == 1) {
        $tmp = $val;
    } else {
        $newarray[$tmp][] = $val;
    }
}
print_r($newarray); // result
?>
Result:

Code: Select all

Array
(
    &#1111;Pop] =&gt; Array
        (
            &#1111;0] =&gt; Rock
            &#1111;1] =&gt; Alternative
        )

    &#1111;Soul] =&gt; Array
        (
            &#1111;0] =&gt; R&amp;B
        )

    &#1111;Dance] =&gt; Array
        (
            &#1111;0] =&gt; Triphop
            &#1111;1] =&gt; Rap &amp; Hiphop
            &#1111;2] =&gt; Ambient
        )
)
Should be able to tweak it to your advantage after fetching the genres from a database...
User avatar
WaldoMonster
Forum Contributor
Posts: 225
Joined: Mon Apr 19, 2004 6:19 pm
Contact:

Post by WaldoMonster »

Not exactly what you were looking for, but you might get ideas from it:
Yes it gives me the idea to combine a query with PHP code instead of only use a query.
It is not the most elegant way, but it works.
Someone has a better idea or even a query witch can do the same?

Code: Select all

<?php
// Conect to MySQL database and select table

function GenreTree($genre_id)
{
global $genre_id_array;
global $genre_array;
$query = mysql_query("SELECT genre_id, genre FROM table WHERE genre_id LIKE '" . $genre_id . "_' ORDER BY genre");
while ($genre = mysql_fetch_array($query))
    {
	$genre_id_array[] = $genre['genre_id'];
	$genre_array[]    = $genre['genre'];
    GenreTree($genre['genre_id']);
    }
}

$genre_id_array = array();
$genre_array 	= array();
GenreTree(''); // Starting from root
?>
Post Reply