MySQL JSON Setup

Question

I apologize if there is a good answer out there for this already, I haven't found it yet...

I have created an endpoint URL, that is getting the JSON as a returned $result from a MySQL Select query. I am wanting to have the JSON have sub-layers in it from this query, and am wondering if anyone has a suggestion how. Let me explain better with abbreviated examples.

I have an exercises table:

id  |  name
1   |  squats
2   |  bench press
3   |  deadlift

I also have an exercises_tags table:

id  |  exercise_id  |  tag
1   |  1            |  legs
2   |  1            |  glute
3   |  1            |  quadriceps
4   |  2            |  upper body
5   |  2            |  chest
6   |  3            |  legs

Ok...so the query I am using is:

"SELECT * FROM exercises"

this provides a JSON of:

[{
  "id": 1,
  "exercisename": "Squats"
},
{
  "id": 2,
  "exercisename": "Bench Press (DB)"
},
{
  "id": 3,
  "exercisename": "Deadlift"
}]

What I would like to have is:

[{
  "id": 1,
  "exercisename": "Squats",
  "tags": [{
      "tag": "Legs",
      "tag": "Glute",
      "tag": "Quadriceps"
  }]
},
{
  "id": 2,
  "exercisename": "Bench Press (DB)",
  "tags": [{
      "tag": "Upper Body",
      "tag": "Chest"
  }]
},
{
  "id": 3,
  "exercisename": "Deadlift",
  "tags": [{
      "tag": "Legs"
  }]
}]

I understand this is not quite formatted correctly, but is the idea. I have tried to join the "exercises_tags" table to the "exercises" one to achieve what I am looking for...but when I do this, it provides a new JSON {} for each tag so I would have "squats" listed three times for example. Or if I say "GROUP BY exercises_id", well, then I only get one tag.

Any ideas?

EDIT: Not sure if it makes a difference...but I am creating these endpoints through Routes created in Laravel. I am then using $http in Ionic where I am using a GET to request this JSON where I am then using it in my application.

score 1 · Accepted Answer · edited May 23 '17 at 12:24

1

in short, after you

"SELECT * FROM exercises"

and have an array...before you output the json, loop over the array and

"SELECT * FROM exercises_tags where exercise_id = ".$exercise['id'];

and as you do that, add to the exercise row the "tags" that come back.

for more info on this technique or alternate techniques, have a look at the best way to create nested array from mulitiple queries and loops

edited May 23 '17 at 12:24

Community

1
1

answered Nov 28 '16 at 03:13

WEBjuju

5,797
4
27
36

Your link seems like a good resource that I did not originally find...I am checking that out right now. Thanks...I'll let you know about that. – Ridge Robinson Nov 28 '16 at 03:17

score 0 · Answer 2 · answered Nov 28 '16 at 03:44

below code will give you exact out what your required. it will be in json format only. you can try using array and then user json_encode PHP function to convert it into json.

$ex=$obj->select("*","exercises","1"); AND $obj->select("*","exercises_tags","exercise_id='$id'"); is mysql query.

$ex=$obj->select("*","exercises","1");

$json.="[";
for($i = 0; $i < count($ex) ; $i++){

    $id=$ex[$i]["id"];
    $json.='{ 
    "id":'.$ex[$i]["id"].',     
    "exercisename":"'.$ex[$i]["name"].'", 
    "tag":[{
    ';

    $ex_ids=$obj->select("*","exercises_tags","exercise_id='$id'");
    for($j = 0; $j < count($ex_ids) ; $j++){
        $json.='"tag":"'.$ex_ids[$j]["tag"].",";
    }
    $json.="}]";
    $json.="},";


}

echo $json;

MySQL JSON Setup

2 Answers2