efficient creation of json from mysql output - grouping within uniques

Question

I believe I have been writing some inefficient code here, but can't seem to figure out a more efficient way of writting it.

This often happens with json output, though I've had the issue with some html or xml output as well.

I run a query in my database which brings back an array. Say a persons favorite foods. php's mysql_fetch_assoc returns

 
Array([person]=>john [food]=>chocolate)
Array([person]=>john [food]=>pizza)
Array([person]=>john [food]=>coffee)
Array([person]=>susan [food]=>licorice)

To create my json or html, i've been looping through looking for unique persons, and then adding the food like this

$jsonOut=''
$personAdd='';
while($gotArray=mysql_fetch_assoc(foodArray)){

if($personAdd!='$gotArray['person']){
$jsonOut.="person: $gotArray['person'], foods{";
}
$jsonOut.="food:$gotArray['food'],";
rtrim(jsonOut,',');
$jsonOut.="}";
$personAdd=$array['person'];
}

Now, this isn't a big deal when you only have one value that is repeated constantly in the mysql response, but when you start having 4 or 5 columns where the values are the same, it starts to get quite verbose.

Is there a better way to do this?

-------------Clarifying what the output should look like ----------------- The final json for the above arrays should look like this

[
    {
        "person": "john",
        "foods": [
            {
                "food": "chocolate",
                "food": "pizza",
                "food": "coffee" 
            } 
        ] 
    },
    {
        "person": "susan",
        "food": "licorice" 
    }
]

or susan would have "foods": [{"food":"licorice"}] something like that.

Answer 1

Not the perfect answer, but

$array = array();
while($gotArray=mysql_fetch_assoc(foodArray)){
 $array[] = $gotArray;
};
$jsonOut = array2json($array);

Now array2json doesn't exist though there are several versions others have coded. You could just use json_encode($array); which would be close but not correct.

Answer 2

How about using the built in php function json_encode()?

You can also use Zend_Json from Zend Framework that will automatically use the native php json function if available, and if not, it's own implementation.

Answer 3

It's a bit of a kludge, but one way to do what your looking for a little faster using mysql's help is to use this query:

SELECT person, GROUP_CONCAT(food) AS foods FROM table GROUP BY person

and then you have one string for the foods which you can split into an array

$persons = [];
while($person = mysql_fetch_assoc(foodArray)){
    $person['foods'] = explode(", ", $person['foods']);
    array_push($persons, $person);
}
$jsonOut = json_encode($persons);

Much simpler, even if it is a bit of a hack. Also, I also would recommend using json_encode over rolling your own json. My php is rusty so I'm not certain this code is 100% correct but it's in the ball park.

Answer 4

Using json_encode:

$persons = array ();
while ($row = mysql_fetch_assoc($result))
{
    if (empty($persons[$row['person']]))
        $persons[$row['person']] = array ();
    array_push ($persons[$row['person']], $row['food']);
}

echo json_encode ($persons);

It should result in the following:

{
    'john': [
        'chocolate',
        'pizza',
        'coffee'
    ],
    'susan': [
        'licorice'
    ]
}

Hope this helps