php to json group array

Question

My goal is list singers and click singer show singers songs. This is my php

<?php
    $server = "localhost";
    $username = "user";
    $password = "123456";
    $database = "database";
    $con = mysql_connect($server, $username, $password) or die ("Could not connect: " . mysql_error());
    mysql_select_db($database, $con);
    mysql_query("set names 'utf8'");
    $sql = "SELECT a.singername,b.songname,b.songembeded from singers a,songs b where a.singerid=b.singerid";
    $result = mysql_query($sql) or die ("Query error: " . mysql_error());
    $records = array();
    while($row = mysql_fetch_assoc($result)) {
        $records[] = $row;
    }
    mysql_close($con);
    utf8_encode($records);
    echo json_encode($records);
?>

This is my json output:

[{"singername":"singerA", "songname":"songA", "songembeded":"https://www.youtube.com"},{"singername":"singerA", "songname":"songB", "songembeded":"https://www.youtube.com"}, "singername":"singerB","songname":"songX", "songembeded":"https://www.youtube.com"}]

I want to make like a group singers and their songs:

["singername":"singerA","songs": {"songname":"songA","songembeded":"https://www.youtube.com"},{"songname":"songB","songembeded":"https://www.youtube.com"},
"singername":"singerB","songs": {"songname":"songX","songembeded":"https://www.youtube.com"}]

The grouping you want would have duplicate keys.

user1939584
– user1939584

2015-02-20 19:19:19 +00:00
Commented Feb 20, 2015 at 19:19 — user1939584
– user1939584, Commented Feb 20, 2015 at 19:19
We just don't do it like this any more

Strawberry
– Strawberry

2015-02-20 19:23:01 +00:00
Commented Feb 20, 2015 at 19:23 — Strawberry
– Strawberry, Commented Feb 20, 2015 at 19:23
my singer table singerid,singername

david5
– david5

2015-02-20 19:29:57 +00:00
Commented Feb 20, 2015 at 19:29 — david5
– david5, Commented Feb 20, 2015 at 19:29
my songs table songid,songname,songembeded,singerid...

david5
– david5

2015-02-20 19:30:33 +00:00
Commented Feb 20, 2015 at 19:30 — david5
– david5, Commented Feb 20, 2015 at 19:30
relation two table singerid

david5
– david5

2015-02-20 19:30:56 +00:00
Commented Feb 20, 2015 at 19:30 — david5
– david5, Commented Feb 20, 2015 at 19:30

Alex · Accepted Answer · 2015-02-20 19:38:11Z

1

Change your query a little:

$sql = "SELECT a.singername,b.songname,b.songembeded from singers a,songs b where a.singerid=b.singerid ORDER BY a.singername";

so replace you loop:

$records = array();
    while($row = mysql_fetch_assoc($result)) {
        $records[] = $row;
    }

with this one

$records = array();
$songs= array();
$singername='';
while($row = mysql_fetch_assoc($result)) {

    if ($singername!='' && $singername!=$row['singername']) {
       $records[]= array("singername"=>$singername,"songs"=>$songs);
       $songs = array();
    }
    $songs[] = array('songname'=>$row['songname'],'songembeded'=>$row['songembeded']);
    $singername=$row['singername'];
}
$records[]= array("singername"=>$singername,"songs"=>$songs);

answered Feb 20, 2015 at 19:38

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

php to json group array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related