PHP自定义json

PHP自定义json_encode使用MySQL加入格式化(PHP Custom json_encode Formatting With A Join From MySQL)

我正在创建一个项目时间跟踪工具。 我是JOINS的菜鸟。 我对非关系型数据库更有经验。

我有一个带有两个表的MySQL数据库。 一个用于名为“Projects”的列表或项目，另一个用于保存每个名为“ProjectLogs”的会话。 每个会话都有一个开始和停止时间戳。

“Projects”表格如下所示：

p_id, projectname

“ProjectLogs”看起来像这样：

id, project_id, starttime, endtime

我正在使用PHP来LEFT 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" } ]

我想要的是这样的，其中Logs是项目内部的一个数组：

[ { "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" } ] } ]

任何帮助将非常感谢！ 我觉得这是一个重复的问题，但我不确定要搜索什么。

I am creating a project time tracking tool. I'm a noob to JOINS. I'm Much more experienced with non-relational databases.

I have a MySQL database with two tables. One for the list or projects called "Projects", and another to hold each session called "ProjectLogs". Each session has a start and stop timestamp.

"Projects" table looks like this:

p_id, projectname

"ProjectLogs" looks like this:

id, project_id, starttime, endtime

I'm using PHP to LEFT JOIN, using this:

$sql = "SELECT * FROM Projects LEFT JOIN ProjectLogs ON Projects.p_id=ProjectLogs.project_id";

Then I get the results:

$result = mysqli_query($con, $sql);

I then need to get the $result to JSON, so I use this:

$emparray = array(); while($row = mysqli_fetch_assoc($result)) { $emparray[] = $row; } header('Content-Type: application/json'); echo json_encode($emparray);

What I'm getting back is this. A different object for each 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" } ]

What I want is something like this, where the Logs are an array inside of the project:

[ { "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" } ] } ]

Any help would be very much appreciated! I feel like this is a repeat question, but I wasn't exactly sure what to search for.

最满意答案

我想出了答案。 如果有人有兴趣，这是解决方案：

$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);

I figured out the answer. Here is the solution if anyone is interested:

$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);