php - Mysql子查询多结果

Question

查询

SELECT artists.artist_id, images.img_id 
FROM `artists`, `images` 
WHERE artists.artist_id = :artist_id 
AND images.artist_id = :artist_id

返回以下示例:

Array
(
[0] => Array
    (
        [artist_id] => 80
        [img_id] => 4
    )

[1] => Array
    (
        [artist_id] => 80
        [img_id] => 5
    )

[2] => Array
    (
        [artist_id] => 80
        [img_id] => 6
    )

[3] => Array
    (
        [artist_id] => 80
        [img_id] => 140
    )

)

如何使它返回 artist_id仅一次并且图像 ID 位于单独的数组中？我已经尝试过INNER JOIN images ON images.artist_id = :artist_id也一样，但返回类似的东西。

Answer 1

尝试在您的 php 端执行此操作。会容易得多

免责声明:我可能已经写过两次 php 了..所以我绝不是 php 方面的专家..但这就是我会尝试做的。如果代码有问题，任何人都可以随意更正；)

设置:我创建了一个数组数组，就像您所拥有的那样。创建一个名为 image_ids 的空数组将 img_id 推送到该数组上以获取 img_id 的数组

$test = Array
(
 Array
    (
        artist_id => 80,
        img_id => 4
    ),

Array
    (
        artist_id => 80,
        img_id => 5
    ),

Array
    (
        artist_id => 80,
        img_id => 6
    ),

Array
    (
        artist_id => 80,
        img_id => 140
    )

);

设置后 - 现在是编码时间!:

$image_ids = Array();
$artist = 0;
foreach ($test as $my_array){ // $test is the variable the whole array is equal to
    array_push($image_ids, $my_array['img_id']);
}
$artist = $my_array['artist_id']; // my_artist is the last element in the array currently

echo 'the artist is: ', $artist;
echo 'the images he made are :';
print_r ($image_ids);