Create nested json object using php mysql
Hi try this,
<?phpinclude '../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);}?>
I think this code is easier to figure out and by the way it uses mysqli ...
This is based on my own data structure, I am in the middle of something and I have no time a.t.m. to adapt it to the question but should easy to figure out how to adapt it to other structures :
$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; Resulting 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" } ]}]
A basic class to handle nesting tables into a php array.
PHP CLASS
// chain data into a php array, filtering by relation to parent, based on a structure definition array// nest child data by relation to parent data// assign a array label "arr_label" to child definition to define what key the filtered data will use // assign a parent key "p_key" and a child key "c_key" to child definition to assign connection points from child to parent// load array data to filter into "arr" key on child definitionclass class_chain_filter{ var $return_arr; function __construct() { } // CONSTRUCTOR // input a defined filter tree array and output a processed result function chain_filter($filter_tree) { // can feed either a single record a set of rows... if(!$this->is_assoc($filter_tree['arr'])) $this->return_arr = $filter_tree['arr']; // root for return array else $this->return_arr[] = $filter_tree['arr']; // force a numeric array so return is consistent. $this->do_chain_filter( $filter_tree['next_arrs'], $this->return_arr ); return $this->return_arr; } // $this->chain_filter($filter_tree) // public function is_assoc($arr) { return array_keys($arr) !== range(0, count($arr) - 1); } function do_chain_filter(&$tree_arr, &$final_arr) { $cur_final_node = &$final_arr; if( !is_array($cur_final_node) ) return false; // send the next_arrs foreach($final_arr as $f_key => $f_arr) { $cur_final_node = &$final_arr[$f_key]; foreach($tree_arr as $n_key => $n_arr) { $cur_tree_node = $tree_arr[$n_key]; // $final_cur_el['arr_label'] = 'true'; $next_final_node = &$cur_final_node[$cur_tree_node['arr_label']]; // data up hombre // filter out array elements not related to parent array $result = $this->children_of_parent( $cur_final_node, $cur_tree_node['arr'], $cur_tree_node['p_key'], $cur_tree_node['c_key'] ); $next_final_node = $result; // now recurse if we have more depths to travel... if(!empty($cur_tree_node['next_arrs'])) $this->do_chain_filter($cur_tree_node['next_arrs'], $next_final_node); } } } // this->function chain_filter(&$tree_arr, &$final_arr) // take 2 arrays // first array is an associative array. // second array is an array of associative arrays. // return children of second array that belong to parent array function children_of_parent($arr_parent, $arr_children, $key_parent, $key_child ) { // parent = a record // child = multiple records // filter out children that don't apply to parent. // return the result $parent_id = $arr_parent[$key_parent]; foreach($arr_children as $arr_child) { $child_id = $arr_child[$key_child]; if($child_id == $parent_id) $return_arr[] = $arr_child; } if(!empty($return_arr)) return $return_arr; } // this->children_of_parent($arr_parent, $arr_children, $key_parent, $key_child )} // end. class class_chain_filterLOAD UP SOME TABLES (USE YOUR OWN PREFERRED DB CLASS)
$areas = $db->get("SELECT * FROM areas");$rooms = $db->get("SELECT * FROM rooms");$exits = $db->get("SELECT * FROM exits");DEFINE OUR RETURNED ARRAY TREE STRUCTURE
// predefine tree structure for generation// structure definition array example...$tree_arr = array ( "arr" => $areas, // root (can be multiple rows or a single record) "next_arrs" => array ( // children 0 => array( "arr" => $rooms, // array to filter against parent "arr_label" => "rooms", // for the php array label "p_key" => "id", // field name of parent // eg) id "c_key" => "areaid", // this array's field name that links it to parent "next_arrs" => array( // children 0 => array( "arr" => $exits, // array to filter against parent "arr_label" => "exits", // for the php array label "p_key" => "id", // field name of parent / blank if root / eg) id "c_key" => "roomid" // this array's field name that links it to parent ) ) ) )); // $tree_arrNOW CREATE OBJECT AND PROCESS INTO DESTINATION ARRAY
$c = new class_chain_filter();$return_arr = $c->chain_filter($tree_arr);print_r($return_arr);... AND THE OUTPUT SHOULD LOOK LIKE ...
Array ([0] => Array ( [id] => 1 [name] => New World [author] => anon [resetfreq] => 3 [rooms] => Array ( [0] => Array ( [id] => 1 [areaid] => 1 [name] => Entrance [description] => The air is humid here. [exits] => Array ( [0] => Array ( [id] => 1 [roomid] => 1 [toroomid] => 2 [direction] => n [description] => A Hall [keyid] => 1 ) [1] => Array ( [id] => 5 [roomid] => 1 [toroomid] => 3 [direction] => s [description] => Entrance [keyid] => ) ) ) [1] => Array ( [id] => 2 [areaid] => 1 [name] => A Corridor [description] => Seems nothing is really going on in this room. Bland tapestry and nothing worth really hanging around for. From the west comes the sound of people training. To the east you can hear people practicing skills and abilities. [exits] => Array ( [0] => Array ( [id] => 2 [roomid] => 2 [toroomid] => 1 [direction] => s [description] => A Corridor [keyid] => ) [1] => Array ( [id] => 7 [roomid] => 2 [toroomid] => 4 [direction] => e [description] => Practice Room [keyid] => ) [2] => Array ( [id] => 9 [roomid] => 2 [toroomid] => 5 [direction] => w [description] => Training Room [keyid] => ) [3] => Array ( [id] => 11 [roomid] => 2 [toroomid] => 8 [direction] => n [description] => A Bend [keyid] => ) ) ) ) ))
and then you could just json_encode the array to turn it from a PHP array into a JSON string