php - 如何使用多个表的数据格式化嵌套的json

Question

我正在尝试从以一对多关系相互映射的三个表中获取 json 数据，并希望以这种格式显示这些数据

{
    products: [
        {
            "product_id": 121,
            "name": "Nike Fusion",
            "type": "Running Shoe",
            "brand": "Nike",
            "product_Description": "very good",
            "size": {
                      "value": "small"
                      "price": 200$
                    },
            "weight": {
                        "value": "100gm"
                        "price": 100$
                      }
       },
       {

       },
       ...
    ]
}

在此表中的“品牌”“产品描述”和“耐克”“非常好”从第二个表。我正在编写这个 php 文件来实现此目的

<?php
include('db_connection.php');

 $result = mysql_query("select product_name,product_id from products where product_id='1' "); 

    $json_response = array(); //Create an array
    while ($row = mysql_fetch_array($result))
    {
        $row_array = array();
       // $row_array['product_name'] = $row['product_name'];        


        $qus_pk = $row['product_id'];  

        $option_qry = mysql_query("select ss.Name,sv.value_s from specifications ss,specification_value sv where sv.specification_ID=ss.specification_ID and sv.product_id =$qus_pk");
        while ($opt_fet = mysql_fetch_array($option_qry))
        {
            $row_array[] = array(
                 $opt_fet['Name'] => $opt_fet['value_s'],

            );

        }
        array_push($json_response, $row_array); //push the values in the array
    }

    echo json_encode($json_response);

?>

但我得到这样的输出

[
    [
        {
            "brand": "Nike"
        },
        {
            "product_Description": "very good"
        }

    ]
]

Answer 1

而不是以下代码:

    $row_array = array();
   // $row_array['product_name'] = $row['product_name'];        


    $qus_pk = $row['product_id'];  

    $option_qry = mysql_query("select ss.Name,sv.value_s from specifications ss,specification_value sv where sv.specification_ID=ss.specification_ID and sv.product_id =$qus_pk");
    while ($opt_fet = mysql_fetch_array($option_qry))
    {
        $row_array[] = array($opt_fet['Name'] => $opt_fet['value_s'],);

    }


    //array_push($json_response, $row_array);

要像上面那样显示，您必须一次添加产品描述，您可以通过以下方式实现: $row_array = 数组(); //$row_array['产品名称'] = $row['产品名称'];

    $qus_pk = $row['product_id'];  
    $product_desc = '';
    $brand_name = '';

    $option_qry = mysql_query("select ss.Name,sv.value_s from specifications ss,specification_value sv where sv.specification_ID=ss.specification_ID and sv.product_id =$qus_pk");
    while ($opt_fet = mysql_fetch_array($option_qry))
    {
        //$row_array[] = array(
             $opt_fet['Name'] => $opt_fet['value_s'], );
        $product_desc .=$opt_fet['value_s'].",";
        $brand_name .= $opt_fet['Name'].",";

    }
    $product_desc = rtrim($product_desc);
    $brand_name = rtrim($brand_name);
    $row_array['product_Description'] = $product_desc;
    $row_array['brand'] = $brand_name;
    array_push($json_response, $row_array); //push the values in the array