Saving JSON from web to SQL Database

[triscocal]

Please help!!!

I have a solution that uses JSON.decode and pulls data from a PHP page into Corona. Once in Corona the data is as below:

[“Player1”, “123”],[“Player2”, “123”]

I’d like to add this to an SQLite database within Corona. Is there an easy way to loop through the JSON data and insert into a database? The code that successfully populates " decodedData" is:

local function networkListener( event )

if ( event.isError ) then
print( “Network error!”)
else
myNewData = event.response
print ("From server: "…myNewData)
decodedData = (json.decode( myNewData))
end

end

network.request( “http://www.mypage.com/example.php”, “GET”, networkListener )

Where do I go from here? The table entries should be player and score. Thankyou for reading

[hammod-930]

_ ***edited to fix the code*** _

Hi, that should be easy 

use this after you get the data and loop it insert all the data you get:

[lua]

require(“sqlite3”)

local function setUpDataBase(dbName)

local path = system.pathForFile( dbName, system.DocumentsDirectory )

    local file = io.open(path, “r”)

    if ( file == nil ) then

    --copy the database file if doesn’t exist

    local pathSource    = system.pathForFile( dbName, system.ResourceDirectory )

    local fileSource    = io.open( pathSource, “r”)

    local contentsSource = fileSource:read("*a")

    local pathDest = system.pathForFile( dbName, system.DocumentsDirectory )

    local fileDest = io.open(pathDest, “w”)

    fileDest:write( contentsSource )

    io.close(fileSource)

    io.close(fileDest)

    end

    local gameDB = system.pathForFile(dbName, system.DocumentsDirectory)

    local dbNew = sqlite3.open(gameDB)

    return dbNew

end

db = setUpDataBase(“mydatabase.sqlite”)

function insertData(sqlTable ,dataToInsert)

local sql = “insert into “…sqlTable…”(name, category, rating) values (’”…dataToInsert…"’)"

db:exec(sql)

end

insertData(sqlTable ,dataToInsert)

[/lua]

note that the mysql data base should be within the project file

good luck

[hammod-930]

_ ***edited to fix the code*** _

Hi, that should be easy 

use this after you get the data and loop it insert all the data you get:

[lua]

require(“sqlite3”)

local function setUpDataBase(dbName)

local path = system.pathForFile( dbName, system.DocumentsDirectory )

    local file = io.open(path, “r”)

    if ( file == nil ) then

    --copy the database file if doesn’t exist

    local pathSource    = system.pathForFile( dbName, system.ResourceDirectory )

    local fileSource    = io.open( pathSource, “r”)

    local contentsSource = fileSource:read("*a")

    local pathDest = system.pathForFile( dbName, system.DocumentsDirectory )

    local fileDest = io.open(pathDest, “w”)

    fileDest:write( contentsSource )

    io.close(fileSource)

    io.close(fileDest)

    end

    local gameDB = system.pathForFile(dbName, system.DocumentsDirectory)

    local dbNew = sqlite3.open(gameDB)

    return dbNew

end

db = setUpDataBase(“mydatabase.sqlite”)

function insertData(sqlTable ,dataToInsert)

local sql = “insert into “…sqlTable…”(name, category, rating) values (’”…dataToInsert…"’)"

db:exec(sql)

end

insertData(sqlTable ,dataToInsert)

[/lua]

note that the mysql data base should be within the project file

good luck