How to Download JSON data from MySQL Using Corona SDK
A question that I am regularly ask is how to get data from a remote server (usually stored in a database) to a mobile app.
I wrestled with how best to present this information, whether it should be a series, how many different methods I should include, so many variations that the project was never going to be finished. SO, I rather than have this how to sit for another couple of months, here is a tutorial on at least one approach for getting data from a remote server to a mobile app.
The Process
I went with a standard 3-tier architecture for this application to keep things simple.
Remote Database: In this tutorial, I created a MySQL database on a remote server. It has just one table with 3 fields: id, movie title, and the year the movie was released.
Logic or Middleware: I used PHP for the middleware to pull the data from the MySQL database and encode it into the JSON format before returning it to the mobile device:
<?php
$host="localhost"; //replace with your hostname
$username="database_example"; //replace with your username
$password="myPassword"; //replace with your password
$db_name="Mymovies"; //replace with your database
$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "SELECT * FROM BestMovies"; //replace BestMovies with your table name
$result = mysql_query($sql);
$json = array();
$count=0;
if(mysql_num_rows($result)){
while($row=mysql_fetch_row($result)){
$count = $count+1;
$json[$count]=$row;
}
}
mysql_close($db_name);
echo json_encode($json);
?>If you want to change the number of results, change the SQL statement:
$sql = “SELECT * FROM BestMovies”;
to
$sql = “SELECT * FROM BestMovies LIMIT 1”;
which will return the first resulting row.
Client: This is the Corona SDK code that I used to pull the information from the remote server, decode the JSON formatted information, save it to a local SQLite file then load from the SQLite file to the screen. Note that the database is persistent, so if you run it multiple times, the remote database call will continue to add new rows of data to the SQLite database.
-- Project: Corona json
-- Description: Client to receive data from a MySQL database formatted as json data by php to a local SQLite database.
-- Copyright 2020 Brian Burton. All Rights Reserved.
local sqlite3 = require ("sqlite3")
local myNewData
local json = require ("json")
local decodedData
local SaveData = function ()
--save new data to a sqlite file
-- open SQLite database, if it doesn't exist, create database
local path = system.pathForFile("movies.sqlite", system.DocumentsDirectory)
db = sqlite3.open( path )
print(path)
-- setup the table if it doesn't exist
local tablesetup = "CREATE TABLE IF NOT EXISTS mymovies (id INTEGER PRIMARY KEY, movie, year);"
db:exec( tablesetup )
print(tablesetup)
-- save data to database
local counter = 1
local index = "movie"..counter
local movie = decodedData[index]
print(movie)
while (movie ~=nil) do
local tablefill ="INSERT INTO mymovies VALUES (NULL,'" .. movie[2] .. "','" .. movie[3] .."');"
print(tablefill)
db:exec( tablefill )
counter=counter+1
index = "movie"..counter
movie = decodedData[index]
end
-- Everything is saved to SQLite database; close database
db:close()
--Load database contents to screen
-- open database
local path = system.pathForFile("movies.sqlite", system.DocumentsDirectory)
db = sqlite3.open( path )
print(path)
--print all the table contents
local sql = "SELECT * FROM mymovies"
for row in db:nrows(sql) do
local text = row.movie.." "..row.year
local t = display.newText(text, display.contentWidth/2, 30 * row.id, native.systemFont, 24)
t:setFillColor(1,1,1)
end
db:close()
end
local function networkListener( event )
if ( event.isError ) then
print( "Network error!")
else
myNewData = event.response
print ("From server: "..myNewData)
decodedData = (json.decode( myNewData))
--local myText= display.newText(decodedData.movie1[2], 50,50, nil,24)
--myText:setTextColor(1,1,1)
SaveData()
end
end
network.request( "http://www.BurtonsMediaGroup.com/myMovies.php", "GET", networkListener )
For a full video explanation, check out the YouTube video (15 minutes in length):
If you are interested in diving deeper into Corona and Lua, check out our eBooks!
43 Comments
David,
Perfect timing on this tutorial ! I was just going to sit down and add this capability to my app that is under development.
Thanks for giving me a headstart
Ken
Not sure who David is, but happy to help!-)
Brian,
so sorry — that’s what happens when I multitask – i.e. read your post while in a meeting with “David” 🙂
Ken
No problem Ken, I get in trouble for multitasking at home all the time!-)
Great three tier tutorial Dr. Burton! But you might want to upgrade from mysql_connect to the new MySQLI extension. It is completely OOP and functions similarly to the older mysql code, but it has enough new features to make the jump worth it. And thank you for using JSON and not XML, and don’t understand why some people think you have to send extra tag data over the network to retrieve data.
Thank you Jacob, I’ll integrate the MySQL update into my next update!
Is it also possible to update data in the sql database on the server from the app? So the other way around?
Oh i forgot to say: I love this post! I can really use it! so thanks for that. 🙂
@John, Yes, it is just a matter of setting up the php file to handle receiving the data and save it to your remote database.
Glad you found the tutorial useful!
Hi Dr. Burton,
Thank you very much for this post! However after trying it out myself it seems that im encountering an error. With a custom database and with a one on one copy of what you created above.
I keep getting Runtime error on line 30 with message attmept to index upvalue ‘decodedData’
stack straceback
[path to project] in fucntion ‘SaveData’
No mather what i try i can’t seem to fix this. Could you please be so kind to assist?
Regards,
Mike Sternfeld
Hi Dr.Burton,
Upon more research it seems the php page generates diffrent results then your page does.
Yours:
{“movie1”:[“1″,”Fiddler on the Roof”,”1971″],”movie2″:[“2″,”The Matrix”,”1999″],”movie3″:[“3″,”The Princess Bride”,”1987″],”movie4″:[“4″,”Monty Python and the Holy Grail”,”1975″]}
Mine:
[[“1″,”Fiddler on the Roof”,”1971″],[“2″,”The Matrix”,”1999″],[“3″,”The Princess Bride”,”1987″],[“4″,”Monty Python and the Holy Grail”,”1975″]]
Why is it that using your PHP script i get diffrent results then your script gives?
Regards,
Mike Sternfeld
Mike,
I had done a minor update to the php script when I was showing it to my class and forgot to update it in the post. I have updated the line so that the results should be the same. Thank you for asking about this!
Thanks again Dr. now it works perfect both in my own script as in the test copy i got from here!
Really greatfull you’ve put this up, couldn’t find anyone to bother and explain this properly!
Thanks again!
I was wondering if anyone knows what would cause this error and my JSON data to come out like this?
Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in /home/raceday/public_html/testdatabase/test123.php on line 15
[[“1″,”patrick”,”patrick is a good guy”],[“2″,”tom”,”tom is well, just tom”]]
when I believe to be valid JSON it should be encapsulated with “{}”
Which it is in the example.
Thank you!
Hello!
I’m learning corona and need acces to information from a DB (mariaDB) and this tutorial describes just what I need. I have to say I’m new to this, JSON and getting information from a remote db. My question here is… What is Logic or Middleware and what do I do with the php code. We made a website that accessed a db before and we used php on the website but now I’m using corona sdk to get information from a db. Sorry if this is a stupid question.
The logic or middleware is the PHP code (the first block of code above). It will be on your server and does all of the work of accessing the database for you. If you are already accessing the db from a website, the whole process will be very similar for your mobile app. Think of the mobile app as your webpage: It is going to call the php file, which accesses the db.
It looks like the problem is in your test123.php file, not the creation of the JSON data. Without seeing your php file, I’m afraid I can’t be of much help.
Hello Dr. Burton,
Thank you very much for this post! I have a problem, I can’t do a query when the table of database have special characters like á, é . Excuse me for my bad English.
@Geovanny, I think the easiest solution would be to use a wild card character in place of the special characters.
Hi
Thank you for this wonderfull tutorial… however, I am having a small problem… I am not getting any data returned from the php script.
I have a very simple table called scores with 2 records.. and am just doing a SELECT * FROM scores
But for some reason myNewData = event.response is empty
@Richard:
Are you getting a response from the server if you access it from a webpage? You should see the data if you cut and paste the URL information into your browser.
Muchas Gracias por su sugerencia Dr. Burton, fue de mucha utilidad. Saludos
Thanks Brian for a great article!
I have a question, though. I don’t have much variable data to send from server to the device.
Would it be wrong, if I just place a json file on my server an ask app to download it istead of making a database, then encoding to json file and afterwards sending to the device?
Is it a wrong approach? It seems that two lines of txt json file would be easier to download than to process database through php file which is also on server. Or am I wrong?
Let me know your opinion please. Thanks!,
Olaf
I’m not sure I understand what you are trying to do. But yes, you could store a file with JSON data on a remote server or even on the local device and import it.
Generally the reason that you would want to connect to a remote server is either for verification/login or to retrieve specific sets of data, which is why the demo was about using a database.
OK, thanks! First I thought that maybe database could be accessed by more simultanous users or sth like that. But to access a database you also have to lookup through a php file which weighs more than my json file. So if it’s no difference (in my case) I’ll stick to the json 🙂
Hello Brian,
Thanks for the video, it took me a little bit to figure out all the steps, but things are almost where I want them to be. My question is that my decodedData variable is coming back nil. I can’t figure out why that might be happening. any ideas???
@Kenny,
Without seeing your code, the things I would check (in order): Are you getting data from the server? How are you checking your decoded data (remember, it is a table)?
I think I figured it out. But now I’m getting:
“main.lua:22: attempt to call local ‘name’ (a nil value)
stack traceback: [C]: in function ‘name'”.
I think something is happening with the decodeData variable where sqlite3 is not creating the table, but I’m not sure. I’m not even sure if I’m describing it correctly, but hopefully the code below will help you figure out what I’m doing wrong. Thanks!!!
local sqlite3 = require “sqlite3”
local myNewData
local json = require (“json”)
local decodedData
local userName = “Stevie Wonder”
local SaveData = function()
local path = system.pathForFile(“User.db”, system.DocumentsDirectory)
db = sqlite3.open( path )
print(path)
local tablesetup = [[CREATE TABLE IF NOT EXISTS kenny (id INTEGER PRIMARY KEY autoincrement, firstName, lastName);]]
db:exec( tablesetup )
print(“table is setup”)
local counter = 1
local index = “Name”..counter
print(“index is: “..index)
local name = decodedData[index]
print (name)
while (name ~=nil) do
print(“Inserting Data”)
local tablefill = [[INSERT INTO kenny VALUES (NULL, ‘” ..name[2].. “‘, ‘” ..name[3] ..”‘);]]
print(tablefill)
print(“Data Inserted”)
db:exec(tablefill)
counter=counter+1
index = “user”..counter
user = decodedData[index]
end
db:close()
local path = system.pathForFile(“User.db”, system.DocumentsDirectory)
db = sqlite3.open( path )
print(path)
local sql = “SELECT * FROM kenny”
for row in db:nrows(sql) do
local text = row.firstName..” “..lastName
local t = display.newText(text, 20, 30 * row.id, native.systemFot, 24)
t:setTextColor(255,255,255)
end
db:close()
end
local function networkListener( event )
if ( event.isError ) then
print(“Network Error!!!”)
else
print (“Network Found”)
print (“From Server: “..event.response)
decodedData = (json.decode( event.response ))
print (decodedData)
print (“hello”)
SaveData()
end
end
I apologize Dr. Burton are new programmer in lua, I was struck by this lua script, I tried it and it works fine if I use its address (http://www.BurtonsMediaGroup.com/myMovies.php) but if I use my php server is working fine but this error in the lua script. (lua: 29: attempt to concatenate field ‘?’ (a nil value) if only rates and return on your server and works fine. ask her help to complete my first app! Thank you.
Mik3langelo
Are you using null values in the database? Try setting all of the fields so that null is not allowed.
The most common reason for the error is if there was a null field. Try setting the remote database table so that it doesn’t allow null.
I wanted to thank Dr. Burton kind for the help they gave me, I solved my problem in an instant thanks to her. Congratulations and I’m sorry for my english definitely not adequate.
mik3langelo
verona Italy
Thanks for the tutorial. Good and informative.
Some minor issue about the php code: mysql_close($db_name);
I get error Warning: mysql_close() expects parameter 1 to be resource.
Then I change to: mysql_close($con);
Issue solved.
Just about to post the same comment. Took me a while to find it, not sure how that slipped through.
I wonder if something has changed, I just get a blank screen on the simulator. I have the PHP spitting out JSON perfectly, but black corona screen with errors about upvalue decodedData being nil and a problem in SaveData
Hi Dr.Burton
Thanks for the tuts, i try and its work, just curious about 1 thing
how to display movie name and the year in 2 line
right now it will look like
THE MATRIX 1999
how to make like this
THE MATRIX
1999
thank you 🙂
If you add “\n” or “\r\n” between THE MATRIX and 1999 it will force a linefeed.
Thank you for the reply
Btw is that will work on device, i mean i read some articles few days ago (forgot the url, but its on corona site) using \n only work on simulator is that true? (sorry dont have device to test yet :p )
sir what is the problem of this?
attemt to index global ‘json’ (a nil value)
Usually a ‘index global’ error means that you have not created the variable with the local variable.
Dr. Burton i have a question..
how can i display json string from the php into corona simulator screen. ..?
for like example…
the json or php output..
{“player1”:[“1″,”Ian”,”200″],”player2″:[“2″,”Matrix”,”199″],”player3″:[“3”,”Princess “,”198”]}
——————
i want to display the playername and playerscore in my corona screen..
like this
——————
ian 200
matrix 199
princess 198
—————–
how can i do that? 🙁
Dr Burton,
Do you have any sample code that does both the reading and the update? I’ve been successful (with your code as a guide) getting the read to work, but I’m having a hard time getting the database updating to work.
Any help greatly appreciated!
[…] tutorial comes to you courtesy of Brian Burton of Burton’s Media Group. Brian is a Corona Ambassador and author of “Mobile App Development with Corona: Getting […]