Json Response Query

Question

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

By 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 the Json response as :

Now what i want was for overall subjects without using where clause (where subject_name = 'maths')

My required Json o/p is as below :

You probably need to do multiple queries (first find how many subjects there are, then loop each subject) or do logic with PHP (group items with same subject name). — Daniel Cheung
– Daniel Cheung, Commented Oct 7, 2016 at 5:44

Sumon Sarker · Accepted Answer · 2016-10-07 07:03:23Z

4

You can group result by subject_name to your PHP array by following 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 from above code.

edited Oct 7, 2016 at 7:03

answered Oct 7, 2016 at 6:05

Sumon Sarker

2,7971 gold badge27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Raja Gopal Over a year ago

Hello , your code gives array like "English " but i need with key like "subject_name : English". Kindly check the required json o/p in my question mate . can you clarify me how to get that done

Sumon Sarker Over a year ago

Ahh! Yes Sure! wait please. I am updating code for you! @RajaGopal

Sumon Sarker Over a year ago

There was a little bit mistake. I updated again. Now fixed. Check please. @RajaGopal

Raja Gopal Over a year ago

Thanks a lot sumon , Thanks for your hard work on this question , marked this answer as working :-)

Sumon Sarker Over a year ago

Let us continue this discussion in chat.

Kiran Dsouza · Accepted Answer · 2016-10-07 05:48:57Z

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']);
                    }

    }

Hello , what i want was not by subject name in the code. subject name might change in the future so i want to take by subject name in the db not directly in the code

Aaron Chen · Accepted Answer · 2017-01-19 08:51:05Z

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);

Rohith K N · Accepted Answer · 2016-10-07 06:15:30Z

My code may not be exactly correct, but may still 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));

Collectives™ on Stack Overflow

Json Response Query

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Related