Customizing the format of JSON data from PHP/MySQL

Question

I have a webservice which gives data as json form mysql database.

PHP webservice

<?php
    $conn = mysql_connect('localhost','root','');
    mysql_select_db('db', $conn);
    $query = mysql_query("SELECT id,group FROM faq");
    //$query content is:
    // Array ([id]=>faq1 [group]=>hardware) Array ([id]=>faq2 [group]=>software) 

    $faq = array();
    while($row = mysql_fetch_assoc($query)) {
        $faq[] = $row // <= ????????
    }

    header('Content-type: application/json');
    return json_encode($faq);
?>

JSON output that I get

[
    {
        "id": "faq1",
        "group": "hardware"
    },
    {
       "id": "faq2",
       "group": "software"
    }
]

JSON output that I want to have

[
    {
        "id": "faq1",
        "group": {
            "id": "hardware"
        }
   },
   {
       "id": "faq2",
       "group": {
            "id": "software"
       }
   }
]

I want to get this JSON data as result but I really couldn't have success on this. So how should I need to edit my "PHP webservice" to get this json result?

Not an answer but mysql_ is deprecated, use mysqli_ instead — ɴᴀᴛᴇ ᴅᴇᴠ
– ɴᴀᴛᴇ ᴅᴇᴠ, Commented May 17, 2013 at 14:15
@Henrique Barcelos I tried to summarize my code and for this I didn't mention my current json result. So, I'm not happy with my current result format:) — Korki Korkig
– Korki Korkig, Commented May 17, 2013 at 14:34
@Pete O'Connell I tried to summarize my code and for this I didn't mention my current json result. I've added it to my question now. — Korki Korkig
– Korki Korkig, Commented May 17, 2013 at 14:37

Michael Marr · Accepted Answer · 2013-05-17 14:16:03Z

5

$faq = array();
while($row = mysql_fetch_assoc($query)) {
   $faq[] = array(
      'id' => $row['id'],
      'group' => array(
         'id' => $row['group']
       )
   );
}

answered May 17, 2013 at 14:16

Michael Marr

2,09914 silver badges13 bronze badges

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

Comments

Ignacio Vazquez-Abrams · Accepted Answer · 2013-05-17 14:15:21Z

2

Do this first:

$row['group'] = Array('id' => $row['group']);

answered May 17, 2013 at 14:15

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Comments

chandresh_cool · Accepted Answer · 2013-05-17 14:15:56Z

1

Construct array like this

$arr = array();
$id = 0;
while($row = mysql_fetch_assoc($query)) {
    $arr[$i]['id'] = $row['id'];
    $arr[$i]['group']['id'] = $row['group'];
    $i++;
}

$json = json_encode($arr);

answered May 17, 2013 at 14:15

chandresh_cool

11.9k3 gold badges32 silver badges46 bronze badges

1 Comment

Korki Korkig Over a year ago

if you change $id = 0; as $i = 0; this works also. But @Michael Marr 's way looks like easier to me. Ofcourse thank you!

Stephan · Accepted Answer · 2013-05-17 14:17:15Z

0

try this :

$arrayJson["id"] = $row["id"];
$arrayJson["group"]["id"] = $row["group"];
echo json_encode($arrayJson)

answered May 17, 2013 at 14:17

Stephan

8,1103 gold badges40 silver badges42 bronze badges

2 Comments

Korki Korkig Over a year ago

Without using a $i counter as @chandresh_cool did, this gives only one object(the last one) after the while loop. Thanks anyway.

Stephan Over a year ago

yeah , i forgot you have multiple rows :(

Collectives™ on Stack Overflow

Customizing the format of JSON data from PHP/MySQL

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related