CODEWITHSUNDEEP
phpmysql
user1585656
user1585656

Reputation: 13

PHP MYSQL looking for a way to group rows that have same value in certain column

I have a table named records with the columns: recordid, artist, album, description and coverimg. Some of the albums in the table have the same artist.

On my site I would like to display it like this:

-artist1
  album1
  album2
- artist2
  album3
-artist3
  album4
  album5

If I use GROUP BY it just shows one row per artist. Is there any other way to group them without resorting to relational tables?

Because now I have a form to insert and update the table through my site, and I have no idea how I would have to code the forms to make this work with relational tables.

Upvotes: 1

Views: 2340

Answers (2)

arnoudhgz
arnoudhgz

Reputation: 1284

You can do it without a group by:

<?php
$query = 'Select artist, album
    FROM records
    ORDER BY artist'
$result = mysql_query($query);

$artist = '';
while ($line = mysql_fetch_assoc($result)){
    // only show artist when it's an other artist then the previous one
    if ($line['artist'] != $artist){
        echo $line['artist'].'<br/>';
        $artist = $line['artist'];
    }
    echo $line['album'].'<br/>';
}
?>

I know I should not be using mysql_* functions anymore, please choose mysqli_* or PDO....

Upvotes: 1

codingbiz
codingbiz

Reputation: 26396

try this

select artist,group_concat(album) from albums group by artist

gives you

artist1  | album1,album2,album3
artist2  | albumx,albumy

You can split the second column in PHP e.g. using explode(row[1])

That should help you a bit

Upvotes: 2

Related Questions