Decode json strings from a database table column

Question

I have a column in my database that essentially creates an array and I am essentially trying to explode it but I cannot figure out how to get it to work. Its also going into SQL with { characters that are not helping the situation.

When I run the query:

$query = "SELECT awaylineup FROM #__baseball_lineup";       
$db->setQuery($query);
$lineups = $db->loadObjectList();
foreach($lineups as $lineup){
    $players = explode(" ", $lineup->awaylineup);
}

The results come out as:

{"awaylineup0":"order":"0","player":"1","position":"2"},"awaylineup1":"order":"1","player":"3","position":"3"},"awaylineup2":{"order":"2","player":"2","position":"9"}}

I am trying to get:

[awaylineup0] => Array
(
[order] => 1
[player] => 1
[position] =>2
)
[awaylineup1] => Array
(
[order] => 1
[player] => 3
[position] =>3
)
[awaylineup2] => Array
(
[order] => 2
[player] => 2
[position] =>9
)

I don't know how many foreach's I have to run to get that, but also how to remove the {} characters in the string?

Update

> array ( 0 => NULL, 1 =>
> '{"awaylineup0":{"order":"0","player":"1","position":"2"},"awaylineup1":{"order":"1","player":"3","position":"3"},"awaylineup2":{"order":"2","player":"2","position":"9"}}',
> 2 =>
> '{"awaylineup0":{"order":"0","player":"1","position":"1"},"awaylineup1":{"order":"1","player":"2","position":"9"},"awaylineup2":{"order":"2","player":"3","position":"6"}}',
> )

Answer 1

It looks like you are mangling valid json data with explode() (the wrong tool for the job).

You just need to call json_decode() on each string. This can be done with array_map() or a classic foreach() (there are other ways as well).

$lineups = $db->loadColumn();
foreach ($lineups as &$lineup) {
    $lineup = json_decode($lineup, true);
}
var_export($lineups);

It is important that you eliminate null values from your result set by adding WHERE awaylineup IS NOT NULL to your query. json_decode() will bark in modern versions of PHP when it encounters null values.

Leaving the result set as an indexed array of arrays is important to avoid destroying/overwriting data while looping. The above should give you something like: (Demo)

array (
  0 => 
  array (
    'awaylineup0' => 
    array (
      'order' => '0',
      'player' => '1',
      'position' => '2',
    ),
    'awaylineup1' => 
    array (
      'order' => '1',
      'player' => '3',
      'position' => '3',
    ),
    'awaylineup2' => 
    array (
      'order' => '2',
      'player' => '2',
      'position' => '9',
    ),
  ),
  1 => 
  array (
    'awaylineup0' => 
    array (
      'order' => '0',
      'player' => '1',
      'position' => '1',
    ),
    'awaylineup1' => 
    array (
      'order' => '1',
      'player' => '2',
      'position' => '9',
    ),
    'awaylineup2' => 
    array (
      'order' => '2',
      'player' => '3',
      'position' => '6',
    ),
  ),
)