开发者

How to structure mysql results this way?

What I'm trying to do is I have my tables structured like so:

The Monkees - I'm A Believer
The Monkees - Daydream Believer  
The Beatles - Hello, Goodbye
The Beatles - Yellow Submarine 

With the artist and title names in the same row and I'm trying to display them like this in the search results:

The Monkees:
I'm A Believer
Daydream Believer

The Beatles:
Hello, Goodbye
Yellow, Submarine

With artist names only appearing once, like a category above the song titles.

Is there any way I can achieve this structure with MySQL statements?

EDIT:

I've tried using group_concat, but I can only get song titles to show up, and not artist names. My code:

<?php
include 'config.php';
$trimmed = $_开发者_StackOverflow中文版GET['term'];

$res = mysql_query("SELECT DISTINCT artist, GROUP_CONCAT(title SEPARATOR ', ') AS artists FROM songs WHERE artist LIKE '%$trimmed%'");
$row = mysql_fetch_array($res, MYSQL_ASSOC);
$result = $row['artists'];

echo $result;

?>


Have you looked at doing a group_concat? Other than that, you can create an array as you loop through doing something like:

$music = array();
$result = mysql_query("SELECT ARTIST,ALBUM FROM MUSIC") or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
    $music[$row['ARTIST']][] = $row['ALBUM'];
}
echo '<pre>'.print_r($music,1).'</pre>';
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜