使用来自json_encode的联接进行PHP定制MySQL格式设置
使用来自json_encode的联接进行PHP定制MySQL格式设置
提问于 2015-08-10 02:01:52
我正在创建一个项目时间跟踪工具。我是加入的菜鸟。我在非关系数据库方面更有经验。
我有一个带有两个表的MySQL数据库。一个用于一个或多个称为“项目”的项目，另一个用于举行每个称为"ProjectLogs“的会话。每个会话都有一个开始和停止时间戳。
“项目”表如下所示：
p_id, projectname"ProjectLogs“如下所示：
id, project_id, starttime, endtime我使用PHP来左转JOIN，使用如下：
$sql = "SELECT *
FROM Projects
LEFT JOIN ProjectLogs
ON Projects.p_id=ProjectLogs.project_id";然后我得到了结果：
$result = mysqli_query($con, $sql);然后，我需要将$result转到JSON，所以我使用以下方法：
$emparray = array();
while($row = mysqli_fetch_assoc($result)) {
$emparray[] = $row;
}
header('Content-Type: application/json');
echo json_encode($emparray);我要拿回的是这个。每个TimeLog有一个不同的对象：
[
{
"p_id":"1",
"projectname":"Project 001",
"id":"1",
"project_id":"1",
"starttime":"2015-08-09 19:37:02",
"endtime":"2015-08-09 19:39:13"
}
]我想要的是这样的东西，其中日志是项目中的一个数组：
[
{
"p_id": "1",
"projectname": "Project 001"
"ProjectLogs": [
{
"id": "1",
"project_id": "1",
"starttime": "2015-08-09 19:44:24",
"endtime": "2015-08-09 20:00:17"
},
{
"id": "2",
"project_id": "1",
"starttime": "2015-08-09 19:44:24",
"endtime": "2015-08-09 20:00:17"
}
]
},
{
"p_id": "2",
"projectname": "Project 002"
"ProjectLogs": [
{
"id": "1",
"project_id": "2",
"starttime": "2015-08-09 19:44:24",
"endtime": "2015-08-09 20:00:17"
},
{
"id": "2",
"project_id": "2",
"starttime": "2015-08-09 19:44:24",
"endtime": "2015-08-09 20:00:17"
}
]
}
]任何帮助都是非常感谢的！我觉得这是个重复的问题，但我不知道该找什么。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-08-10 04:54:18
我想出了答案。如果有人感兴趣，以下是解决办法：
$sql = "SELECT *
FROM Projects
LEFT JOIN ProjectLogs
ON Projects.p_id=ProjectLogs.project_id
ORDER BY Projects.p_id";
$result = mysqli_query($con, $sql);
// create an empty array that you can send
// to json_encode later
$arr = array();
// Loop through the results
while($row = mysqli_fetch_assoc($result)) {
// Check to see if the result contains a 'project_id' key
// If it doesn't, then this isn't a result with a ProjectLog
if( $row['project_id'] != "" ) {
// Set the key vaue pairs for the Project
$arr[$row['p_id']]['p_id'] = $row['p_id'];
$arr[$row['p_id']]['projectname'] = $row['projectname'];
// Now we need to check to see if any timelogs have been
// pushed to $arr.
if(array_key_exists("timelogs", $arr[$row['p_id']]) ) {
// Since it does exist:
// Create a variable to store the 3 values we need,
// And push them to the existing timelogs array
$val = array(
'id' => $row['id'],
'starttime' => $row['starttime'],
'endtime' => $row['endtime']
);
array_push($arr[$row['p_id']]['timelogs'], $val);
} else {
// Since it doesn't exist, lets create the timelogs array
$arr[$row['p_id']]['timelogs'] = array(
array(
'id' => $row['id'],
'starttime' => $row['starttime'],
'endtime' => $row['endtime']
)
);
}
}else {
// This fires when there are no ProjectLogs yet, just an empty project
$arr[$row['p_id']]['p_id'] = $row['p_id'];
$arr[$row['p_id']]['projectname'] = $row['projectname'];
}
}
// Apparently this is required
header('Content-Type: application/json');
// Go time! JSON!
echo json_encode($arr);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31910678
复制相关文章
相似问题
腾讯云开发者
