Formatting Arrays for 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!

Your table structure (database) is incorrect, you divide one entry into 3? What for? ID should be auto increment. Why is your table structure like that? — Linek
– Linek, Commented Apr 22, 2014 at 12:00
Create a PHP class with an id, taskName and taskDetail properties. Create an array of instances of this class in your fetch loop. Serialize. — Saturnix
– Saturnix, Commented Apr 22, 2014 at 12:02
I have another table that auto increments as a new item is added. The id in this table correlates back to those items so I know which attributes apply each. — C_B
– C_B, Commented Apr 24, 2014 at 2:27

Purefan · Accepted Answer · 2014-04-22 12:02:06Z

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

answered Apr 22, 2014 at 12:02

Purefan

1,50626 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

C_B Over a year ago

The format is slightly different than what I posted but this still worked like a charm for what I needed. THANK YOU!!

C_B Over a year ago

Here is the result for others to see: {"1":{"taskName":"First Task","taskDetail":"Enter first task in application.","taskList":"Important"},"2":{"taskName":"Second Task","taskDetail":"Enter second task in application.","taskList":"Very Important"}}

Collectives™ on Stack Overflow

Formatting Arrays for json_encode

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related