我正在执行一个查询,该查询返回如下所示的注释列表:
$sQuery = "
select cp_comments.*,users.user_login, users.user_url, users.display_name, users.ID as avatar, cp_comments.id as replies
from ".$wpdb->prefix."cp_comments cp_comments
left join ".$wpdb->prefix."users users on users.ID=cp_comments.uid
where songid='$id'
order by cp_comments.id asc
";
$result1 = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
然后,我循环遍历结果,并编码一个json数组,如下所示:
/*
* Output
*/
$output = array(
"comments" => array()
);
while ( $aRow = mysql_fetch_array( $result1, MYSQL_ASSOC ) )
{
$row = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $aColumns[$i] == "playtime" )
{
/* Special output formatting for 'playtime' column */
$row[$aColumns[$i]] = ($aRow[ $aColumns[$i] ]) / $duration * 100;
}
else if ( $aColumns[$i] == "avatar" )
{
/* Special output to render Avatar by user id */
$row[$aColumns[$i]] = commentplayer_get_user_avatar($aRow[ $aColumns[$i] ]);
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
$row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
}
}
$output['comments'][] = $row;
}
echo json_encode($output);
这将生成一个JSON响应,如图所示:-
{ "comments" : [ { "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "More tests....",
"display_name" : "admin",
"id" : "26",
"playtime" : 36.206896551699998,
"posttime" : "2011-10-08 11:11:55",
"cid" : "26",
"songid" : "30",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
},
{ "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "test comment",
"display_name" : "admin",
"id" : "70",
"playtime" : 29.597701149399999,
"posttime" : "2011-10-13 15:51:01",
"cid" : "70",
"songid" : "30",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
},
{ "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "Another genius comment....",
"display_name" : "admin",
"id" : "71",
"playtime" : 48.8505747126,
"posttime" : "2011-10-13 15:55:38",
"cid" : "71",
"songid" : "30",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
}
] }
然而,我需要检索每个注释的"回复"(它们存储在一个单独的表中,并将它们嵌套在同一个JSON对象中。
这样,最终编码的JSON输出如下:-
{ "comments" : [ { "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "More tests....",
"display_name" : "admin",
"id" : "26",
"playtime" : 36.206896551699998,
"posttime" : "2011-10-08 11:11:55",
"cid" : "26",
"songid" : "30",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
"cid": "1",
"replies" : [ { "cid" : "26",
"body" : "test reply",
"posttime" : "2011-10-08 11:11:55"
}]
},
我似乎不知道如何将第二个mysql查询的结果嵌套到每一行中(假设存在任何回复)
我想运行的查询返回给定评论的回复(在两个表中链接为"cid")是:-
$sql = "select cp_replies.*,users.user_login, users.user_url, users.display_name, users.ID as avatar
from ".$wpdb->prefix."cp_replies cp_replies
left join ".$wpdb->prefix."users users on users.ID=cp_replies.uid
where cp_replies.cid= $cid
order by cp_replies.id asc";
$result2 = mysql_query( $sql, $gaSql['link'] ) or die(mysql_error());
变量$cid需要根据第一个查询的每一行的哪个"cid"动态传递给查询。(我希望这是有道理的)。
我尝试创建一个包含查询的单独函数,将"cid"作为变量传递给它,但在尝试检索数组时一直返回"null"。
我真的被困在这里了,伙计们,真的希望有人能帮助我。
EDIt:
在做出StuR建议的改变后,很明显我离目标越来越近了,但仍然没有达到预期。我的JSON(假设我已经正确地实现了他的建议)现在如下:-
{ "comments" : [ { "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "comment at 0:48",
"display_name" : "admin",
"id" : "2",
"playtime" : 17.977528089900002,
"posttime" : "2011-09-28 14:38:41",
"songid" : "24",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
},
{ "replies" : { "body" : "haha reply",
"cid" : "2",
"id" : "1",
"posttime" : "2011-09-28 15:14:56",
"uid" : "1",
"user_login" : "admin"
} },
{ "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "asdasd",
"display_name" : "admin",
"id" : "3",
"playtime" : 0.74906367041199995,
"posttime" : "2011-09-28 14:43:11",
"songid" : "24",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
},
{ "replies" : { "body" : "haha reply",
"cid" : "2",
"id" : "1",
"posttime" : "2011-09-28 15:14:56",
"uid" : "1",
"user_login" : "admin"
} },
{ "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
"body" : "test",
"display_name" : "admin",
"id" : "10",
"playtime" : 36.329588014999999,
"posttime" : "2011-10-06 14:15:12",
"songid" : "24",
"uid" : "1",
"user_login" : "admin",
"user_url" : "http://www.songbanc.com/members/admin/"
},
{ "replies" : { "body" : "haha reply",
"cid" : "2",
"id" : "1",
"posttime" : "2011-09-28 15:14:56",
"uid" : "1",
"user_login" : "admin"
} }
] }
有任何基于此的构建吗?
$output['comments'][] = $row;
这是在"comments"下为它循环的每一行创建一个嵌套数组,所以在将其编码为JSON之前,您的数组结构看起来像:
$output['comments'][]['id'] = 26;
$output['comments'][]['songid'] = 30;
所以你想把你的回复放进:
$output['comments'][]['replies'] = $replies;
我要做的是把你的第二个查询放在while循环中,然后这样做:
while ( $replies = mysql_fetch_array( $result2, MYSQL_ASSOC ) )
{
$output['comments'][$cid]['replies'][] = $replies;
}
第一次改变:
$output['comments'][] = $row;
至
$output['comments'][$cid] = $row;