Converting mysql query result into very specific json structure

Issue

I’m trying to get familiar with using php and javascript together in a joomla template, but I’ve moved it to a local box (no joomla, just testing the files on their own) to try and make more sense of it but I’m still stuck

I have a php function which is making a select on the database and returning the result, which is then passed to the default html file where I have my Javascript. So I’m getting the data passed to the page.

My issue is this: I have a mock JSON object in my Javascript that is structured exactly how I need/want it but I’m unsure of how I can convert my datebase result from SQL into this exact structure and replace the mock object.

Here is my query:

SELECT name, phone, address, city, state, zip, country, url, logo, lat, lng
from address_info
where published = 1

and when I dump the result on the page, this is the structure:

object(stdClass)#589 (11) {
      ["name"]=>
      string(14) "TEST STORE"
      ["phone"]=>
      string(12) "555-555-5555"
      ["address"]=>
      string(18) "123 test ave"
      ["city"]=>
      string(6) "Denver"
      ["state"]=>
      string(8) "Colorado"
      ["zip"]=>
      string(5) "80223"
      ["country"]=>
      string(3) "USA"
      ["url"]=>
      string(7) "http://test.com"
      ["logo"]=>
      string(29) "logo.png"
      ["lat"]=>
      string(12) "-104.9981929"
      ["lng"]=>
      string(10) "39.7164275"
    }
    

Any help on how to convert this result into the exact JSON structure is greatly appreciated

<script type="text/javascript">

const stores = {
        'type': 'FeatureCollection',
        'features': [
          {
            'type': 'Feature',
            'geometry': {
              'type': 'Point',
              'coordinates': [-77.034084142948, 38.909671288923]
            },
            'properties': {
              'storeImage' : 'https://via.placeholder.com/150',
              'storeName' : 'Test Store',
              'phoneFormatted': '(202) 234-7777',
              'address': '1471 P St NW',
              'city': 'Washington   DC',
              'country': 'United States',
              'postalCode': '20005',
              'state': 'D.C.'
            }
          },
          ]
          };
          
  </script>

Solution

You need to manually assign all values to their corresponding place. So in your specific use case, this would look like this:

// I've set your result from MySQL to be stored in $data

// Populate a new array
$return = [
    'type' => 'FeatureCollection',
    'feature' => [
        [
            'type' => 'Feature',
            'geometry' => [
                'type' => 'Point',
                'coordinates' => [
                    $data->lat,
                    $data->lng
                ]
            ],
            'properties' => [
                'storeImage' => $data->logo, // you need to convert this into an actual URL
                'storeName' => $data->name,
                'phoneFormatted' => $data->phone, // this needs formatting aswell
                'address' => $data->address,
                'city' => $data->city,
                'country' => $data->country,
                'postalCode' => $data->zip,
                'state' => $data->state
            ]
        ]
    ]
];

// output the PHP array as JSON
echo json_encode($return);

Answered By – stui

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0