我有这样的代码,它击中了mysql中一个名为vAlbums
的视图,它返回了一个查询结果的JSON数组
function getAlbumPics($arno){
$aSql = "SELECT albu_ablumid, albu_name_en, albu_name_de, albu_name_fr, albu_name_nl, albu_name_es, albu_name_it, albu_photourl FROM vAlbums WHERE site_arno=:arno";
try {
$db = getConnection();
$aStmt = $db->prepare($aSql);
$aStmt->bindParam(":arno",$arno);
$aStmt->execute();
$albums = $aStmt->fetchAll(PDO::FETCH_OBJ);
$arrAID = $aStmt->fetchColumn(2);
$db = null;
echo '{"albums": ' . json_encode($albums) . '}';
} catch(PDOException $e) {
echo '{"error":[{"text":"'. $e->getMessage() .'"}],';
echo '"SQL": ' . json_encode($aSql) .'}';
}
}
我需要做一个子查询,在数组中的每个相册中放置一个照片数组,就像一样
{
"albums": [
{
"albu_ablumid": "1",
"photos": [
{
"photourl": "photo1"
},
{
"photourl": "photo2"
},
{
"photourl": "photo3"
}
]
},
{
"albu_ablumid": "2",
"photos": [
{
"photourl": "photo1"
},
{
"photourl": "photo2"
},
{
"photourl": "photo3"
}
]
}
]
}
有人能展示如何实现这一点吗照片的MySQL查询是:
SELECT * FROM photos WHERE album_id = x
感谢
为什么不在单个查询中执行联接和组连接?
SELECT
albu_ablumid,
albu_name_en,
albu_name_de,
albu_name_fr,
albu_name_nl,
albu_name_es,
albu_name_it,
albu_photourl,
group_concat(photourl) // Guessing at column Name
FROM
vAlbums a
join photos b
on a.albu_ablumid=b.album_id
WHERE
site_arno=:arno
group by
albu_ablumid,
albu_name_en,
albu_name_de,
albu_name_fr,
albu_name_nl,
albu_name_es,
albu_name_it,
albu_photourl
这将在与相册匹配的每一行中以逗号分隔的字符串返回所有照片。然后,您可以根据需要在代码中轻松地将其拆分。它节省了大量连接和正在运行的多个查询。
下面是我的playdb中的一个小例子,展示它的功能:
mysql> select * from table1;
+---------+------+------+-------------+
| autonum | ID | name | metavalue |
+---------+------+------+-------------+
| 1 | 1 | Rose | Drinker |
| 2 | 1 | Rose | Nice Person |
| 3 | 1 | Rose | Runner |
| 4 | 2 | Gary | Player |
| 5 | 2 | Gary | Funny |
| 6 | 2 | Gary | NULL |
| 7 | 2 | Gary | Smelly |
+---------+------+------+-------------+
7 rows in set (0.00 sec)
mysql> select ID, group_concat(metavalue) from table1 group by ID;
+------+----------------------------+
| ID | group_concat(metavalue) |
+------+----------------------------+
| 1 | Drinker,Nice Person,Runner |
| 2 | Player,Funny,Smelly |
+------+----------------------------+
2 rows in set (0.00 sec)
如果出于某种原因需要,可以使用一些简单的代码来处理NULL:
mysql> select ID, group_concat(coalesce(metavalue,'Empty')) from table1 group by ID;
+------+-------------------------------------------+
| ID | group_concat(coalesce(metavalue,'Empty')) |
+------+-------------------------------------------+
| 1 | Drinker,Nice Person,Runner |
| 2 | Player,Funny,Empty,Smelly |
+------+-------------------------------------------+
2 rows in set (0.00 sec)