Json Response Request

Question

Now I am working on getting a Json response from the data in the following table

Using the following query:

$sql = "select * from subject where subject_name = 'maths'"; $result = mysqli_query($conn,$sql); while($row = mysqli_fetch_array($result)) { $data = new stdClass(); $subject_name = $row['subject_name']; $unit = $row['unit']; $unit_name = $row['unit_name']; $data->subject_name=$subject_name; $emparray[] = array('unit' => $unit, 'unit_name' => $unit_name, ); $data->units=$emparray; } echo json_encode(array($data)); 

I can get a Json response like:

Now what I want was for generic objects without using where where (where subject_name = 'maths')

My required Json o / p is as follows:

Answer 1

You can group the result of subject_name into a PHP array by following the code.

 $sql = "select * from users"; 

OR

 $sql = "select subject_name,unit,unit_name from users"; 

PHP code

 $result = mysqli_query($conn,$sql); $data = array(); function search($subject){ global $data; foreach ($data as $key => $value) { if (isset($value['subject_name']) && $value['subject_name']==$subject) { return $key; } } return false; } while($row = mysqli_fetch_assoc($result)){ $res = search($row['subject_name']); if ($res===false) { array_push($data, array( 'subject_name' =>$row['subject_name'], 'units'=>array($row) ) ); }else{ array_push($data[$res]['units'], $row); } } echo json_encode($data); 

Now you can get your JSON format above the code.

Answer 2

You can try this code

  $sql = "select * from subject; $result = mysqli_query($conn,$sql); while($query_row= mysqli_fetch_array($result)) { if($query_row['subject_name'] == 'English'){ $timeslot = array( "unit" => $query_row['unit'], "unit_name" => $query_row['unit_name'] ); $subjects = addToArray($subjects, $timeslot['subject_name']); } else if($query_row['subject_name'] == 'Maths'){ $timeslot = array( "unit" => $query_row['unit'], "unit_name" => $query_row['unit_name'] ); $subjects = addToArray($subjects, $timeslot['subject_name']); } else if($query_row['subject_name'] == 'Science'){ $timeslot = array( "unit" => $query_row['unit'], "unit_name" => $query_row['unit_name'] ); $subjects = addToArray($subjects, $timeslot['subject_name']); } } 

Answer 3

Try:

 $sql = " select subject_name, group_concat(unit) unit, group_concat(unit_name) unit_name from subject group by subject_name "; $result = mysqli_query($conn, $sql); $data = []; while($row = mysqli_fetch_array($result)) { $subject_name = $row['subject_name']; $units = explode( ',', $row['unit'] ); $unit_names = explode( ',', $row['unit_name'] ); $subject = []; $subject['subject_name'] = $subject_name; $emparray = []; for ($i=0; $i < count($units); $i++) { $emparray[] = array('unit' => $units[$i], 'unit_name' => $unit_names[$i]); } $subject['units'] = $emparray; $data[] = $subject; } echo json_encode($data); 

Answer 4

My code may not be entirely accurate, but it may help you.

 $outer_sql = "SELECT DISTINCT subject_name * from subject; $outer_result = mysqli_query($conn,$outer_sql); while($outer_query_row= mysqli_fetch_array($outer_result)) { $sql = "select * from subject where subject_name = '".$outer_query_row['subject_name']."'"; $result = mysqli_query($conn,$sql); while($row = mysqli_fetch_array($result)) { $data = new stdClass(); $subject_name = $row['subject_name']; $unit = $row['unit']; $unit_name = $row['unit_name']; $data->subject_name=$subject_name; $emparray[] = array('unit' => $unit, 'unit_name' => $unit_name, ); $data->units=$emparray; } } echo json_encode(array($data));