How to select data to array field?

Question

TableA:

idA: 1, name: John;

TableB:

idB: 1, product: milk, idA: 1;
idB: 2, product: egg, idA: 1;

And I want the finally JSON result look like that:

[
    {
        "idA": 1, 
        "name": "John", 
        "TableB": [
            {
                "idB": 1, 
                "product": "milk", 
                "idA": 1
            },
            {
                "idB": 2, 
                "product": "egg", 
                "idA": 1
            }
        ]
    }
]

I'm using PHP and mySQL. How's the query should be? Thanks too much! I'm so sorry because my English isn't well.

I'm trying to use inner join but it's just give me multi row — Nguyen Quoc Dat, Jun 07 '17 at 02:29
It will, as you got 2 rows to pick from TableB for idA 1. Still can't see your query or code. — ASR, Jun 07 '17 at 02:42
Hi. As ASR said: Please add your SQL code to the question. Were happy to help you debug and evolve your code but we wont write it from scratch for you. — Fabian S., Jun 07 '17 at 06:07
@NguyenQuocDat, I fear, you can't hydrate resulting set with relations in one go with just one request. At least, you need two separate request (to avoid [N+1 problem](https://stackoverflow.com/questions/97197/what-is-select-n1)): one to fetch all needed data from first table (`select * form TableA as A where ...`), and then fetch all related data from TableB (`select * from TableB as B where B.idA in () and ...`) and manually hydrate resulting set. — ankhzet, Jun 07 '17 at 07:48

Nijesh Hirpara · Accepted Answer · 2017-06-07T03:07:15.917

Use Mysqli or pdo. Check the code below. I didn't test but it should work.

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT * FROM TableA ORDER BY idA";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {

  $rows = array();
  while ($row = mysqli_fetch_array($result)) {
    $rows[] = $row;

    $sqlSub = "SELECT * FROM TableB WHERE idA = '{$row['idA']}' ORDER BY idB";  
    $resultSub = mysqli_query($conn, $resultSub);

    if (mysqli_num_rows($resultSub) > 0) {
        while ($rowSub = mysqli_fetch_array($resultSub)) {

            $rows[]['TableB'][] = $rowSub;
        }
    }

  }

  echo json_encode($rows);
} else {
  echo "no results found";
}

It's _exactly_ how N+1 problem looks like. Don't do like this. — ankhzet, Jun 07 '17 at 08:07

score 0 · Answer 2 · answered Jun 07 '17 at 03:01

this query will returns to you a single row. I did not test it but it should work just fine.

select tA.idA,name, group_concat(idB) as cIdB,
 group_concat(product) as cProduct ,
 group_concat(tB.idA) as cIdA 
from Table A tA
left join Table B tB on tA.idA = tB.idA group by tB.idA

it should returns an array looks like this :

idA          1
name         Jhon
cIdB         1,2
cProduct     milk,egg
cIdA         1,1

Hope it helps.

score 0 · Answer 3 · answered Jun 07 '17 at 03:03

Get the rows from SQL with an inner join:

select TableA.*, TableB.* from TableA, TableB where TableA.idA = TableB.idA

Format the data in PHP:

$formattedResults = array(
"idA" => $result.idA, "name" =>  $result.name, "TableB" => array();
);
foreach ($results as $result) {
    $formattedResults["TableB"][] = array("idB"=> result.idB, "product"=> 
    result.product, "idA"=> result.idA;);
}

$json = json_encode($formattedResults);

How to select data to array field?

3 Answers3