Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

I have an SQL query that converts result to json.

SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM (
    SELECT 'Feature' As type, 
        ST_AsGeoJSON(geom)::json As geometry, 
        row_to_json((name, category)) As properties 
    FROM my_geometry_table
) As f ;

I am using this query in PHP script.

$result = pg_query($connection, $queryString);
$resultArray = pg_fetch_all($result);

echo json_encode($resultArray[0]);

My php result is like this: (array is double-quote )

{
   type: "FeatureCollection",
   features: "[]"
}

But it should be like this:

{
   type: "FeatureCollection",
   features: []
}
share|improve this question

It's important to remember that JSON is a way of representing data inside a string. PHP does not know that something is JSON, only that it's a string.

You need to first decode the result from Postgres (which is a JSON string) so that you have a PHP array. Then you can encode that PHP array back to JSON:

$result = pg_query($connection, $queryString);
$resultArray = pg_fetch_all($result);
// $resultArray[0]['features'] is a string of JSON data from the DB
// For example, let's say it's '[1,2,3]'

// To you, this is obviously JSON: it looks like it,
// and you know you asked for that column to be JSON in the SQL.
// But PHP has no idea; it just sees a 7-character long string;
// so we need to tell it to decode that string:
$decoded_features_array = json_decode($resultArray[0]['features']);
// $decoded_features_array is now a PHP array containing 1, 2, and 3

// Obviously, you can just write that value straight back into the result
$resultArray[0]['features'] = json_decode($resultArray[0]['features']);

// Now the 'features' field of the result is an actual array,
// not a string, so we can do with it whatever we'd do with any other array

// That includes encoding it to send somewhere else - in this case, as JSON:
$json_result = json_encode($resultArray[0]);
// $json_result is now a string with all the fields
// and our PHP array gets encoded as a JSON array as we wanted:
// e.g. '{"type": "FeatureCollection", "features": [1,2,3]}'
share|improve this answer
    
But $db_result['foo'] contains some arrays. How can I know which is array? – bookmarker Feb 4 at 18:24
    
My example was a bit rushed; I've edited it to match the example in your question more closely, and added more comments to explain what's going on. Does that make more sense, or is there something still not clear? – IMSoP Feb 4 at 22:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.