格式化json_encode的数组

Question

I have been searching for days to try and piece together my solution but am still struggling. I'm struggling with some php and getting it into the format I want in json-encode so I can use knockout.js.

I have a db that looks like this:

| id | name       | value                  |
| 1  | taskName   | First Task             |
| 1  | taskDetail | Enter your first task  |
| 1  | taskList   | Very Important         |
| 2  | taskName   | Second Task            |
| 2  | taskDetail | Enter your second task |
| 2  | taskList   | Important              |

I'm trying to get this response to my site with json_encode to look like this:

[
  {
    "id": "1",
    "taskName": "First Task",
    "taskDetail": "Enter your first task",
    "taskList": "Very Important"
  },
  {
    "id": "2",
    "taskName": "Second Task",
    "taskDetail": "Enter your second task",
    "taskList": "Important"
  }
]

Here is what I have so far:

$user = 1;

$task_sql = "
    SELECT *
    FROM `li_id`
    INNER JOIN `li_details`
    ON li_id.id = li_details.id
    WHERE li_id.user = '" . $user . "'";


$result = mysql_query($task_sql, $con);
$results = array();

while ($row = mysql_fetch_assoc($result)) {
    $results[] = array($row['name'] => $row['value']);
}

echo json_encode($results);

Response:

[
  {
    "taskName": "First Task"
  },
  {
    "taskDetail": "Enter first task in application."
  },
  {
    "taskList": "Important"
  },
  {
    "taskName": "Second Task"
  },
  {
    "taskDetail": "Enter second task in application."
  },
  {
    "taskList": "Very Important"
  }
]

The problem is that I'm not able to figure out how to group by the id into separate arrays. I've tried several things but without any luck. Any tips or direction to other threads would be awesome. Thanks!

Answer 1

How about something like this:

while ($row = mysql_fetch_assoc($result))
{           
    if (!isset($results[$row['id']])) $results[$row['id']] = array();
    $results[$row['id']][$row['name']] = $row['value'] );
}

Then after you json_encode it you will have all the fields nested in their id