How to control manually json_encode?

Question

I have some datas that I want to display on a map. To do this, I have to put it into a JSON file like this (because google map) :

{
"id": 1,
"picture": "cité.jpg",
"name": "Cité",
"category": "green",
"address": "52 rue de la victoire",
"about": "Cité en 1935",
"location": {
  "latitude": "48.8757197",
  "longitude": "2.3350033000000394"
},
}

With the latitude and longitude into "location" But today my php code (

$db = mysqli_connect("localhost", "root", "", "parisdb");
    $reponse = $db->query('SELECT * FROM paristable');
    while ($donnees = $reponse->fetch_assoc()){
        $data[]= $donnees;
    }
    $encode_donnees = json_encode($data);
    echo $encode_donnees;

) show me something like this :

{"ID":"1","picture":"cit\u00e99.jpg","name":"cit\u00e9",
"about":"cit\u00e9 en 1935","adress":"52 rue de la victoire","category":"green",
"latitude":"48.8757197","longitude":"2.3350033000000394"}

Is there a way to do it in php? Or do I have to do it in MYSQL? thank you in advance :)

Answer 1

I would like to spend some words on how to solve this class of problems without a lot of assle, not on how to solve this particular case, other have done it quicker, and maybe better, than me.

In such cases, var_export is your friend:

It is a, often overlooked, function that outputs the string representation of a variable. In other words it prints the code you would write to create the variable you pass as parameter.

If you get a sample of your output data, as you already have, do an export of an encoding to have the php array that will be decoded in the format you need.

If you write a simple script like this

<?php 

  // lets discover what php array will produce the desired output format

  $sample_of_output_format = '{
    "id": 1,
    "picture": "cité.jpg",
    "name": "Cité",
    "category": "green",
    "address": "52 rue de la victoire",
    "about": "Cité en 1935",
    "location": {
      "latitude": "48.8757197",
      "longitude": "2.3350033000000394"
    } 
  }';

var_export(json_decode($sample_of_output_format, true));
?>

and run it, you will get

array (
  'id' => 1,
  'picture' => 'cité.jpg',
  'name' => 'Cité',
  'category' => 'green',
  'address' => '52 rue de la victoire',
  'about' => 'Cité en 1935',
  'location' => 
  array (
    'latitude' => '48.8757197',
    'longitude' => '2.3350033000000394',
  ),
)

At this point you just have to copy the output you got in your code substituting sample datas with your db fields names and retaining the structure.

You will easily get a function similar to the following one that you can use in your code to solve your contingent problem.

function encode_for_google_map($dbrow) {
    $result = array (
      'id' => $dbrow['id'],
      'picture' => $dbrow['picture'],
      'name' => $dbrow['name'],
      'category' => $dbrow['category'],
      'address' => $dbrow['address'],
      'about' => $dbrow['about'],
      'location' => 
      array (
        'latitude' => $dbrow['latitude'],
        'longitude' => $dbrow['longitude'],
      ),
    );
    return json_encode($result);
}

and your code becomes

$db = mysqli_connect("localhost", "root", "", "parisdb");
    $reponse = $db->query('SELECT * FROM paristable');
    while ($donnees = $reponse->fetch_assoc()){
        $data[]= $donnees;
    }
    $encode_donnees = array_map(encode_for_google_map, $data);
    echo $encode_donnees;

You can even save the helper script you wrote as a future reference.

Reference

• var_export

Answer 2

You would accomplish this by creating an array that looked like this

$a = [
    "id"=> 1,
    "picture"=> "cité.jpg",
    "name"=> "Cité",
    "category"=> "green",
    "address"=> "52 rue de la victoire",
    "about"=> "Cité en 1935",
    "location"=> [
        "latitude"=> "48.8757197",
        "longitude"=> "2.3350033000000394"
    ]
];

And then calling json_encode($a)

Answer 3

You could use this code after the while

  }

  $data['location']= [$data['latitude'], $data['longitude']];
  unset($data['latitude']);
  unset($data['longitude']);

  $encode_donnees = json_encode($data);
  echo $encode_donnees;