使用PHP从SQL生成嵌套JSON

这里有一个不需要子查询的解决方案。

看起来至少在这个例子中你不需要ProductDB，所以我们将直接使用Product类

为了将商店保留在Product对象中，我们需要那里的持有者。我们将把$shop更改为$shops，它将包含一个带有Shop对象的数组。

产品类别：

class Product
{
    public $product_name = "";
    public $product_category = "";
    public $shops = null;
    function __construct($product_name, $product_category, $shop_name, $user_id, $price, $date, $time)
    {
        $this->product_name = $product_name;
        $this->product_category = $product_category;
        $this->shops = array(new Shop($shop_name, $user_id, $price, $date, $time));
    }
    public function addShop($shop_name, $user_id, $price, $date, $time)
    {
        // because $shops is a public property we can check if it still is an array
        if (!is_array($this->shops)) {
            $this->shops = array();
        }
        $this->shops[] = new Shop($shop_name, $user_id, $price, $date, $time);
    }
}

假设你可以看到有一个新的功能，它将新的商店添加到阵列中。

现在是将商店分组为产品的部分。

$product_array = array();
$currProduct   = null;
if ($result = $dbc->query($query)) {
    while ($obj = $result->fetch_object()) {
        // check if it is a first product or if we have encountered product with different name or category
        if ($currProduct === null
            || $currProduct->product_name !== $obj->product_name
            || $currProduct->product_category !== $obj->product_category) {
            // create new Product with one position in the shops array
            $product = new Product(
                $obj->product_name,
                $obj->product_category,
                $obj->shop_name,
                $obj->user_id,
                $obj->price,
                $obj->date,
                $obj->time);
            $product_array[] = $product;
            // set created product as a currently used
            $currProduct = $product;
        } else {
            // if name and category is the same add shop data to the current product
            $currProduct->addShop(
                $obj->shop_name,
                $obj->user_id,
                $obj->price,
                $obj->date,
                $obj->time);
        }
    }
    $product_array = array('products' => $product_array);
    $product_json = json_encode($product_array);
    echo $product_json;
}

为了正确地对数据进行分组，有必要对产品数据进行排序。因此，在查询的末尾添加ORDER BY a.product_name, a.product_category。

就是这样：）让我知道它是如何工作的（如果你会使用它）

此外，如果您想声明类属性private，并且仍然使用json_encode来获得类的json表示，则可以使用JsonSerializable接口。

商店类别

class Shop implements 'JsonSerializable
{
    private $shop_name = "";
    private $user_id = "";
    private $price;
    private $date = "";
    private $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;
    }
    public function JsonSerialize()
    {
        return get_object_vars($this);
    }
}

产品类别

class Product implements 'JsonSerializable
{
    private $product_name = "";
    private $product_category = "";
    private $shops = null;
    function __construct($product_name, $product_category, $shop_name, $user_id, $price, $date, $time)
    {
        $this->product_name = $product_name;
        $this->product_category = $product_category;
        $this->shops = array(new Shop($shop_name, $user_id, $price, $date, $time));
    }
    public function addShop($shop_name, $user_id, $price, $date, $time)
    {
        $this->shops[] = new Shop($shop_name, $user_id, $price, $date, $time);
    }
    function getName()
    {
        return $this->product_name;
    }
    function getCategory()
    {
        return $this->product_category;
    }
    public function JsonSerialize()
    {
        return get_object_vars($this);
    }
}

主代码

[...]
if ($currProduct === null
    || $currProduct->getName() !== $obj->product_name
    || $currProduct->getCategory() !== $obj->product_category) {
[...]

玩得开心：）

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

{
        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();
    }
}