0

I have four tables: followers, users, mixes, songs I am trying to get all the mixes from all the followers of one user, I have that part figured out, but I also want to get the songs from each of those mixes, currently my query is giving me results but each result is for one song on the mix, rather than an array of songs within each result for one mix ... any help would be amazing, my sql skills aren't the greatest and I have spent a lot of time trying to figure this out!

my current query is:

SELECT followers.following_id, users.id, users.user_username, mixes.id, mixes.mix_created_date, mixes.mix_name,songs.song_artist
FROM followers, users, mixes,songs
WHERE followers.user_id = 46
AND users.id = followers.following_id
AND mixes.user_id = followers.following_id
AND mixes.id > 0
ORDER BY mixes.mix_created_date DESC
LIMIT 10

the current result is (from running this through a cakephp custom query)

Array
(
[0] => Array
    (
        [followers] => Array
            (
                [following_id] => 47
            )

        [users] => Array
            (
                [id] => 47
                [user_username] => someguy
            )

        [mixes] => Array
            (
                [id] => 45
                [mix_created_date] => 2012-07-21 2:42:17
                [mix_name] => this is a test
            )

        [songs] => Array
            (
                [song_artist] => Yo La Tengo
            )

    )

[1] => Array
    (
        [followers] => Array
            (
                [following_id] => 47
            )

        [users] => Array
            (
                [id] => 47
                [user_username] => someguy
            )

        [mixes] => Array
            (
                [id] => 45
                [mix_created_date] => 2012-07-21 2:42:17
                [mix_name] => this is a test
            )

        [songs] => Array
            (
                [song_artist] => Animal Collective
            )

    )

as you can see the mix id's are the same, I am trying to get the songs to be an array inside of each result like :

Array
(
    [0] => Array
        (
        [followers] => Array
            (
                [following_id] => 47
            )

        [users] => Array
            (
                [id] => 47
                [user_username] => someguy
            )

        [mixes] => Array
            (
                [id] => 45
                [mix_created_date] => 2012-07-21 2:42:17
                [mix_name] => this is a test
            )

        [songs] => Array
            (
                [0]=>array(
                    ['song_artist'] => Yo La Tengo
                ),
                [1]=>array(
                    ['song_artist'] => Animal Collective
                )
            )

    )

Really hoping this can be done with just one sql statement! thanks in advance!

user1517041
  • 21
  • 3

2 Answers2

1

You can use the SQL join command to make multiple queries together..

Use this...

sql_join

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rohan
  • 105
  • 6
  • Thanks for the answer, I have tried the JOIN, LEFT JOIN and INNER JOIN, but I can't figure out where it should go in the statement – user1517041 Jul 21 '12 at 14:30
0

first a note: it looks like you have a missing condition. according to the above query, every song in songs table will be joined with every result possible. probably there should be a condition similar to the following added: (column names can be different based on your tables): 

...
and mix.song_id=songs.song_id
...

as for your question: I don't know php so i regard mysql alone: I don't think it is possible to do it with mysql. mysql returns rows in the result set and each row can contain a single value in each column. to add a group of values (song names) in one column, they must be concatenated (and that is possible: Can I concatenate multiple MySQL rows into one field?), and later you split them back in your php script. this is not a good idea as you will need to choose a separator that you know will never appear in the values that are concatenated. therefore I think its better to remove the songs table from the query and after getting the mix id, run a second query to get all songs in that mix.

Community
  • 1
  • 1