php - 如何选择数据到数组字段？

Question

表A:

idA: 1, name: John;

表B:

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

我希望最终的 JSON 结果看起来像这样:

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

我正在使用 PHP 和 mySQL。查询应该如何进行？太感谢了!我很抱歉，因为我的英语不好。

Answer 1

使用Mysqli或pdo。检查下面的代码。我没有测试，但它应该可以工作。

// 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";
}