Generate nested JSON from SQL using PHP

Question

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

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"
        }
    ]
}

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"
            ],    
        }
    ]
}

How can I achive this result?

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;

What is it about your code that does not achieve the result? — Jay Blanchard, Apr 07 '15 at 21:50
`new ProductDB` -> without knowing what that is, I assume you just need to have another level of nesting in your class for each shop. You haven't really included enough info to go on. — Devon Bessemer, Apr 07 '15 at 21:52
Even if you will know how does the ProductDB look like then it will not solve the problem, because for each shop data you create a new object. If you want to get the requested output, you must create one object for each product and then save the shops data into it. Also I think you will not achieve the requested output with json_encode, because you want to get 2 different data structures from the same class ("Samsung" has shops as an objects array and "motorolla" not). For that you will have to have 2 different classes. So consider stadardizing your outputs structure :) — AdamM, Apr 07 '15 at 22:03
@JayBlanchard I need to nest the different shops info for the same product name. — FaizFizy, Apr 08 '15 at 01:11
@AdamM thanks for pointing that out. I've added more information to the question and the Motorola also will have shops as an object array. My bad, it's a mistake. — FaizFizy, Apr 08 '15 at 01:43

score 1 · Answer 1 · answered Apr 08 '15 at 20:57

Here you have a solution which does not require subqueries.

It looks like at least in this example you do not need the ProductDB so we will use directly Product class

To keep the shops in the Product object we need the holder there. We will change $shop into $shops which will hold an array with Shop objects.

Product class:

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

Ass you can see there is a new function which adds new shops to the array.

Now the part which will group the shops into the products.

$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;
}

TO group the data properly it is necessary to sort the products data. So add at the end of the query ORDER BY a.product_name, a.product_category.

That's it :) Let me know how if it worked (if you will use it)

Also if you would like to declare the class properties private and still use json_encode to get the JSON representation of your classes, you can use JsonSerializable interface.

Shop class

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

Product class

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

Main code

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

Have fun :)

I've tried both code, yours and @BSimpson. Both are working and achieved my expected output. Thanks for taking your time assisting me. And I learn new things from your code too! — FaizFizy, Apr 10 '15 at 05:37
Nice to hear that you learned something. Just for the future, the biggest difference in our codes is that @BSimpson is using suqueries which makes data fetching form the DB much more time consuming for a bigger amount of data. For a small one it probably does not make much difference. Just wanted to give you a tip to avoid subqueries if possible. It's a good habit :) — AdamM, Apr 10 '15 at 08:26
@AdamM Have a look please, I need to solve this http://stackoverflow.com/questions/29501883/generate-nested-json-from-sql-using-php — hawkeye, Sep 13 '15 at 17:26

BSimpson · Accepted Answer · 2015-04-08T15:40:21.193

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"
        ]
}

How about something like this:

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

thanks for correcting my JSON structure. I've added some information to the question, especially the query part. — FaizFizy, Apr 08 '15 at 01:47
@FaizFizyRosle Given the new information, I will update my answer in the morning when I'm at a better computer. — BSimpson, Apr 08 '15 at 02:41
@BSimpson Have a look please http://stackoverflow.com/questions/29501883/generate-nested-json-from-sql-using-php — hawkeye, Sep 13 '15 at 17:25

Generate nested JSON from SQL using PHP

2 Answers2

Linked