Convert PHP Array to JSON Array and get Response with SLIM3

Question

I have problems with converting the result of a database query to a json array. My Output looks like this:

{
  "user_id": "1",
  "username": "testuser1",
  "user_permissions": [
    {
      "list_id": "1"
    },
    {
      "list_id": "2"
    },
    {
      "list_id": "3"
    }
  ],
  "android_app_id": null,
  "iat": 1537694955,
  "exp": 1537702155
}

The brackets ({}) around the JSON array lead to problems parsing the array in the client's response. I just need to have a simple array like (1, 2, 3).

The Array is produced by database query result (PHP) and then using SLIM3 Operation $this->response->withJson:

$query = "SELECT list_id FROM permissions WHERE user_id='$user_id'";
$sth = $this->dbconnection->prepare($query);
$sth->execute();
$result_permissions = $sth->fetchAll();
return $result_permissions;

I really have problems to convert database results to normal JSON arrays, because PHP only knows associative arrays (numeric or with keys) which leads to bad formatted json arrays.

The json Output is returned to the server. Using SLIM3 Framework I access the JSON Data and the permissions array like this: $user_permissions = $decoded_response['user_permissions']; Now i try to get list-ids with $user_permissions[list'id][0] which gives 1 using print_r command.

What I want to do next is using a database query with IN Operator to check for the permission_ids. Therefore I need to produce an array like (1, 2, 3).. I'm stuck right now, because I don't know how to produce such array from the JSON..

For me the easiest approach would be to directly produce such an array after the database query and add it to the JSON at the beginning but I don't know how to achieve that.

Any hints?

Answer 1

For anyone with the same problem, here is what I did to produce the array:

      $query = "SELECT list_id FROM permissions WHERE user_id='$user_id'";
      $sth = $this->dbconnection->prepare($query);
      $sth->execute();
      $result_permissions;
      for ($i = 0; $i < $sth->rowCount(); $i++) {
        if ($i == $sth->rowCount() - 1) {
          $result = $sth->fetchColumn();
          $result_permissions .= $result;
        } else {
          $result = $sth->fetchColumn();
          $result_permissions .= $result . ",";
        }
      }
      return explode(',', $result_permissions);

If you json_encode this String it will lead to this: {user_permissions":["1","2","3"]} which is what I needed.

Answer 2

If I understood what you need to achieve, you can use array_column php function to get an array of list_ids http://php.net/manual/en/function.array-column.php

$json = '{
    "user_id": "1",
    "username": "testuser1",
    "user_permissions": [
        {
            "list_id": "1"
        },
        {
            "list_id": "2"
        },
        {
            "list_id": "3"
        }
    ],
    "android_app_id": null,
    "iat": 1537694955,
    "exp": 1537702155
    }
';

$arrayFromJson = json_decode($json, true);

$ids = array_column($arrayFromJson['user_permissions'], 'list_id');

print_r($ids);

The output of the print_r will be

Array
(
    [0] => 1
    [1] => 2
    [2] => 3
)

To get a string like (1,2,3) you can use the php implode function https://secure.php.net/manual/en/function.implode.php

$inString= "(" . implode(",", $ids) . ")";

You will get a string like this: (1,2,3).

Keep in mind that using directly variables into a SQL query leads to SQL injection vulnerabilities