select json string in sqlite

Question

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

    ?>

ScaisEdge · Accepted Answer · 2018-03-18 14:20:50Z

1

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

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

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

    }

answered Mar 18, 2018 at 14:20

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Collectives™ on Stack Overflow

select json string in sqlite

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related