create a more complex json object from SQL result set

Question

HI I am returning some data from sql and would like to format it as Json I know I can use json.encode() but the data is nested a little and I am unsure how to achieve this.

this is how I would like my json to look.

[
{"coords":
            {"lat":53.745,"lng":-0.338},
                "iconImage":"https://developers.google.com/maps/documentation/javascript/examples/full/images/beachflag.png",
                    "content":"<h1>Tony G</h1>"},
{"coords":{"lat":53.747,"lng":-0.340},
    "iconImage":"https://maps.gstatic.com/mapfiles/ms2/micons/blue.png",
        "content":"<h1>fred</h1>"}
]

here is my code so far.

require("../PHP/phpsqlajax_dbinfo.php");
$connection=odbc_connect($database, $username, $password);

//Select Test statement 
$query="select 53.745 as lat,-0.338 as lng,'https://developers.google.com/maps/documentation/javascript/examples/full/images/beachflag.png' as iconImage, '<h1>Tony G</h1>' as content union all
select 53.745 as lat,-0.310 as lng,'https://maps.gstatic.com/mapfiles/ms2/micons/blue.png' as iconImage, '<h1>fred</h1>' as content ";

$result=odbc_exec($connection,$query);
//work through result and create JSON
while (odbc_fetch_row($result)){

//what do I do here?

} 

echo json_encode(//theData I would like to return) ;

thanks for any help

Felippe Duarte · Accepted Answer · 2018-10-19 13:45:08Z

2

Just add to an array then encode:

$json = [];

while ($row = odbc_fetch_row($result)){

    $json[] = [
        'coords' => ['lat' => $row['lat'],'lng' => $row['lng']],
        'iconImage' => $row['iconImage'],
        'content' => $row['content'],
    ];
}

echo json_encode($json);

answered Oct 19, 2018 at 13:45

Felippe Duarte

15.2k2 gold badges27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create a more complex json object from SQL result set

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related