我正在尝试通过利用信息三个数据库表来创建一个 json 数组。

利用此处找到的信息和解决方案后 source
我开始意识到我必须使用外键进行引用，还必须使用连接查询。

三个表创建如下，每个表都有它们的引用键

Create table categorys(id int primary key auto_increment, category_name varchar(30), buyer_userid int(30));

Create table product(id int primary key auto_increment, product_name varchar(30), buyer_userid int(30),category_id int,
foreign key (category_id) references categorys(id));


Create table sales(items_id int primary key auto_increment, item_name varchar(30),quantity varchar(30), buyer_userid int(30),
product_id int, foreign key (product_id) references product(id));

这是我预期的 Json 格式...

[
{"id":"1","cat_name":"Provision","catbuy_userid":"100",
"products_info":[{"productid":"2","product_name":"Malt","buyer_userid":"100",
"sales_Info":[{"items_id":"1","item_name":"malt","buyer_userid":"100","quantity":"72"}]
}]},
{"id":"2","cat_name":"Cosmetics","catbuy_userid":"200",
"products_info":[{"productid":"3","product_name":"soapy","buyer_userid":"200",
"sales_Info":[{"items_id":"2","item_name":"cream","buyer_userid":"200","quantity":"83"}]
}]}
]

这是我目前的编码

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "mydb"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

    $res_arr = array();
$query = '
    SELECT 
c.id,
c.category_name,
c.buyer_userid,
p.id,
p.product_name,
p.buyer_userid,
p.category_id,
s.items_id,
s.item_name,
s.quantity
    FROM `categorys` c
        INNER JOIN `product` p ON c.id=p.category_id
        INNER JOIN `sales` s ON p.id=s.product_id
    ORDER BY c.id';

    $result = mysqli_query($con,$query);
    while($row = mysqli_fetch_array($result)){    

        $res_arr[] = $row;
}
    echo json_encode($res_arr);
    exit;

?>

这是我的 json 输出

[{"0":"1","id":"1","1":"Provisions","category_name":"Provisions","2":"100","buyer_userid":"100","3":"4","4":"Malt","product_name":"Malt","5":"100","6":"1","category_id":"1","7":"4","items_id":"4","8":"malt","item_name":"malt","9":"93","quantity":"93"},
{"0":"2","id":"2","1":"Cosmetics","category_name":"Cosmetics","2":"200","buyer_userid":"200","3":"3","4":"soapy","product_name":"soapy","5":"200","6":"2","category_id":"2","7":"3","items_id":"3","8":"cream","item_name":"cream","9":"83","quantity":"83"}
]

请问我如何循环以按照上面的 json 格式获取结果。

更新部分

错误警告示例 当我运行代码时，它们有很多错误警告。以下只是其中的一小部分

警告:在第 51 行的 C:\xampp\htdocs\api\test.php 中使用了未定义的常量 products_info - 假定为“products_info”(这将在未来的 PHP 版本中引发错误)

警告:在第 51 行的 C:\xampp\htdocs\api\test.php 中使用了未定义的常量 product_name - 假设为“product_name”(这将在未来的 PHP 版本中引发错误)

最佳答案

我已经修改了你的 php 代码，所以你可以获得你想要的输出。

根据您的要求创建关联数组，而不是从 mysql 查询返回的 assignind 行，然后转换为 json

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "mydb"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

    $res_arr = array();
$query = '
    SELECT 
c.id,
c.category_name,
c.buyer_userid,
p.id,
p.product_name,
p.buyer_userid,
p.category_id,
s.items_id,
s.item_name,
s.quantity
    FROM `categorys` c
        INNER JOIN `product` p ON c.id=p.category_id
        INNER JOIN `sales` s ON p.id=s.product_id
    ORDER BY c.id';

    $result = mysqli_query($con,$query);
    while($row = mysqli_fetch_array($result)){    

        $res['id'] = $row[0];
        $res['cat_name'] = $row[1];
        $res['catbuy_userid'] = $row[2];
        $res['products_info']['id'] = $row[3];

        $res['products_info']['productid'] = $row[4];
        $res['products_info']['product_name'] = $row[5];
        $res['products_info']['buyer_userid'] = $row[6];

        $res['products_info']['sales_info']['items_id'] = $row[7];
        $res['products_info']['sales_info']['items_name'] = $row[8];
        $res['products_info']['sales_info']['buyer_userid'] = $row[6];
        $res['products_info']['sales_info']['quantity'] = $row[9];

        $res_arr[] = $res;
  }

}
  echo json_encode($res_arr);

?>

关于php - 如何使用 php 中的数据库记录正确循环和创建 json，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/55435780/