将mysql查询与子查询结果合并为一个PHP数组
我有一个包含事件的表，列出这些事件，我循环遍历事件表，检查事件类型并使用它的eventId查找它的特定表中的值。
目前，这使用一个查询来获取20个事件，然后使用3个查询来获取这些事件的数据。 我目前使用程序编码，但需要对其进行更改，以便在最后它仅以数组形式返回数据。
下面是我需要实现的一个Pseduo代码示例：
while(eventQuery):
if commentQueryResult;
$array .= commentQueryResult;
if forumPostQueryResult;
$array .= forumPostQueryResult;
if uploadItemQueryResult;
$array .= uploadItemQueryResult;
endwhile;
return $array; // Combined returned results as one array
然后，我将能够访问数据并通过它进行foreach循环。
我只是不确定将多个结果集合到一个数组中的最佳方式？
或者你可以尝试将它们组合成一个查询...
$eventResult = mysql_query(
'SELECT userevent.event, userevent.eventId, userevent.friendId
FROM userevent
WHERE userevent.userId = 1 OR userevent.friendId = 1
ORDER BY userevent.id DESC
LIMIT 20'
);
while ($eventRow = mysql_fetch_row($eventResult)){
if($eventRow[0] == 1){
$result = mysql_fetch_array(mysql_query("
SELECT forumRooms.id, forumRooms.title
FROM forumPosts
INNER JOIN forumRooms ON forumPosts.`forumTopic` = forumRooms.`id`
WHERE forumPosts.id = '$eventRow[1]'"));
}
elseif($eventRow[0] == 2){
$result = mysql_fetch_array(mysql_query("
SELECT game.id, game.uriTitle, game.title
FROM usergamecomment
INNER JOIN game ON usergamecomment.`gameId` = game.id
WHERE usergamecomment.id = $eventRow[1]"));
}
elseif($eventRow[0] == 4){
$result = mysql_fetch_array(mysql_query("
SELECT usercomment.comment, UNIX_TIMESTAMP(usercomment.TIME), `user`.id, `user`.username, `user`.activate
FROM usercomment
INNER JOIN `user` ON usercomment.`userId` = `user`.id
WHERE usercomment.id = $eventRow[1]
AND `user`.activate = 1"));
}
elseif($eventRow[0] == 5){
$result = mysql_fetch_array(mysql_query("
SELECT game.id, game.title, game.uriTitle
FROM game
WHERE game.id = $eventRow[1]"));
}
// Combined Results as array
}
我正在将所有这些转换为PDO，这是在制定最佳方法来最小化这一点之后的下一步。
接受挑战。 ;）
既然你实际上只对while循环内的结果感兴趣，你可以试试这个单一的查询。 由于左连接它可能不会更快，几乎取决于您的数据库。 最后的$result包含所有元素及其各自的字段。
$result = array();
$q = 'SELECT userevent.event AS userevent_event,
userevent.eventId AS userevent_eventId,
userevent.friendId AS userevent_friendId,
forumRooms.id AS forumRooms_id,
forumRooms.title AS forumRooms_title,
game.id AS game_id,
game.uriTitle AS game_uriTitle,
game.title AS game_title,
usercomment.comment AS usercomment_comment,
UNIX_TIMESTAMP(usercomment.TIME) AS usercomment_time,
user.id AS user_id,
user.username AS user_username,
user.activate AS user_activate,
g2.id AS game2_id,
g2.uriTitle AS game2_uriTitle,
g2.title AS game2_title
FROM userevent
LEFT JOIN forumPosts ON forumPosts.id = userevent.eventId
LEFT JOIN forumRooms ON forumPosts.forumTopic = forumRooms.id
LEFT JOIN usergamecomment ON usergamecomment.id = userevent.eventId
LEFT JOIN game ON usergamecomment.gameId = game.id
LEFT JOIN usercomment ON usercomment.id = userevent.eventId
LEFT JOIN user ON usercomment.userId = user.id
LEFT JOIN game g2 ON userevent.eventId = g2.id
WHERE (userevent.userId = 1 OR userevent.friendId = 1)
AND userevent.eventId >= (SELECT userevent.eventId
WHERE userevent.userId = 1 OR userevent.friendId = 1
ORDER BY userevent.id DESC LIMIT 1,20);';
$r = mysql_query($q);
while ( $o = mysql_fetch_row($r) ) {
switch($o['userevent_event']) {
case 1:
$result[] = array(
'id' => $o['forumsRooms_id'],
'title' => $o['forumsRooms_title'],
);
break;
case 2:
$result[] = array(
'id' => $o['game_id'],
'uriTitle' => $o['game_uriTitle'],
'title' => $o['game_title'],
);
break;
case 4:
$result[] = array(
'comment' => $o['usercomment_comment'],
'time' => $o['usercomment_time'],
'id' => $o['user_id'],
'username' => $o['user_username'],
'activate' => $o['user_activate'],
);
break;
case 5:
$result[] = array(
'id' => $o['game2_id'],
'uriTitle' => $o['game2_uriTitle'],
'title' => $o['game2_title'],
);
break;
}
}
注意：最终，查询必须稍微编辑，我只是将它写在我的头上，没有测试。 如果我对数据库结构有更多的了解，优化肯定可以完成。
此外，这仅仅是一个概念证明，它确实可以用单个查询来完成。 ;）
链接地址: http://www.djcxy.com/p/47803.html上一篇: Combine mysql query with sub query results into one PHP array