5

I'm trying to create a json object from MySQL results, but not getting the result I need.

Here is the PHP

$json = array();
$result = mysqli_query ($connection, $query);
    echo '['; 

        while($row = mysqli_fetch_array ($result))     
        {
            echo '{';
            echo '"latitude":"'.$row['lat'].'",';
            echo '"longitude":"'.$row['lng'].'",';
            echo '"icon":'.'"./images/'.$row['busColor'].'.png"';
            echo '}';    
        }
        echo ']';

        $jsonstring = json_encode($json);
        echo $jsonstring;

        die();

It outputs this

[{"latitude":"39.976257","longitude":"-83.003464","icon":"./images/pink.png"}][]

But I want this

[{"latitude":"39.976257","longitude":"-83.003464","icon":"./images/pink.png"}]

once I get the result I need to pass the object to a jQuery plugin function if that makes any difference

$.getJSON('myJsonURL, function(myMarkers){
  $("#map").goMap({
    markers: myMarkers
  });
});

Thanks

Improve this question
1
  • isnt the output that you want an object? not an array?.. Commented Nov 20, 2020 at 19:00

3 Answers 3

Reset to default
26

I guess the correct way to do this would be:

$json = array();
$result = mysqli_query ($connection, $query);
while($row = mysqli_fetch_array ($result))     
{
    $bus = array(
        'latitude' => $row['lat'],
        'longitude' => $row['lng'],
        'icon' => './images/' . $row['busColor'] . '.png'
    );
    array_push($json, $bus);
}

$jsonstring = json_encode($json);
echo $jsonstring;

die();
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This works perfectly and demonstrates how to do what David explained. The output looks like this [{"latitude":"39.992989","longitude":"-249.090057","icon":".\/images\/pink.png"}. So json_encode automatically escapes the slashes? Is there a way to suppress this? I don't need to, but just curious.
Yes, the slashes are added by default. You could try urlencode('./images/') and the urldecode(json_encode($json)) - but that's kinda messy!
8

you output your json by hand and then you call json_encode on an empty array() - $json

json_encode() outputs [] on you pass an empty array so your last [] comes from here
$jsonstring = json_encode($json);
echo $jsonstring;
Edit: More about json_encode json_encode php manual

Improve this answer

3 Comments

@OctoPaul I went this route as it was the path of least resistance for me. I changed the last echo to echo '},';, because JSLint didn't like the syntax. I get one extra comma before the closing ], but jQuery parses it though.
Look at the other two answers, it is better to put all your data in an array and call json_encode on that array - this way your json will be valid
Thanks Paul, I updated the correct answer. Your method got me where I wanted to go, so it is still a valid answer.
5

You start by defining an array.

You then generate some JSON manually.

You then convert the array to JSON and output it.

Replace all the echo statements in and at the edge of your while loop with code to generate an associative array containing the data, and then insert it into $json.

Improve this answer

3 Comments

I should probably go this route, but I am a relative noob with PHP so I went the easier route for now. Especially since it's 3:43AM in my part of town. Thanks for the info. +1
That is the easier route. Bashing strings together is going to generate invalid JSON, sooner or later.
I figured as much, but I was looking for a quick and dirty fix after hacking at it for a while. I've resorted to your method using the example @RabidFire provided.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.