BSounder BSounder - 4 months ago 34
JSON Question

Associative array in correct format from mysql stored procedure and php json_decode

I'm trying to use a mysql stored procedure to return a JSON string which can be turned into an associative array using PHP's json_decode function. I have a valid JSON string being returned from the s.p but it isn't in the correct form or

json_decode($skusJson, true);
doesn't like it for some reason. The PHP code for getting the s.p results and doing the json_decode is:

$partsQuery = $this->Orders->callSP('part_skus_dropdown1');
$skusJson = $partsQuery->fetchAll('assoc');
$partsQuery->closeCursor();
$skus = json_decode($skusJson[0]['json'], true);


The mysql s.p is:

CREATE DEFINER=`root`@`localhost` PROCEDURE `part_skus_dropdown1`()
BEGIN
SELECT
CONCAT('[',
GROUP_CONCAT(
CONCAT('{"id":',id),
CONCAT(',"sku":"',sku,'"}')
),
']')
AS json FROM parts where id < 25;
END


The resulting JSON string is:

[
{
"id" : 1,
"sku" : "1"
},
{
"id" : 3,
"sku" : "3"
},
{
"id" : 6,
"sku" : "6"
},
{
"id" : 7,
"sku" : "7"
},
{
"id" : 9,
"sku" : "9"
}
]


(The sku's will not always match the id's and are stored as strings rather than ints). The results of the json_decoding are:

[
(int) 0 => [
'id' => (int) 1,
'sku' => '1'
],
(int) 1 => [
'id' => (int) 3,
'sku' => '3'
],
(int) 2 => [
'id' => (int) 6,
'sku' => '6'
],
(int) 3 => [
'id' => (int) 7,
'sku' => '7'
]
]


Is there anyway that I can get the resulting array to be in the form of [ id => sku ]: (alt. sku format shown)

[
(int) 1 => '1',
(int) 3 => '3',
(int) 58 => '3-BOX100'
]


I'm stuck... I've been following the http://php.net/json_decode documentation and using http://jsonlint.com/ to check the results of the s.p but think its time to ask for help... thanks in advance :)

Answer

Change your procedure so that it returns a single JSON object, not an array, and then uses id as the key and sku as the value in each element

CREATE DEFINER=`root`@`localhost` PROCEDURE `part_skus_dropdown1`()
BEGIN
    SELECT 
        CONCAT('{',
            GROUP_CONCAT(
                CONCAT('"',id, '": "',sku,'"')
            ),
            '}')
    AS json FROM parts where id < 25;
END   

DEMO

Note that the keys of a JSON object are always strings, they can't be integers. But both PHP and Javascript will automatically convert between integers and strings when accecssing the array.