Formatting JSON output in PHP MYSQL

Question

I am trying to get the Devbridge Autocomplete jQuery script to work, and I am oh so very close. I can get it to give me suggestions (dropdown values) however I need to use it's data attribute.

The suggested JSON formatting as as follows:

{
    suggestions: [
        { value: "United Arab Emirates", data: "AE" },
        { value: "United Kingdom",       data: "UK" },
        { value: "United States",        data: "US" }
]
}

So far, I have managed this:

{
"suggestions": [
    "Show Name 1",
    "Show Name 2"
],
"data": [
    "1",
    "2"
]
}

The code producing that output is as follows:

$reply = array();
$reply['suggestions'] = array();
$reply['data'] = array();

while ($row = $result->fetch_array(MYSQLI_ASSOC))//loop through the retrieved values
{
    //Add this row to the reply
    $reply['suggestions'][]=$row['SHOW_NAME'];
    $reply['data'][]=$row['SHOW_ID'];
}

//format the array into json data
echo json_encode($reply);

Any suggestions? I can't figure out how to combine the two data elements into one array, let alone prepend them with 'value' or 'data'...

Take a look at this question: http://stackoverflow.com/questions/6054033/pretty-printing-json-with-php — carl-lopez, Jul 22 '13 at 01:56

score 1 · Answer 1 · answered Jul 22 '13 at 01:59

$response = array();
$reply = array();
while ($row = $result->fetch_array(MYSQLI_NUM))//loop through the retrieved values
{
    //Add this row to the reply
    $reply['value'] = $row[0];
    $reply['data'] = $row[1];
    $response['suggestions'][] = $reply;
}
//format the array into json data
echo json_encode($response, JSON_PRETTY_PRINT);

Bob Arezina · Accepted Answer · 2013-07-22T03:02:50.993

1

while($row = $result->fetch_array(MYSQLI_ASSOC))
{

$rec = array();

$rec['value'] = $row['SHOW_NAME'];
$rec['data'] = $row['SHOW_ID'];

$payload['suggestions'][] = $rec;

}

echo json_encode($payload);

edited Jul 22 '13 at 03:02

answered Jul 22 '13 at 02:00

Bob Arezina

462
5
16

`$payload` is never initialized and will result in a PHP Warning. Also, the use of `JSON_PRETTY_PRINT` is recommended when returning the variable back to a Javascript function for improved debugging. – DevlshOne Jul 23 '13 at 13:24

score 0 · Answer 3 · answered Jul 22 '13 at 02:00

0

Not sure if i have got you correctly but if you mean to get both values in one array then use :

$replay[][array('country' => $row['SHOW_NAME'],'data' => $row['SHOW_ID'])];

answered Jul 22 '13 at 02:00

munzx

375
5
13

Formatting JSON output in PHP MYSQL

3 Answers3