INNER JOIN SQL/PHP - Need a single row with array on column

Question

I have created a relational database for songs, composers and lyricists.

My issue is that some songs can have multiple lyricists or multiple composers so when I search my database I would like a single song to be returned with an array of composers or lyricists if the particular song has had more than one contributor.

I am pulling data from several tables. Songs, composers [links composers to songs], composerdetails, lyricists [links lyricists to songs], lyricistdetails, Shows.

This is all brought together with the SQL query:

 SELECT songs.songtitle, shows.showtitle, composerdetails.name composer, lyricistdetails.name lyricist
FROM songs
JOIN composers ON composers.songid = songs.songid
JOIN composerdetails ON composers.composerid = composerdetails.composerid
JOIN lyricists ON lyricists.songid = songs.songid
JOIN lyricistdetails ON lyricists.lyricistid = lyricistdetails.lyricistid
JOIN shows ON shows.showid = songs.showid
WHERE MATCH (
songtitle
)
AGAINST (
'$songname'
IN BOOLEAN
MODE
)

At the moment, I get

  [0] => stdClass Object
    (
        [songtitle] => Maria
        [showtitle] => West Side Story
        [composer] => Leonard Bernstein
        [lyricist] => Leonard Bernstein
    )

[1] => stdClass Object
    (
        [songtitle] => Maria
        [showtitle] => West Side Story
        [composer] => Leonard Bernstein
        [lyricist] => Stephen Sondheim
    )

If I GROUP the songs by songtitle I would ignore the second lyricist. What is the best way to go about having the results returned as single rows, with an array for the lyricist if there is more than one?

score 2 · Accepted Answer · edited May 23 '17 at 10:31

2

You can use GROUP_CONCAT but be aware of that it has the limit of character to group

SELECT
    songs.songtitle, shows.showtitle,
    GROUP_CONCAT(composerdetails.name) composer,
    GROUP_CONCAT(lyricistdetails.name) lyricist
FROM songs
JOIN composers ON composers.songid = songs.songid
JOIN composerdetails ON composers.composerid = composerdetails.composerid
JOIN lyricists ON lyricists.songid = songs.songid
JOIN lyricistdetails ON lyricists.lyricistid = lyricistdetails.lyricistid
JOIN shows ON shows.showid = songs.showid
WHERE MATCH (
songtitle
)
AGAINST (
'$songname'
IN BOOLEAN
MODE
)
GROUP BY songs.songtitle

For the limit of character in group_concat refer to this question

edited May 23 '17 at 10:31

Community

1
1

answered Feb 17 '14 at 20:23

M Khalid Junaid

63,861
10
90
118

2

Brilliant, thanks a lot. Will mark as answered as soon as it lets me – Robert Owen Feb 17 '14 at 20:30
@RobertOwen my pleasure if you find this answer as helpfull then read [Accepting Answers: How does it work?](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) – M Khalid Junaid Feb 17 '14 at 20:57
1

Thanks, I do understand it was not letting me accept before as it was too soon! :) – Robert Owen Feb 17 '14 at 21:25

INNER JOIN SQL/PHP - Need a single row with array on column

1 Answers1