How to create json array in json array codeigniter

Question

I have been searching for the solution on how to create JSON array in JSON object from Mysql query. Need help from any of you.

My source data in mysql:

Table: parent
--------------------------------------
| id | firstname | lastname | rating |
--------------------------------------
|  1 | John      | Doe      | 9.3    |
|  2 | Marry     | Jane     | 8.5    |
|  3 | Paijo     | Masni    | 9.8    |
--------------------------------------

Table: children
------------------------------
| id |  idparent  | name     |
------------------------------
|  1 |  1         | John A   |
|  2 |  1         | John B   |
|  3 |  1         | John C   |
|  4 |  2         | Jane A   |
|  5 |  2         | Jane B   |
|  6 |  3         | Bang Jo  |
|  7 |  3         | Kak Jo   |
------------------------------

My MySQL Query:

Select p.firstname, p.lastname, p.rating, c.name as children
from parent p join children c on p.id = c.idparent

Output:
-------------------------------------------------
| id | firstname | lastname | rating | children |
-------------------------------------------------
|  1 | John      | Doe      | 9.3    | John A   |
|  1 | John      | Doe      | 9.3    | John B   |
|  1 | John      | Doe      | 9.3    | John C   |
|  2 | Marry     | Jane     | 8.5    | Jane A   |
|  2 | Marry     | Jane     | 8.5    | Jane B   |
|  3 | Paijo     | Masni    | 9.8    | Bang Jo  |
|  3 | Paijo     | Masni    | 9.8    | Kak Jo   |
-------------------------------------------------

Here is my output of JSON format that I wanted:

var profile = [
    {
        "firstname": "John",
        "lastname": "Doe",
        "rating": 9.3,
        "children": [
            "John A",
            "John B",
            "John C"
        ],
        "id": 1
    },
    {
        "firstname": "Marry",
        "lastname": "Jane",
        "rating": 8.5,
        "children": [
            "Jane A",
            "Jane B"
        ],
        "id": 2
    },
    {
        "firstname": "Paijo",
        "lastname": "Masni",
        "rating": 9.8,
        "children": [
            "Bang Jo",
            "Kak Jo"
        ],
        "id": 3
    }
];

The one I got stuck from generating the JSON is on the children: [], I want to have an array with double-quote "" separated by comma ',' inside the [].

Thank you.

NB: Currenty I am using codeigniter for my coding. If you have faced this kind of problem on codeigniter before, I'm looking forward to getting the sharing from you.

The source of my data comes from Mysql database. Currently, I use json_encode() in codeigniter to generate the JSON array, but it turns out very general format without array inside json array. — erwandy92
– erwandy92, Commented Nov 16, 2017 at 0:56

erwandy92 · Accepted Answer · 2017-11-16 02:34:14Z

Thanks, all, for the valuable responses. I just got the solution.

After searching for any solution by using any available function in mysql (which I have used are json_extract() and group_concat() ), yet it didn't give the best format like what I wanted.

Inspired by the answers above, I have made my code like this in Codeigniter that works perfectly:

$parent   = array();
$children = array();
$profile  = array();

$parent_query = $this->db->query("select firstname, lastname, rating, id from parent")->result_array();

for($i = 0; $i < count($parent_query); $i++)
{
   $children_query = $this->db->query("select name from children where idparent = '$parent_query[$i]['id']'")->result_array();

   $parent[$i]['firstname'] = $parent_query[$i]['firstname'];
   $parent[$i]['lastname']  = $parent_query[$i]['lastname'];
   $parent[$i]['rating']    = $parent_query[$i]['rating'];

   for($j = 0; $j < count($children_query); $j++)
   {
      $children[] = $children_query[$j]['name'];
   }

   $parent[$i]['children']  = $children;
}

$profile = json_encode($parent);

The result given:

[  
   {  
      "firstname":"John",
      "lastname":"Doe",
      "rating":9.3,
      "children":[  
         "John A",
         "John B",
         "John C"
      ],
      "id":1
   },
   {  
      "firstname":"Marry",
      "lastname":"Jane",
      "rating":8.5,
      "children":[  
         "Jane A",
         "Jane B"
      ],
      "id":2
   },
   {  
      "firstname":"Paijo",
      "lastname":"Masni",
      "rating":9.8,
      "children":[  
         "Bang Jo",
         "Kak Jo"
      ],
      "id":3
   }
]

Xpleria · Accepted Answer · 2017-11-15 13:35:45Z

1

$people = array();
$person = array();

$person['firstname'] = "John";
$person['lastname'] = "Doe";
$person['rating'] = 9.3;
$person['children'] = array('John A', 'John B', 'John C');
$person['id'] = 1;

$people[] = $person;
$person = array();

$person['firstname'] = "Marry";
$person['lastname'] = "Jane";
$person['rating'] = 8.5;
$person['children'] = array('JaneA', 'JaneB');
$person['id'] = 2;

$people[] = $person;

$profile = json_encode($people);

echo $profile;

Gives the following output:

[{"firstname":"John","lastname":"Doe","rating":9.3,"children":["John A","John B","John C"],"id":1},
{"firstname":"Marry","lastname":"Jane","rating":8.5,"children":["JaneA","JaneB"],"id":2}]

answered Nov 15, 2017 at 13:35

Xpleria

5,9625 gold badges55 silver badges67 bronze badges

1 Comment

erwandy92 Over a year ago

Thanks, Man. I got the solution inspired by your answer.

Jawad A. · Accepted Answer · 2017-11-15 14:45:46Z

0

I am using NEIL's array structure,

In codeigniter, use the following code at the end for encoding.

$this->output->set_content_type('application/json'); $this->output->set_output(json_encode($your_array));

It will output the following:

"[{\"firstname\":\"John\",\"lastname\":\"Doe\",\"rating\":9.3,\"children\":[\"John A\",\"John B\",\"John C\"],\"id\":1},{\"firstname\":\"Marry\",\"lastname\":\"Jane\",\"rating\":8.5,\"children\":[\"JaneA\",\"JaneB\"],\"id\":2}]"

And in AJAX return, use the $.parseJSON(data) to get your required result.

edited Nov 15, 2017 at 14:45

answered Nov 15, 2017 at 14:40

Jawad A.

736 bronze badges

Collectives™ on Stack Overflow

How to create json array in json array codeigniter

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related