PHP + PostgreSQL (NOSQL with JSON)

Question

I'm testing the JSON feature in Postgres for NOSQL.

I have PostgreSQL "testdb"

And table "multimedia"

INSERT INTO multimedia (data) VALUES ('{"id":"1", "title":"Iliade", "type":"ebook", "pages":"250"}');

INSERT INTO multimedia (data) VALUES ('{"id":"2", "title":"Pavarotti", "type":"CD", "tracks":"9"}');

INSERT INTO multimedia (data) VALUES ('{"id":"3", "title":"art", "type":"photo", "pixels":"100x100"}');

Now I would like to extract data with PHP

    $conn = pg_connect("host=localhost port=5432 dbname=testdb user=postgres password=xxx");

        if(!$conn) {
            die("Error sorry !");
        } else {
            echo "OK";
        }

        if(!$query = @pg_query("SELECT * FROM multimedia;"))
        die("Error in query:" . pg_last_error($conn));


        $myarray = array();
while ($row = pg_fetch_row($query)) {
  $myarray[] = $row;

}
echo json_encode($myarray);

This will print all the data, But I do not understand how to extract only the value of all field "id" and "title" in to html table ...

https://www.postgresql.org/docs/current/static/datatype-json.html — E_p, May 19 '17 at 22:40
http://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type — JacobW, May 19 '17 at 23:06

score 0 · Answer 1 · answered Dec 28 '18 at 01:52

0

The last few lines may be done like so

while ($row = pg_fetch_row($query)) {
  $myarray[] = $row;
  echo  $row->id . ' - ' . $row->title;

} 
echo json_encode($myarray);

answered Dec 28 '18 at 01:52

Yevgeniy Afanasyev

37,872
26
173
191

PHP + PostgreSQL (NOSQL with JSON)

1 Answers1