Page 1 of 1
Hierarchical MySQL query
Posted: Mon Apr 19, 2004 6:19 pm
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>
Posted: Mon Apr 19, 2004 6:51 pm
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,
Posted: Mon Apr 19, 2004 7:04 pm
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?
Posted: Mon Apr 19, 2004 10:28 pm
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
(
їPop] => Array
(
ї0] => Rock
ї1] => Alternative
)
їSoul] => Array
(
ї0] => R&B
)
їDance] => Array
(
ї0] => Triphop
ї1] => Rap & Hiphop
ї2] => Ambient
)
)
Should be able to tweak it to your advantage after fetching the genres from a database...
Posted: Tue Apr 20, 2004 7:06 am
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
?>