Создать вложенный объект JSON с помощью PHP MySQL
У меня есть две таблицы, таблица 1 имеет 2 поля (question_pk, question_name), а таблица 2 имеет 4 поля (ans_pk, options, question_fk и right_answer). Я хочу создать JSON, как следующую структуру
{
"type": "quiz",
"name": "Brand Colors",
"description": "Can you identify these brands by the background color?",
"questions": [
{
"name": "Can you identify this color?",
"description": "#ea4c89",
"answers": [
{
"name": "Dribbble",
"description": "dribbble.png",
"weight": 1
},
{
"name": "Amazon",
"description": "amazon.png",
"weight": 0
},
{
"name": "Apple",
"description": "apple.png",
"weight": 0
}
]
},
{
"name": "Can you identify this color?",
"description": "#3d9ae8",
"answers": [
{
"name": "Youtube",
"description": "youtube.png",
"weight": 0
},
{
"name": "Dropbox",
"description": "dropbox.png",
"weight": 1
},
{
"name": "Wordpress",
"description": "wordpress.png",
"weight": 0
}
]
},
{
"name": "Can you identify this color?",
"description": "#c4302b",
"answers": [
{
"name": "Youtube",
"description": "youtube.png",
"weight": 1
},
{
"name": "Twitter",
"description": "twitter.png",
"weight": 0
},
{
"name": "Vimeo",
"description": "vimeo.png",
"weight": 0
}
]
}
]
}
Мой код PHP
<?php
include '../config/config.php';
if(isset($_GET['sub_cat_id']))
{
$sub_cat_id = $_GET['sub_cat_id'];
$result = mysql_query("select * from $questions where sub_cat='$sub_cat_id' order by level_fk asc");
$json_response = array(); //Create an array
$i=1;
while ($row = mysql_fetch_array($result))
{
$row_array['qus_pk'] = $row['qus_pk'];
$row_array['question'] = $row['question'];
$qus_pk = $row['qus_pk'];
$option_qry = mysql_query("select * from $qus_ans where qus_pk=$qus_pk");
while ($opt_fet = mysql_fetch_array($option_qry))
{
$row_array['options'] = $opt_fet['options'];
$row_array['right_ans'] = $opt_fet['right_ans'];
array_push($json_response,$row_array); //push the values in the array
}
$i++;
}
echo json_encode($json_response);
}
?>
И мой результат я получаю JSON ответ, как следующий
[
{
"qus_pk": "1",
"question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
"options": "45",
"right_ans": "0"},
{
"qus_pk": "1",
"question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
"options": "40",
"right_ans": "0"},
{
"qus_pk": "1",
"question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
"options": "35",
"right_ans": "1"},
{
"qus_pk": "1",
"question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
"options": "50",
"right_ans": "0"},
{
"qus_pk": "2",
"question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
"options": "4 times",
"right_ans": "0"},
{
"qus_pk": "2",
"question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
"options": "1 times",
"right_ans": "0"},
{
"qus_pk": "2",
"question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
"options": "3 times",
"right_ans": "1"},
{
"qus_pk": "2",
"question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
"options": "5 times",
"right_ans": "0"}
]
В моем распоряжении каждый раз, когда вопрос повторяется так, как избежать и если я хочу достичь первой структуры JSON, в моем коде PHP, что&где мне нужно внести изменения? Если кто-нибудь знает, помогите мне.
Решение
Привет, попробуй это,
<?php
include '../config/config.php';
if(isset($_GET['sub_cat_id']))
{
$sub_cat_id = $_GET['sub_cat_id'];
$result = mysql_query("SELECT * FROM $questions WHERE sub_cat='$sub_cat_id' ORDER BY level_fk ASC");
$json_response = array(); //Create an array
while ($row = mysql_fetch_array($result))
{
$row_array = array();
$row_array['qus_pk'] = $row['qus_pk'];
$row_array['question'] = $row['question'];
$row_array['answers'] = array();
$qus_pk = $row['qus_pk'];
$option_qry = mysql_query("SELECT * FROM $qus_ans WHERE qus_pk=$qus_pk");
while ($opt_fet = mysql_fetch_array($option_qry))
{
$row_array['answers'][] = array(
'options' => $opt_fet['options'],
'right_ans' => $opt_fet['right_ans'],
);
}
array_push($json_response, $row_array); //push the values in the array
}
echo json_encode($json_response);
}
?>
Другие решения
Я думаю, что этот код легче понять, и, кстати, он использует mysqli …
Это основано на моей собственной структуре данных, я нахожусь в середине чего-то, и у меня нет времени a.t.m. чтобы адаптировать его к вопросу, но легко понять, как адаптировать его к другим структурам:
$usersList_array =array();
$user_array = array();
$note_array = array();
$fetch_users = mysqli_query($mysqli, "SELECT
ID,
Surname,
Name
FROM tb_Users
WHERE Name LIKE 'G%'
ORDER BY ID") or die(mysqli_error($mysqli));
while ($row_users = mysqli_fetch_assoc($fetch_users)) {
$user_array['id'] = $row_users['ID'];
$user_array['surnameName'] = $row_users['Surname'].' '.$row_users['Name'];
$user_array['notes'] = array();
$fetch_notes = mysqli_query($mysqli, "SELECT
id,
dateIns,
type,
content
FROM tb_Notes
WHERE fk_RefTable = 'tb_Users' AND
fk_RefID = ".$row_users['ID']."") or die(mysqli_error($mysqli));
while ($row_notes = mysqli_fetch_assoc($fetch_notes)) {
$note_array['id']=$row_notes['id'];
$note_array['dateIns']=$row_notes['dateIns'];
$note_array['type']=$row_notes['type'];
$note_array['content']=$row_notes['content'];
array_push($user_array['notes'],$note_array);
}
array_push($usersList_array,$user_array);
}
$jsonData = json_encode($usersList_array, JSON_PRETTY_PRINT);
echo $jsonData;
Результирующий JSON:
[
{
"id": "1",
"surnameName": "Xyz Giorgio",
"notes": [
{
"id": "1",
"dateIns": "2016-05-01 03:10:45",
"type": "warning",
"content": "warning test"},
{
"id": "2",
"dateIns": "2016-05-18 20:51:32",
"type": "error",
"content": "error test"},
{
"id": "3",
"dateIns": "2016-05-18 20:53:00",
"type": "info",
"content": "info test"}
]
},
{
"id": "2",
"cognomeNome": "Xyz Georg",
"notes": [
{
"id": "4",
"dateIns": "2016-05-20 14:38:20",
"type": "warning",
"content": "georg warning"},
{
"id": "5",
"dateIns": "2016-05-20 14:38:20",
"type": "info",
"content": "georg info"}
]
}
]