I am trying to generate a json file from an SQL Server database using sqlsrv.
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);
?>
Select all Open in new window
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"
}
}
]
Select all Open in new window
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"
}
]
Select all Open in new window
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
Select all Open in new window
SQL Server has data types, that are not only a scalar value, some types even have functionalities attached, as long as you're on the T-SQL level, eg STDistance.
Of course, effectively you turned the datetime objects you get back from SQL Server to string type, but it should work out.