MySql PHP Select JSON value

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?

I will write you some code... Give me a second :-)

Dennis Weidmann
– Dennis Weidmann

2015-10-26 17:24:05 +00:00
Commented Oct 26, 2015 at 17:24 — Dennis Weidmann
– Dennis Weidmann, Commented Oct 26, 2015 at 17:24

davidkonrad · Accepted Answer · 2015-10-26 17:54:34Z

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.

edited Oct 26, 2015 at 17:54

answered Oct 26, 2015 at 17:32

davidkonrad

85.7k17 gold badges211 silver badges273 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user4942382 Over a year ago

How can I then use these to pass them through parameters in a URL string? Let's say: example.com?player=FirstBitOfArray&home=SecondBitOfArray

user4942382 Over a year ago

I added another foreach($parent as $ids) and plan on doing it all in there?

davidkonrad Over a year ago

@ShivamPaw, no - just in one line. You have the array, json_encode turns it "back" to a string you can pass over ...

davidkonrad Over a year ago

@hanshenrik, yes but not nessecary in this case; the array only hold numbers and letters, curly braces and brackets are perfectly legal.

Niranjan N Raju · Accepted Answer · 2015-10-26 17:37:11Z

0

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>" ;
    }

edited Oct 26, 2015 at 17:37

answered Oct 26, 2015 at 17:31

Niranjan N Raju

12k4 gold badges24 silver badges41 bronze badges

8 Comments

user4942382 Over a year ago

I want to try and avoid having to fetch the entire JSON string as it can take longer no?

Niranjan N Raju Over a year ago

You dont want all data??

user4942382 Over a year ago

I only need the userId and currentHomeId from the json array

Niranjan N Raju Over a year ago

you need all user id and all currenthome id??

user4942382 Over a year ago

Yes, I need all of them but the rest of the stuff in the JSON isn't needed

|

Collectives™ on Stack Overflow

MySql PHP Select JSON value

2 Answers 2

4 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related