title: Display PostgreSQL JSON in PHP
body: <p>I am querying <strong>3 tables in PostgreSQL</strong> and converting the result <strong>into a</strong> <strong>nested json object</strong>. I am using <strong>PHP (and the Slim framework)</strong> as my application code. I am able to return a <strong>valid nested JSON object using PostgreSQL's</strong> built-in json operators. However, I'm having trouble rendering that JSON object in PHP.</p>
  
  <p>PHP's <strong>json_encode</strong> operator seems to be trying to perform a JSON conversion on the provided obect (which is already in JSON format). <strong>And I can't get PHP to simply echo, print (or print_r) out the valid result provided by PostgreSQL.</strong> PHP keeps adding extra characters to the result.... I've tried :</p>
  
  <ul>
  <li>(a) not using the PostgreSQL JSON operators, </li>
  <li>(b) using PHP's json_decode before using json_encode,</li>
  <li>(c) simply running echo and print_r statements, and</li>
  <li>(d) I just started toying with using str_replace to remove the extraneous characters, but that is starting to feel scuzzy... </li>
  </ul>
  
  <p>I'm pretty sure I'm doing something obviously wrong. I'm just at a standstill on this one. Any help is greatly appreciated!</p>
  
  <p><strong>The 3 tables are :
  1. Company
  2. Contact
  3. Pic</strong></p>
  
  <p>CONTACT >----(:company_id)----- COMPANY ----(:company_id)-----&lt; PIC</p>
  
  <p><em>(A single company can have many contacts, and can have many pics... No relationship b/w the CONTACT and PIC tables)</em></p>
  
  <p><strong>HERE IS THE PHP CODE WHICH USES THE POSTGRESQL QUERY :</strong></p>
  
  <pre><code>&lt;?php
  
  $app-&gt;get('/companies/{id}', function($request)
  {
  $db = pg_connect("host=localhost dbname=cool_db_name user=postgres")
                  or die('Could not connect: ' . pg_last_error());
  
      $id = $request-&gt;getAttribute('id');
  
      $query = "
  
      SELECT row_to_json(t)
      FROM (
          SELECT company_id, company_name, company_street, company_city, company_state, company_zip, active_ind
  
          , (
          SELECT array_to_json(array_agg(row_to_json(c)))
          FROM (
              SELECT ct.contact_id, ct.contact_lname, ct.contact_fname, ct.contact_email, ct.active_ind
              FROM contact ct
              WHERE ct.company_id = c.company_id
              ORDER by ct.contact_lname
          ) c
          ) AS contacts
  
          , (
              SELECT array_to_json(array_agg(row_to_json(p)))
              FROM (
              SELECT p.pic_id, p.pic_location, p.active_ind
              FROM pic p
              WHERE p.company_id = c.company_id
              AND p.active_ind = 1
              ORDER by p.pic_id
          ) p
          ) AS pics
  
          FROM company c
  
          WHERE c.alive_ind = 1 AND c.company_id = " . $id . "
      ) t
      ;";
  
  
      $result = pg_query($db, $query);
  
      while($row = pg_fetch_assoc($result))
      {
          $data[] = $row;
      }
  
      if(isset($data))
      {
          header('Content-Type: application/json');
          echo json_encode($data);
      }
  });
  </code></pre>
  
  <p><strong>1. Again, if I run that PostgreSQL query by itself, the result is VALID JSON (below) :</strong></p>
  
  <p>{
      "company_id": 1,
      "company_name": "Company #1",
      "company_street": "111 Fun Ave",
      "company_city": "Creve Coeur",
      "company_state": "MO",
      "company_zip": "10002",
      "active_ind": 1,
      "contacts": [{
          "contact_id": 1,
          "contact_lname": "Figgis",
          "contact_fname": "Cyril",
          "contact_email": "figgis@gmail.com",
          "active_ind": 1
      }, {
          "contact_id": 2,
          "contact_lname": "Kane",
          "contact_fname": "Lana",
          "contact_email": "lana@gmail.com",
          "active_ind": 1
      }, {
          "contact_id": 3,
          "contact_lname": "Tunt",
          "contact_fname": "Cheryl",
          "contact_email": "crazy@gmail.com",
          "active_ind": 1
      }],
      "pics": [{
          "pic_id": 1,
          "pic_location": "profile/pic_900.jpg",
          "active_ind": 1
      }, {
          "pic_id": 2,
          "pic_location": "profile/pic_901.jpg",
          "active_ind": 1
      }, {
          "pic_id": 3,
          "pic_location": "profile/pic_902.jpg",
          "active_ind": 1
      }, {
          "pic_id": 4,
          "pic_location": "profile/pic_903.jpg",
          "active_ind": 1
      }]
  }</p>
  
  <p><em>(sorry for the poor formatting... it looks better in jsonlint.com)</em></p>
  
  <p><strong>2. ...but when I run that same query in the PHP application I get the following result (WEIRD JSON) :</strong></p>
  
  <p>[{"row_to_json":"{\"company_id\":1,\"company_name\":\"Company #1\",\"company_street\":\"111 Fun Ave\",\"company_city\":\"Creve Coeur\",\"company_state\":\"MO\",\"company_zip\":\"10002\",\"active_ind\":1,\"contacts\":[{\"contact_id\":1,\"contact_lname\":\"Figgis\",\"contact_fname\":\"Cyril\",\"contact_email\":\"figgis@gmail.com\",\"active_ind\":1},{\"contact_id\":2,\"contact_lname\":\"Kane\",\"contact_fname\":\"Lana\",\"contact_email\":\"lana@gmail.com\",\"active_ind\":1},{\"contact_id\":3,\"contact_lname\":\"Tunt\",\"contact_fname\":\"Cheryl\",\"contact_email\":\"crazy@gmail.com\",\"active_ind\":1}],\"pics\":[{\"pic_id\":1,\"pic_location\":\"profile/pic_900.jpg\",\"active_ind\":1},{\"pic_id\":2,\"pic_location\":\"profile/pic_901.jpg\",\"active_ind\":1},{\"pic_id\":3,\"pic_location\":\"profile/pic_902.jpg\",\"active_ind\":1},{\"pic_id\":4,\"pic_location\":\"profile/pic_903.jpg\",\"active_ind\":1}]}"}]</p>
  
  <p><strong><em>Like I said, I'm at a loss right now. Any help would be GREATLY appreciated. Thanks!</em></strong></p>