Can't figure out how to turn this table into a JSON object

I have a mySQL query that returns the following table:

group_id     groupName          word_id     word
---------    --------------     --------    ------
111          transportation     1           car
111          transportation     2           plane
111          transportation     3           train
222          fruit              4           apple
222          fruit              5           banana
222          fruit              6           pear         

I'd like to send this data through a function (in PHP) which returns it to my ajax call in this JSON format:

{
  "group": {
    "group_id": "222",
    "groupName": "fruit",
    "wordlist": [
      {
        "word": "apple",
        "word_id": "4"
      },
      {
        "word": "banana",
        "word_id": "5"
      },
      {
        "word": "pear",
        "word_id": "6"
      }
    ]
  }
}

I am not sure if this query result is ideal for this kind of thing but I can't seem to get my head around another way. I know its kind of lame not to at least take a shot at it and post my result but I'm not very good with arrays yet so this is making my head spin and I don't know where to start. Many thanks in advance for your help.

Answers


You'll need to write a custom function. But ultimately you'll use json_encode().

A few notes about the custom function:

  • Create the wordList array
  • You'll need to wrap the resulting data under a group.

Need Your Help

doctrine ODM (mongodb) merge doesn't work

php mongodb doctrine doctrine-odm

I'm not having any luck using merge(). I'm doing almost exactly what is documented:

Ajax appending nonce to js files

javascript jquery ruby-on-rails ajax

I'm getting a unique nonce (i think a timestamp) appended to all and any of my js files every time I send a request to my rails web service: