MySql PHP选择JSON值
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?
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.
What you have is a json encoded atrray. So use json_decode()
to decode it.
$arr1 = json_decode($result);
foreach ($arr1 as $row) {
echo $row->avatar->userId."-------".$row->avatar->currentHomeId."<br>" ;
}