Json encode for PostGre SQL query result using PHP

Question

I have written PHP code to convert query result to JSON.

But while using jSON encode, my result only shows field values. There is no field name in the resulting json file.

My code:

$sql = 'select * from "'.$tablename.'"';
$myarray = array();
while ($row = pg_fetch_row($ret)) {
    $myarray[] = $row;
}   
$jsonData = json_encode(array('data' => $myarray));

Result:

{"data":[["Bob", "23", "New York"], ["Alice", "20", "Sidney"], ....]}

Expected Result:

{"data":[
{"firstName":"Bob", "age":"23", "City":"New York"},
{"firstName":"Alice", "age":"20", "City":"Sidney"},...]}

frz3993 · Accepted Answer · 2016-02-16 06:39:08Z

1

Just use pg_fetch_array() passing the type as PGSQL_ASSOC. Example

while ($row = pg_fetch_array($ret,PGSQL_ASSOC)) {
  // your code here
}

or just use pg_fetch_all(). This will index the resulting array with the column name and there's no need to iterate the result set. Example

$myarray = pg_fetch_all($ret);

answered Feb 16, 2016 at 6:39

frz3993

1,63312 silver badges13 bronze badges

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

6 Comments

Sergey Vidusov Over a year ago

Plus, instead of using pg_* functions, I'd highly recommend to switch to using PDO instead.

frz3993 Over a year ago

I totally agree @SergeyVidusov, SQL Injection is a common problem to any SQL database. Although, there are some differents when using PDO with PGSQL or with MySQL, example using the lastInsertId() require no argument for MySQL.

Sreeja Suresh Over a year ago

i tried both suggestions given above. Both are not giving any results

Sreeja Suresh Over a year ago

I think pg_fetch_all() and pg_fetch_array() functions are nor recognized. I have faced this issue earlier also with these function. Hence I started iterating to access individual rows.

frz3993 Over a year ago

Once you have installed the postgre extension, all those functions will be available. Using the pg_fetch_row indicates that you have the extension and you should have other pg_* functions available too. In your code I noticed there's no execution part.

|

Collectives™ on Stack Overflow

Json encode for PostGre SQL query result using PHP

My code:

Result:

Expected Result:

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My code:

Result:

Expected Result:

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related