1

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 :)

BSounder
  • 134
  • 9
  • why do you want your final array to look like that? just wondering – thedarklord47 Aug 05 '16 at 18:47
  • @thedarklord47 It's going to be used in a select dropdown to allow users to choose a part based on the part sku. I was initially going to just used a s.p to pull all part info and loop through those results javascript to update the select options but that was much too slow with 20k parts... – BSounder Aug 05 '16 at 18:53
  • There is quite possibly a better way to go about it (which i'd love to hear) but I want to avoid looping and preferably use a stored procedure to reduce the load on the web server – BSounder Aug 05 '16 at 18:57
  • are you by chance using PDO for your MySQL? – thedarklord47 Aug 05 '16 at 19:01

2 Answers2

3

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
1

your problem is that you need to turn the rows into an associative array keyed by the id. this will do that

function rowsToAssoc ($rows) {
    $assoc = Array();
    for ($rows as $r) {
        $assoc[$r['id']] = $r['sku'];
    }
    return $assoc;
}

then you can json_encode/decode as you wish.

if you are using PDO for your MySQL calls, check out this answer to avoid looping completely

Community
  • 1
  • 1
thedarklord47
  • 3,183
  • 3
  • 26
  • 55