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>';
精彩评论