group by json output in php

Question

<?php
$link = mysqli_connect("localhost", "root", "", "college");
$query = "SELECT branch, year,semester, COUNT(*) count FROM students GROUP BY branch,year,semester;";
$result = mysqli_query($link,$query);
$rows= mysqli_fetch_all($result,MYSQLI_ASSOC);
echo json_encode($rows);

?>

this is my output:

[{"branch":"cse","year":"1","semester":"1","count":"4"},{"branch":"cse","year":"2","semester":"1","count":"1"},{"branch":"cse","year":"2","semester":"2","count":"2"},{"branch":"cse","year":"2","semester":"4","count":"1"},{"branch":"cse","year":"3","semester":"2","count":"1"},{"branch":"cse","year":"3","semester":"3","count":"2"},{"branch":"cse","year":"4","semester":"3","count":"1"},{"branch":"cse","year":"4","semester":"4","count":"2"}, {"branch":"ece","year":"1","semester":"4","count":"2"},{"branch":"ece","year":"2","semester":"2","count":"2"},{"branch":"ece","year":"2","semester":"3","count":"1"},{"branch":"ece","year":"3","semester":"1","count":"1"},{"branch":"ece","year":"3","semester":"3","count":"2"},{"branch":"ece","year":"4","semester":"2","count":"2"},{"branch":"ece","year":"4","semester":"4","count":"2"}, {"branch":"eee","year":"1","semester":"1","count":"1"},{"branch":"eee","year":"1","semester":"4","count":"1"},{"branch":"eee","year":"2","semester":"1","count":"1"},{"branch":"eee","year":"2","semester":"2","count":"3"},{"branch":"eee","year":"2","semester":"3","count":"2"},{"branch":"eee","year":"2","semester":"4","count":"1"},{"branch":"eee","year":"3","semester":"1","count":"1"},{"branch":"eee","year":"3","semester":"2","count":"1"},{"branch":"eee","year":"3","semester":"3","count":"4"},{"branch":"eee","year":"3","semester":"4","count":"3"},{"branch":"eee","year":"4","semester":"2","count":"3"},{"branch":"eee","year":"4","semester":"3","count":"2"},{"branch":"eee","year":"4","semester":"4","count":"1"}]

but i want output like this:

{"cse":

[{"year":"1","semester":"1","count":"4"},
{"year":"2","semester":"1","count":"1"},
{"year":"2","semester":"2","count":"2"},
{"year":"2","semester":"4","count":"1"},
{"year":"3","semester":"2","count":"1"},
{"year":"3","semester":"3","count":"2"},
{"year":"4","semester":"3","count":"1"},
{"year":"4","semester":"4","count":"2"}],

"ece":

[{"year":"1","semester":"4","count":"2"},
{"year":"2","semester":"2","count":"2"},
{"year":"2","semester":"3","count":"1"},
{"year":"3","semester":"1","count":"1"},
{"year":"3","semester":"3","count":"2"},
{"year":"4","semester":"2","count":"2"},
{"year":"4","semester":"4","count":"2"}],

"eee":

[{"year":"1","semester":"1","count":"1"},
{"year":"1","semester":"4","count":"1"},
{"year":"2","semester":"1","count":"1"},
{"year":"2","semester":"2","count":"3"},
{"year":"2","semester":"3","count":"2"},
{"year":"2","semester":"4","count":"1"},
{"year":"3","semester":"1","count":"1"},
{"year":"3","semester":"3","count":"4"},
{"year":"3","semester":"4","count":"3"},
{"year":"4","semester":"2","count":"3"},
{"year":"4","semester":"3","count":"2"},
{"year":"4","semester":"4","count":"1"}]}

suggest any other ways to display above output

maybe duplicated : stackoverflow.com/questions/44014428/…

FatemehNB
– FatemehNB

2018-11-12 16:28:11 +00:00
Commented Nov 12, 2018 at 16:28 — FatemehNB
– FatemehNB, Commented Nov 12, 2018 at 16:28

Nigel Ren · Accepted Answer · 2018-11-12 16:46:12Z

1

Rather than do a mysqli_fetch_all, it may be easier (and might be quicker) to fetch each row at a time and put it into the correct format in one operation...

$result = mysqli_query($link,$query);
$output = [];
while ( $rows= mysqli_fetch_assoc($result)) {
    $output [$rows['branch']][] = ["year" => $rows['year'], 
                   "semester" => $rows['semester'], 
                   "count" => $rows['count']];
}
echo json_encode($output);

edited Nov 12, 2018 at 16:46

answered Nov 12, 2018 at 16:31

Nigel Ren

57.2k11 gold badges49 silver badges59 bronze badges

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

4 Comments

Mahesh Ramineedi Over a year ago

thanks...its working bro...is there any way display output in more attractive way in json format?

Nigel Ren Over a year ago

You can use json_encode($output,JSON_PRETTY_PRINT)

Mahesh Ramineedi Over a year ago

no difference with json_encode($output,JSON_PRETTY_PRINT)

ADyson Over a year ago

@MaheshRamineedi please define "attractive" then...what do you mean by that exactly? What are you wanting to see? JSON is just data for transmission or storage, it's not really meant for displaying nicely, so beyond the "pretty print" setting (and that might not have much effect, depending exactly what client you're using to receive the data) there aren't really any options.

Collectives™ on Stack Overflow

group by json output in php

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related