MySql PHP选择JSON值

Question

I have this SQL query.

$sql = "SELECT playerjson FROM `clans` WHERE playercount > ? AND level > ? AND score > ?";
$selectstmt = $con->prepare($sql);
$selectstmt->bind_param('iii',$playercountvar,$levelvar,$scorevar);
$selectstmt->execute(); //execute select statement 
$result = $selectstmt->get_result(); //get select statement results

playerjson is a large JSON Array.

[
    {
        "avatar":{
            "userId":253404325847,
            "currentHomeId":253404325847,
            "userName":"enal",
            "role":"Member",
            "level":62,
            "league":8,
            "trophies":1707,
            "donatedTroops":0,
            "receivedTroops":0,
            "clanRank":1,
            "lastClanRank":2,
            "inWar":1
        }
    },
    {
        "avatar":{
            "userId":158925253577,
            "currentHomeId":158925253577,
            "userName":"Valen kamja",
            "role":"Leader",
            "level":54,
            "league":8,
            "trophies":1693,
            "donatedTroops":1054,
            "receivedTroops":2131,
            "clanRank":2,
            "lastClanRank":3,
            "inWar":1
        }
    },
    {
        "avatar":{
            "userId":296357929514,
            "currentHomeId":296357929514,
            "userName":"\u0645\u064c\u0648\u0646\u0633\u062a\u064d\u0631502",
            "role":"Member",
            "level":59,
            "league":7,
            "trophies":1568,
            "donatedTroops":0,
            "receivedTroops":0,
            "clanRank":3,
            "lastClanRank":0,
            "inWar":1
        }
    },
    {
        "avatar":{
            "userId":283468864924,
            "currentHomeId":283468864924,
            "userName":"tolzz",
            "role":"Co-Leader",
            "level":64,
            "league":7,
            "trophies":1312,
            "donatedTroops":34,
            "receivedTroops":456,
            "clanRank":4,
            "lastClanRank":4,
            "inWar":1
        }
    },
    {
        "avatar":{
            "userId":257703167804,
            "currentHomeId":257703167804,
            "userName":"hailery",
            "role":"Co-Leader",
            "level":58,
            "league":6,
            "trophies":1219,
            "donatedTroops":21,
            "receivedTroops":404,
            "clanRank":5,
            "lastClanRank":5,
            "inWar":1
        }
    },
    {
        "avatar":{
            "userId":210456177319,
            "currentHomeId":210456177319,
            "userName":"chey lie",
            "role":"Co-Leader",
            "level":79,
            "league":0,
            "trophies":1101,
            "donatedTroops":0,
            "receivedTroops":0,
            "clanRank":6,
            "lastClanRank":6,
            "inWar":0
        }
    }
]

What I want to do is just store the userid and currenthomeid and store them in an array which will be in a parent array...

Because from that I will need to get the child array and pass those one by one as parameters in a url. explode wouldn't work with this would it?

How would I go about achieving this? Also I need a way to improve the SQL Statement so that I don't retrieve the entire JSON like that as it could take longer?

Answer 1

Decode the result string and iterate over it as stdClasses :

$json = json_decode($result);
$parent = array();
foreach($json as $item) {
    $parent[] = array('userId' => $item->avatar->userId, 'currentHomeId' => $item->avatar->currentHomeId);
}

echo '<pre>';
print_r($parent);
echo '</pre>';

will produce :

Array
(
    [0] => Array
        (
            [user] => 253404325847
            [currentHomeId] => 253404325847
        )

    [1] => Array
        (
            [user] => 158925253577
            [currentHomeId] => 158925253577
        )

etc. To pass $parent as a URL string you could simply use json_encode to stringify it :

$url = '?values='.json_encode($parent);

gives ?values=[{"user":253404325847,"currentHomeId":253404325847},{"user":158925253577," etc...

This will automatically be escaped, you can read the array back in javascript clientside with

var value = window.location.href.split('?values=')[1],
    array = JSON.parse(unescape(value));

console.log(array);

you now have the array as JSON objects clientside. There is many ways you could do this. This was just a quick suggesion.