CODEWITHSUNDEEP
phpjsonstringsqlite
jamefane
jamefane

Reputation: 65

select json string in sqlite

i have a sqlite db which has a column called data in which is a json string. i want to retrieve certain values ​​from the json string now. how do I do that? I had tried something here but without success.

JSON string in SQL DB: 

{"lang": "VALUE1", "gamecd": "VALUE2", "makercd": "VALUE3", "unitcd": "VALUE4", "challenge": "VALUE5", "ipaddr": "VALUE6", "region": "VALUE7", "userid": "VALUE8", "gsbrcd": "VALUE9", "cfc": "VALUE10", "ingamesn": "VALUE11", "devtime": "VALUE12", "csnum": "VALUE13", "action": "VALUE14", "macadr": "VALUE15", "sdkver": "VALUE16"}

PHP code:

    <?php

        $dbfile = "DBNAME.db";


        $sqlite = new SQLite3($dbfile);
        $results = $sqlite->query("SELECT data FROM nas_logins");

        while ($jsonData = $results->fetchArray()) {

            $json = json_decode($jsonData, true);

        }


        echo "lang: " . $json['lang'] . "<br>";
        echo "gamecd: " . $json['gamecd'] . "<br>";
        echo "makercd: " . $json['makercd'] . "<br>";
        echo "unitcd: " . $json['unitcd'] . "<br>";
        echo "challenge: " . $json['challenge'] . "<br>";
        echo "ipaddr: " . $json['ipaddr'] . "<br>";
        echo "region: " . $json['region'] . "<br>";
        echo "userid: " . $json['userid'] . "<br>";
        echo "gsbrcd: " . $json['gsbrcd'] . "<br>";
        echo "cfc: " . $json['cfc'] . "<br>";
        echo "ingamesn: " . $json['ingamesn'] . "<br>";
        echo "devtime: " . $json['devtime'] . "<br>";
        echo "csnum: " . $json['csnum'] . "<br>";
        echo "action: " . $json['action'] . "<br>";
        echo "macadr: " . $json['macadr'] . "<br>";
        echo "sdkver: " . $json['sdkver'] . "<br>";

    ?>

Upvotes: 0

Views: 391

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

looking to your data you should access to data column name eg:

while ($jsonData = $results->fetchArray()) {

        $json = json_decode($jsonData['data'], true);

    }

Upvotes: 1

Related Questions