Trying to return data from 2 tables in JSON format

Question

I am querying a members table to return all the data in that table, I also have another table that holds members interest and its linked to the members table by the member id. I want to return JSON so the members interest show as an array under the main members object data.

This is what I am getting out

http://www.dile.ng/member/model/getAllMem.php

I will rather just get the interest which shows at the top as seperate objects show as an interest array under each member object

require_once("../functions.php");

$sql="SELECT * FROM members";
$res=mysqli_query($dbconn,$sql);

while($row=mysqli_fetch_assoc($res))
{
    $id=$row['member_id'];
    $sqla="SELECT interest FROM memint WHERE  memid='$id'";
$resa=mysqli_query($dbconn,$sqla);
  while($rowa=mysqli_fetch_assoc($resa))
{
    $data[]=$rowa;
}
   $data[]=$row;
}
echo json_encode($data);

Nick · Accepted Answer · 2019-09-04 03:26:29Z

1

If you want to merge the interest values into the member object, you need to create a separate array for them and then add that as a field to the member object. Something like this should work:

$sql="SELECT * FROM members";
$res=mysqli_query($dbconn,$sql);

while($row=mysqli_fetch_assoc($res)) {
    $id=$row['member_id'];
    $sqla="SELECT interest FROM memint WHERE  memid='$id'";
    $resa=mysqli_query($dbconn,$sqla);
    $interests = array();
    while($rowa=mysqli_fetch_assoc($resa)) {
        $interests[] = $rowa['interest'];
    }
    $data[] = array_merge($row, array('interests' => $interests));
}
echo json_encode($data);

answered Sep 4, 2019 at 3:26

Nick

147k23 gold badges67 silver badges106 bronze badges

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

1 Comment

user3582815 Over a year ago

Thanks a lot, it worked perfectly. Very much appreciated.

Tim Morton · Accepted Answer · 2019-09-04 04:15:48Z

0

The database should be able to do much of the work for you using JSON_ARRAYAGG

$sql = "SELECT members.*, JSON_ARRAYAGG(interest) AS interests FROM 
        members  
        INNER JOIN memint on members.member_id=memint.memid
        GROUP BY members.member_id";

$res=mysqli_query($dbconn,$sql);

$data = array();
while($row=mysqli_fetch_assoc($res)) { $data[] = $row; }

echo json_encode($data);

(disclaimer: I don't use MySQL, but theoretically this should work)

—-

Edit:

I may have a bad assumption here: the value for interests column will probably be a json string, not a php array. Therefore when assigning it to $data you would need to decode it in order for the later encode to work.

It would look more like

while($row=mysqli_fetch_assoc($res)) { 
    $row[‘interests’] = json_decode($row[‘interests’];
    $data[] = $row; 
}

edited Sep 4, 2019 at 4:15

answered Sep 4, 2019 at 3:50

Tim Morton

2,6341 gold badge17 silver badges28 bronze badges

1 Comment

user3582815 Over a year ago

Thanks everyone for your comments, I truely appreciate it. I went with Nick's approach because I don't like using too much SQL, it worked just as expected.

Collectives™ on Stack Overflow

Trying to return data from 2 tables in JSON format

2 Answers 2

1 Comment

Edit:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Edit:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related