使用PHP从SQL生成嵌套的JSON [英] Generate nested JSON from SQL using PHP
本文介绍了使用PHP从SQL生成嵌套的JSON的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用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屋!
查看全文