使用PHP从SQL生成嵌套的JSON [英] Generate nested JSON from SQL using PHP

问题描述

我正在尝试使用PHP生成JSON API，以用作我的Android应用程序数据库的远程服务器接口.

I'm trying to generate a JSON API using PHP to be used as remote server interface for my Android application database.

我设法像这样生成JSON:

I managed to generate JSON like this:

{
    products: [
        {
            product_name: "Samsung",
            product_category: "phones",
            shop_name: "Gadget Store",
            user_id: "1",
            price: "1999",
            date: "2015-04-05",
            time: "11:14:44"
        },
        {
            product_name: "Samsung",
            product_category: "phones",
            shop_name: "IT Store",
            user_id: "1",
            price: "1899",
            date: "2015-04-01",
            time: "13:00:00"
        },
        {
            product_name: "Motorola",
            product_category: "phones",
            shop_name: "IT Store",
            user_id: "1",
            price: "1499",
            date: "2015-04-02",
            time: "10:31:29"
        }
    ]
}

但是我想我需要一个类似这样的嵌套JSON:

But I guess I need a nested JSON which is something like this:

{
    products: [
        {
            product_name: "Samsung",
            product_category: "phones",
            shops: [
                {
                    shop_name: "Gadget Store",
                    user_id: "1",
                    price: "1999",
                    date: "2015-04-05",
                    time: "11:14:44"
                },
                {
                    shop_name: "IT Store",
                    user_id: "1",
                    price: "1899",
                    date: "2015-04-01",
                    time: "13:00:00"
                }
             ],
        },
        {
            product_name: "Motorola",
            product_category: "phones",
            shops: [
                    shop_name: "IT Store",,
                    user_id: "1",
                    price: "199",
                    date: "2015-04-02",,
                    time: "10:31:29"
            ],    
        }
    ]
}

我如何获得这个结果?

sql查询来自3个不同的表. 下面是我当前的代码:

The sql query is from 3 different table. Below is my current code:

class productDB
{
    public $product_name = "";
    public $product_category = "";
    public $shop_name = "";
    public $user_id = "";
    public $price;
    public $date = "";
    public $time = "";

    function __construct($product_name, $product_category, $shop_name, $user_id, $price, $date, $time)
    {
        $this->product_name = $product_name;
        $this->product_category = $product_category;
        $this->shop_name = $shop_name;
        $this->user_id = $user_id;
        $this->price = $price;
        $this->date = $date;
        $this->time = $time;
    }

class Shop
{
    public $shop_name = "";
    public $user_id = "";
    public $price;
    public $date = "";
    public $time = "";

    function __construct($shop_name, $user_id, $price, $date, $time)
    {
        $this->shop_name = $shop_name;
        $this->user_id = $user_id;
        $this->price = $price;
        $this->date = $date;
        $this->time = $time;
    }
}

class product
{
    public $product_name = "";
    public $product_category = "";
    public $shop = "";

    function __construct($product_name, $product_category, $shop_name, $user_id, $price, $date, $time)
    {
        $this->product_name = $product_name;
        $this->product_category = $product_category;
        $this->shop = new Shop($shop_name, $user_id, $price, $date, $time);
    }
}


$query = "SELECT a.product_name, a.product_category,
                 b.shop_name,
                 c.user_user_id, c.price, c.date, c.time
          FROM price c, item a, shop b
          WHERE c.product_product_id = a.product_id AND c.shop_shop_id = b.shop_id";

$product_array = array();
if ($result = $dbc->query($query)) {

    while ($obj = $result->fetch_object()) {
        $temp_product[] = new ProductDB(

            $obj->product_name,
            $obj->product_category,
            $obj->shop_name,
            $obj->user_id,
            $obj->price,
            $obj->date,
            $obj->time);

        $product_array = $temp_product;
    }

//Give a name to the array
$array_name = 'products';
$product_array = (array($array_name=>$product_array));

$product_object = json_encode($product_array);
echo $product_object;

推荐答案

为保持一致的JSON结构，第二部分如下所示:

To keep a consistent JSON structure, the second part would look like this:

{
        product_name: "Motorola",
        product_category: "phones",
        shops: [
            shop_name: "IT Store",
            user_id: "1",
            price: "1499",
            date: "2015-04-02",
            time: "10:31:29"
        ]
}

怎么样呢?

$queryStr_products = "Select * FROM item";
$queryStr_price = "Select b.shop_name, c.user_user_id, c.price, c.date, c.time FROM price c, shop b WHERE b.shop_id = c.product_product_id and c.product_product_id =";

$product_array = array();
if ($result = $dbc->query($queryStr_products)) {
    //Iterate over all products returned
    while ($obj = $result->fetch_object()) {
        $product_array[] = array (
            'product_name' => $obj->product_name,
            'product_category' => $obj->product_category,
            'shops' => getPricesForProducts($obj->product_id)
        );
    }
    $result->close();
}

echo json_encode(array('products'=>$product_array));

/**
 * For clarity purposes
 * This returns an array of all product prices for a particular productID
 */
function getPricesForProducts ($productID) {
    //You may need to get a new DB connection
    if ($result = $dbc2->query($queryStr_price.$productID)) {
        $price_array = array();
        while($obj = $result->fetch_object()) {
            $price_array[] = array (
                'shop_name' => $obj->b.shop_name,
                'user_id' => $obj->c.user_user.id,
                'price' => $obj->c.price,
                'date' => $obj->c.date,
                'time' => $obj->c.time,
            );
        }
        $result->close();
        return $price_array;
    } else {
        //Maybe you want to set shop_name to "No Prices Found" and user_id = 0;
        return array();
    }
}

这篇关于使用PHP从SQL生成嵌套的JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！