Generate nested json format like nvd3 StackAreaChart data

Question

I'm trying to produce a nested json like nvd3 StackedAreaChart uses:

    [
        {
            "key" : "North America" ,
            "values" : [ [ 1025409600000 , 23.041422681023] , [ 1028088000000 , 19.854291255832] ]

        },

        {
            "key" : "Africa" ,
            "values" : [ [ 1025409600000 , 7.9356392949025] , [ 1028088000000 , 7.4514668527298] ]

        },

    ]

source: http://plnkr.co/edit/CIGW0o?p=preview

I want to use data from my database.

How could I achieve that kind json? I'm not so familiar with nested json plus one thing I noticed in the structure of their json is that the values in the values object is a plain integers. It's not quoted like "1025409600000". And when I'm trying to qoute it, the chart can't properly read the data.

Questions

1. How to produce nested json like nvd3 uses? I've come to some research but nothing happens. I found something like I guess it will, but can't get it working. Here and this one too.

2. Is it possible to unquote the value from nested json structure? If it is, how?

Here's what I'm currently working on:

        <?php

        require_once('conn.php');

        $sql = "SELECT ua.user_id,(UNIX_TIMESTAMP(dt.transac_date)*1000) AS transac_date,
                        CONCAT(ui.fname,' ',ui.lname) AS fullname,
                        SUM((dt.item_price - dt.item_srp) * dt.sold) as profit,
                        SUM((dt.item_price) * dt.sold) as total_sales
                FROM dsp_transactions dt
                INNER JOIN user_acct ua ON dt.user_id=ua.user_id
                INNER JOIN user_info ui ON ua.ui_id=ui.ui_id
                GROUP BY ua.user_id";
        $qry = $con->query($sql);

        $data = array();
        if($qry->num_rows > 0) {
            while($row = $qry->fetch_object()) {
                $data[] = array(

                    'key' => $row->fullname,
                    'values' => $row->user_id

                    );
            }
        } else {
            $data[] = null;
        }

        $con->close();

        echo json_encode($data);

        ?>

Which give me this value:

[{"key":"Juan Dela Cruz","values":["1461772800000","5665.00"]},{"key":"Maria Gonzales","values":["1461772800000","275.00"]},{"key":"Apolinario Mabini","values":["1461772800000","100.00"]}]

Thanks in advance :)

EDIT

For further information I want something like this to happen:

         dsp      |    sales     |   profit    |     date    
    --------------+--------------+-------------+--------------
        Juan      |    500       |     100     |    04/24/2016
    --------------+--------------+-------------+--------------
        Maria     |    600       |     200     |    04/24/2016
    --------------+--------------+-------------+--------------
       Apolinario |    700       |     300     |    04/24/2016
    --------------+--------------+-------------+--------------
        Juan      |    550       |     150     |    04/25/2016

Will return like this in json format

    [
        {
            "key" : "Juan",
            "values" : [ ["04/24/2016", "500"], ["04/25/2016", "550"] ]  // "values" loop twice because "juan" has two sales

        },
        {
            "key" : "Maria",
            "values" : [ ["04/24/2016", "600"] ]

        },
        {
            "key" : "Apolinario",
            "values" : [ ["04/24/2016", "700"] ]

        }
    ]

Answer 1

The user id's coming back will be converted to strings. You can convert them back to floats using php:

• http://php.net/manual/en/function.doubleval.php
• http://php.net/manual/en/function.floatval.php

Answer 2

I think the problem starts with how you handled the data returned by your query. As I understand the problem, you want to group all the transaction date and sales (or profit) per person. Thus you need to manipulate the array before encoding it.

See if this snippet runs, do tell me if an error fires, but basically, this is the logic I see that solves the problem:

<?php

require_once('conn.php');

$sql = "SELECT ua.user_id,(UNIX_TIMESTAMP(dt.transac_date)*1000) AS transac_date,
                CONCAT(ui.fname,' ',ui.lname) AS fullname,
                SUM((dt.item_price - dt.item_srp) * dt.sold) as profit,
                SUM((dt.item_price) * dt.sold) as total_sales
        FROM dsp_transactions dt
        INNER JOIN user_acct ua ON dt.user_id=ua.user_id
        INNER JOIN user_info ui ON ua.ui_id=ui.ui_id
        GROUP BY transac_date, ua.user_id";
$qry = $con->query($sql);

$data = array();
$individual_row = array();
if($qry->num_rows > 0) 
{
    while($row = pg_fetch_object($qry)) {
        //we find if there is an existing row with the person
        $indexOfIndividualRow = array_search($row->dsp, array_column($data, 'key'));
        //if no rows of the person are added yet
        if(empty($indexOfIndividualRow)&& !is_numeric($indexOfIndividualRow))
        {
            $individual_row['key'] = $row->dsp;
            $individual_row['values'] = array(array($row->date, $row->sales));
            array_push($data, $individual_row);
        }
        //if there is a row with person as key
        else 
        {
            //if there is a 'values' key
            if(isset($data[$indexOfIndividualRow]['values'])){
                array_push($data[$indexOfIndividualRow]['values'], array($row->date, $row->sales));
            }
            //else if there is no 'values' key
            else $data[$indexOfIndividualRow]['values'] = array($row->date, $row->sales);
        }
    }
}
else {
    $data[] = null;
}

$con->close();

echo json_encode($data);
?>