Create JSON output from SQL Server Joined Resultset

Question

I am trying to create a Web Service using PHP. My Data is coming from SQL Server Database. The Query output that I want to use is coming from an inner join. There are multiple rows for same day (Date Column). These are later grouped based on date in the json output.

I have not much exposure to use PHP, and I like to learn any ways that I could create a Nested JSON output as below from the SQL Data.

[
{
    "date": "2018-11-09 18:30:00",
    "details": [{
        "ServerName":"Server1",
        "ScheduleStart":"2018-11-09 08:00:00",
        "ScheduleEnd": "2018-11-09 11:00:00"
    },
    {
        "ServerName":"Server2",
        "ScheduleStart":"2018-11-09 18:00:00",
        "ScheduleEnd": "2018-11-09 21:00:00"
    },
    {
        "ServerName":"Server3",
        "ScheduleStart":"2018-11-09 21:00:00",
        "ScheduleEnd": "2018-11-10 00:00:00"
    }
    ],
    "total":3,
    "summary":[{
        "ServerName": "Server1",
        "Status": "Success"
    },
    {
        "ServerName": "Server2",
        "Status": "Failed"
    },
    {
        "ServerName": "Server3",
        "Status": "Scheduled"
    }
    ]
},
{
    "date": "2018-11-10 18:30:00",
    "details": [{
        "ServerName":"Server3",
        "ScheduleStart":"2018-11-09 21:00:00",
        "ScheduleEnd": "2018-11-10 00:00:00"
    },
    {
        "ServerName":"Server4",
        "ScheduleStart":"2018-11-10 02:00:00",
        "ScheduleEnd": "2018-11-10 05:00:00"
    }
    ],
    "total":2,
    "summary":[{
        "ServerName": "Server3",
        "Status": "Success"
    },
    {
        "ServerName": "Server4",
        "Status": "Scheduled"
    }
    ]
}
]

Please guide me how to start. I have explored a few ways, but nothing has given me enough confidence to create similar output.

The json_encode gives me plain json as below. But, I actually want the nested output as already showed above.

[
    {
        "date": "2018-05-27 00:00:00.000",
        "ServerName": "Server1",
        "ScheduleStart": "2018-05-27 03:00:00.000",
        "ScheduleEnd": "2018-05-27 06:00:00.000",
        "Status": "Scheduled"
    },
    {
        "date": "2018-05-27 00:00:00.000",
        "ServerName": "Server2",
        "ScheduleStart": "2018-05-27 21:30:00.000",
        "ScheduleEnd": "2018-05-28 00:30:00.000",
        "Status": "Scheduled"
    }
]

I use SQL 2014 which doesn't allow nested JSON output from SQL, which would have been lot easier in this case.

Or, I believe, its possible to do multiple calls to Database to collect and create the array, which doesn't look like a good practice.

Look for json_encode() function in php

Prakhar Londhe
– Prakhar Londhe

2018-12-04 02:55:09 +00:00
Commented Dec 4, 2018 at 2:55 — Prakhar Londhe
– Prakhar Londhe, Commented Dec 4, 2018 at 2:55
stackoverflow.com/questions/32483721/…

chintuyadavsara
– chintuyadavsara

2018-12-04 04:51:30 +00:00
Commented Dec 4, 2018 at 4:51 — chintuyadavsara
– chintuyadavsara, Commented Dec 4, 2018 at 4:51

Adarsh · Accepted Answer · 2018-12-04 06:11:19Z

Okay, after some research and trials, I have the format I wanted in the JSON. Below is the sample of the same.

$items= array();
    //Sample SQL statement, its syntactically wrong. Just to omit references. 
    $sql = "select [date], [ServerName], [ScheduleStart], [ScheduleEnd], [Status] from table1 tb1 inner join table2 tb2 on tb1.Subscribe_ID = tb2.Subscribe_ID order by date";

    if($stmt = sqlsrv_query($connect, $sql))
    {
        $Checks = sqlsrv_has_rows( $stmt );
        if ($Checks === true)
        {
            while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) 
            {
                $date = $row['date'];
                if (isset($items[$date])){
                    $item = $items[$date];
                }
                else
                {
                    $item = array
                    (
                    'date' => $date,
                    'details' => array(),
                    'total'   => 1,
                    'summary' => array()
                    );
                }
                $item['details'][] = array(
                    'ServerName'=>$row['ServerName'],
                    'ScheduleStart'=>$row['ScheduleStart'],
                    'ScheduleEnd'=>$row['ScheduleEnd']
                );
                $item['total'] = count($item['details']);
                $item['summary'][] = array(
                                            'ServerName'=>$row['ServerName'],
                                            'Status'=> $row['Status']
                                        );
                $items[] = $item;
            }

            return success($items);
        }
        else
        { 
                return error('Data not found!','404');
        }       
    }

Collectives™ on Stack Overflow

Create JSON output from SQL Server Joined Resultset

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related