I am trying to generate a json file from an SQL Server database using sqlsrv.
The code I am using is:
The json file is created as follows:
How can I change the code so that it returns this:
My database fields are:
The code I am using is:
<?php
//load.php
include "config.php";
$data = array();
$sql = "SELECT id, title, start_event, end_event FROM events ORDER BY id";
$result=sqlsrv_query($conn, $sql);
include "error.php";
while ($row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)) {
$data[] = array(
'id' => $row["id"],
'title' => $row["title"],
'start' => $row["start_event"],
'end' => $row["end_event"]
);
}
echo json_encode($data);
?>
The json file is created as follows:
[
{
"id": 1,
"title": "Title 1",
"start": {
"date": "2018-04-11 18:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
},
"end": {
"date": "2018-04-11 21:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
}
},
{
"id": 3,
"title": "Title 2",
"start": {
"date": "2018-04-11 19:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
},
"end": {
"date": "2018-04-11 21:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
}
},
{
"id": 4,
"title": "Title 3",
"start": {
"date": "2018-04-11 20:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
},
"end": {
"date": "2018-04-11 21:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
}
},
{
"id": 5,
"title": "Title 4",
"start": {
"date": "2018-04-11 21:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
},
"end": {
"date": "2018-04-11 21:00:00.000000",
"timezone_type": 3,
"timezone": "Europe/London"
}
}
]
How can I change the code so that it returns this:
[
{
"id": 1,
"title": "Title 1",
"start": "2018-04-11 18:00:00",
"end": "2018-04-11 21:00:00"
},
{
"id": 3,
"title": "Title 2",
"start": "2018-04-11 19:00:00",
"end": "2018-04-11 21:00:00"
},
{
"id": 4,
"title": "Title 3",
"start": "2018-04-11 20:00:00",
"end": "2018-04-11 21:00:00"
},
{
"id": 5,
"title": "Title 4",
"start": "2018-04-11 21:00:00",
"end": "2018-04-11 21:00:00"
}
]
My database fields are:
CREATE TABLE [dbo].[Events](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](255) NULL,
[Start_event] [datetime] NULL,
[End_event] [datetime] NULL,
CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.