PHP PDO retrieving json array from database - removing unwanted chars

Question

I am storing a JSON array of data within a row in a database and attempting to retrieve the array in order to use in a foreach loop.

The code below is the code that I will be using for the foreach loop, but I am having some troubles actually retrieving the data, using PDO, as needed to be used.

$data = '[{"id":"H592736029375"},{"id":"K235098273598"},{"id":"B039571208517"}]';
$array = json_decode($data, true);
foreach($array as $data){
    echo $data['id'];
}

The problem I am having is retrieving the array to put into $data using PDO. Currently I have this query and code, the output for which is below.

$statement = $this->db_connection->prepare("SELECT gp FROM gs WHERE c_s= :c_s");
                $statement->bindParam(':c_s', $c_s);
                $statement->execute();
                $data = $statement->fetchAll();
$result = json_encode($data, true);
                return $result;

Mixed with the following code to show the array:

foreach ($result as $key) {
            echo $key['id'];
        }

        print_r($result);
        die();

This gives an error for the for each, and outputs the array (unusable) as following:

[{"gp":"[{\"id\":\"H592736029375\"},{\"id\":\"K235098273598\"},{\"id\":\"B039571208517\"}]","0":"[{\"id\":\"H592736029375\"},{\"id\":\"K235098273598\"},{\"id\":\"B039571208517\"}]"}]

Being as the first segment of code I have I can use the data as I needed I'm just in need of some guidance of how to correctly get the array from the database to then be used in the foreach.

I'm aware that I'm currently using json_encode instead of json_decode, using json_decode gives the following error:

Warning: json_decode() expects parameter 1 to be string, array given

Suggestions for my errors would be much appreciated

Answer 1

fetchAll() returns an array of rows, not the single field you expect:

$statement = $this->db_connection->prepare("SELECT gp FROM gs WHERE c_s= :c_s");
$statement->bindParam(':c_s', $c_s);
$statement->execute();
$rows = $statement->fetchAll();
$data = $rows[0]['gp'];
$result = json_decode($data);

Answer 2

You are expecting $data to be a string while it's an array. Either use $statement->fetchColumn() to retreive a single field or use json_decode($data['gp'])

Other then that, I'm not exactly sure why you are looping over a json_encoded array, you can't do that.

• json_encode() formats variables for javascript object notation.
• json_decode() loads variables from strings(READ: string, not array) in the javascript object notation.

To make it even clearer:

• use json_encode() to insert data in mysql.
• use json_decode() to put back a column to their corresponding php variable/state.