How to create multidimensional JSON Array from MYSQL database?

Question

I have the following code that will print some JSON from mysql database.

However, when i check the JSON output, the JSON is invalid.

This is the JSON out put on my PHP page:

[{
    "id": "1",
    "title": "test title",
    "about": "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.",
    "address": "some address goes here",
    "lat": "51",
    "lon": "0.888",
    "distance": {
        "miles": 3.973345345,
        "kilometers": 6.39345348
    }
}][{
    "id": "3",
    "title": "test title 5",
    "about": "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.",
    "address": "some address goes here",
    "lat": "51",
    "lon": "0.256",
    "distance": {
        "miles": 3.9735000071413,
        "kilometers": 6.3947283954928
    }
}]

This is my PHP code:

header('Content-type: application/json');

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2) {    
$theta = $longitude1 - $longitude2;
$miles = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos (deg2rad($latitude2)) * cos(deg2rad($theta)));
$miles = acos($miles);
$miles = rad2deg($miles);
$miles = $miles * 60 * 1.1515;
$kilometers = $miles * 1.609344;
return compact('miles','kilometers'); 
}

$records = array();


/* soak in the passed variable or set our own */
$latitude2 = floatval($_GET['latitude']); //no default
$longitude2 = floatval($_GET['longitude']); //no default


/* grab the posts from the db */
   $sql = "SELECT * FROM businesses ORDER BY id";
   $query = mysqli_query($db_conx, $sql);
   $productCount = mysqli_num_rows($query);
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
{

extract($row);


$latitude = $row['lat'];    
$longitude = $row['lon'];


$point1 = array('lat' => number_format ($latitude,4,'.',''), 'long' => number_format ($longitude,4,'.',''));
$point2 = array('lat' => number_format ($latitude2,4,'.',''), 'long' => number_format ($longitude2,4,'.',''));
$distance = getDistanceBetweenPointsNew($point1['lat'], $point1['long'], $point2['lat'], $point2['long']);


$channel = array(
'id' => $id,
'title' => $title,
'about' => $about,
'address' => $address,
'lat' => $latitude,
'lon' => $longitude,
'distance' => $distance,
);
}   
$channels = array($channel);
$records[] = $channel;
//$json = json_encode($channel);
//echo $json;

echo '' . json_encode($records, JSON_UNESCAPED_SLASHES) . '';
}

If I have only one record in the database, it works fine but when its more than 1 record, the JSON output is invalid.

Could someone please advice on this issue?

@RedBottle apologies, I don't know whats wrong with stackoverflow site but sometimes it misses to add the codes. I've added my code now. — James Juanjie
– James Juanjie, Commented Jun 14, 2019 at 12:00
@u_mulder, that will only print 1 record from myql database. — James Juanjie
– James Juanjie, Commented Jun 14, 2019 at 12:03
Why did you open a second scope inside the while ? while () { {} } ? — Cid
– Cid, Commented Jun 14, 2019 at 12:05

u_mulder · Accepted Answer · 2019-06-14 12:14:41Z

2

Here how it should be:

while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
    // code without `extract`:

    $latitude = $row['lat'];    
    $longitude = $row['lon'];

    $point1 = array('lat' => number_format ($latitude,4,'.',''), 'long' => number_format ($longitude,4,'.',''));
    $point2 = array('lat' => number_format ($latitude2,4,'.',''), 'long' => number_format ($longitude2,4,'.',''));
    $distance = getDistanceBetweenPointsNew($point1['lat'], $point1['long'], $point2['lat'], $point2['long']);

    $channel = array(
        'id' => $row['id'],
        'title' => $row['title'],
        'about' => $row['about'],
        'address' => $row['address'],
        'lat' => $latitude,
        'lon' => $longitude,
        'distance' => $distance,
    );

    $records[] = $channel;
}
// echo ONCE
// and as `json_encode` returns a string - using '' is USELESS
echo json_encode($records, JSON_UNESCAPED_SLASHES);

Also, as $point2 never changes, it's better to set

$point2 = array('lat' => number_format ($latitude2,4,'.',''), 'long' => number_format ($longitude2,4,'.',''));

outside while loop.

edited Jun 14, 2019 at 12:14

answered Jun 14, 2019 at 12:05

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

3 Comments

Cid Over a year ago

I would add the whole content of the loop, there is weird things (the second scope, the usage of extract() ...)

James Juanjie Over a year ago

@Cid, the witout the extract() i cannot create the multidimendional array. take a look at the $channel = array(....

Cid Over a year ago

@JamesJuanjie 'id' => $row['id'] and so on ?

Collectives™ on Stack Overflow

How to create multidimensional JSON Array from MYSQL database?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related