tried to get json formatter from mysql

Question

I tried to get a formatter json from mysql, but I dont know how can get it.

$query = 'SELECT data1,data2,data3,data4 FROM database';
$dbquery = mysql_query($query);
$geojson = array();

while($row = mysql_fetch_assoc($dbquery)) {
    $feature = array('loc' => $row['data1'],$row['data2'],'title' => $row['data3'], $row['data4']);
    array_push($geojson,$feature);
};

echo json_encode($geojson);

This outputs:

[{"loc":data1,"0":data2,"title":"data3","1":"data4"},
{"loc":data1,"0":data2,"title":"data3","1":"data4"}]

I want this instead:

[{"loc":[data1,data2], "title":"data3 data4"},
{"loc":[data1,data2], "title":"data3 data4"}]

What should I change in my code?

Jaylen · Accepted Answer · 2015-07-05 00:08:29Z

2

Try this query and see if it gives you the output that you need

        SELECT
    CONCAT('[', 
            GROUP_CONCAT('
                  CONCAT('{"loc": [', data1, data2, '], ')
                , CONCAT('"title": "', data3, data4 ,'"}')
                )
, ']')
        FROM database

you can also do something like this

SELECT
  CONCAT(data1,data2) AS loc
, CONCAT(data3,data4) as title 
FROM database';

Then in PHP you can do this

$feature = array(array('loc'=> $row['loc'],
                 'title' => $row['title']));
echo json_encode($feature);

edited Jul 5, 2015 at 0:08

answered Jul 4, 2015 at 23:44

Jaylen

40.5k43 gold badges140 silver badges228 bronze badges

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

Comments

meda · Accepted Answer · 2015-07-05 00:45:43Z

1

Your loop is not correct it should have been:

while($row = mysql_fetch_assoc($dbquery)) {
    $feature = array('loc' => array(
                              $row['data1'],$row['data2']
                              ),
                     'title' => $row['data3'] .' '.$row['data4']
                );
    array_push($geojson,$feature);
};

answered Jul 5, 2015 at 0:45

meda

45.5k14 gold badges97 silver badges123 bronze badges

Comments

Florian Brinker · Accepted Answer · 2015-07-04 23:26:41Z

1

You should change your $feature line like this:

$feature = array('loc' => array($row['data1'], $row['data2']),'title' => $row['data3'].' '.$row['data4']);

That will result in a json blob like the following, for each line:

{"loc":["data1","data2"],"title":"data3 data4"}

Explanation

loc: You want an array. So you have do define it with array() in PHP. If you want to cast the value to numbers, just typecast them if needed:

array((float)$row['data1'], (float)$row['data2'])
title: Just concatenate the two strings with dots and a space in-between.

edited Jul 4, 2015 at 23:26

answered Jul 4, 2015 at 23:20

Florian Brinker

7357 silver badges17 bronze badges

Collectives™ on Stack Overflow

tried to get json formatter from mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related