mysql：从两个表中获取数据取决于第一个表的id和json中的结果

Question

I've 2 tables, I want to get data from the first one depends on "id", and the data from second one depends on the "gid" that same as "id" from the first table, in JSON.

Example:

First Table: 
- - - - - - -
id  name
1   Aaron
2   Caleb
3   Albert
4   Ethan

Second Table:
- - - - - - -
id  gid image
1   1   http://.......image1.jpg
2   1   http://.......image2.jpg
3   2   http://.......image3.jpg
4   3   http://.......image4.jpg
5   3   http://.......image5.jpg
6   3   http://.......image6.jpg

I want the result when I request id=1, Something like this:

"names": [
    {

     "id": 1,

     "name": "Aaron"
          "images":[
             {
                "id" :1
                "url":http://.......image1.jpg
             },
             {
                "id" :2
                "url":http://.......image2.jpg
             }
       ]
   }
]

This some of my code:

$SqlInfo = "Select * from tabel1 where id='$id'";

$obj = json_decode($_GET["x"], false);
$stmt = $con->prepare($SqlInfo);
$stmt->bind_param("ss", $obj->table, $obj->limit);
$stmt->execute();
$result = $stmt->get_result();
$outp = $result->fetch_all(MYSQLI_ASSOC);

echo json_encode(array(
    'status' => 'Ok',
    'name' => $outp
    ));

to be more specific, my above code , bring json format for the first table, I want to insert the result of the second table in the same result of json (of the first table). Thank you ...

Answer 1

first you can use inner join to combine result from both table

$sql = "select 
           t2.gid,
           t2.id,
           t2.image,
           ti.name 
        from 
            second_table as t2
        join first_table as t1 on t2.gid = t1.id 
        where id='$id'           
       ";

$obj = json_decode($_GET["x"], false);
$stmt = $con->prepare($SqlInfo);
$stmt->bind_param("ss", $obj->table, $obj->limit);
$stmt->execute();
$result = $stmt->get_result();
$outp = $result->fetch_all(PDO::FETCH_ASSOC);
/*PDO::FETCH_ASSOC return result in associative array */
$arr = array();
if(!empty($outp)){

    $i =1;/* this is used so that first table data can only be feed to array once*/

    foreach($outp as $val){
        if($i ==1){
            $arr['id'] = $val['gid'];
            $arr['name'] = $val['name'];
        }
        $tmp = array();
        $tmp['id'] = $val['gid'];
        $tmp['url'] = $val['image'];
        $arr['images'][] = $tmp;
        $i++;
        /*increment $i so that we cannot enter $val['name'] repeatedly*/ 
    }
}
$json_arr = array('status' => 'Ok','name' => $arr);
echo json_encode($json_arr);