php - 尝试将多表sql查询放入一个结果数组中

Question

我有四个表:关注者、用户、混音、歌曲我试图从一个用户的所有关注者中获取所有混音，我已经弄清楚了这一部分，但我也想从每个用户中获取歌曲混合，目前我的查询正在给我结果，但每个结果都是混合中的一首歌曲，而不是一个混合的每个结果中的一系列歌曲......任何帮助都会很棒，我的 sql 技能不是最好的，我花了很多时间试图解决这个问题!

我当前的查询是:

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   

当前结果是(通过 cakephp 自定义查询运行此结果)

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
            )

    )

正如您所看到的，混合 ID 是相同的，我试图将歌曲作为每个结果中的数组，如下所示:

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
                )
            )

    )

真的希望只用一条sql语句就能完成这件事!提前致谢!

Answer 1

您可以使用 SQL join 命令一起进行多个查询..

使用这个...

sql_join