Posted on by

Today’s tutorial comes to you courtesy of Brian Burton, Corona Ambassador and author of “Mobile App Development with Corona: Getting Started.” This tutorial was originally published on Burton’s Media Group.

A question that I am regularly asked is how to get data from a remote server (usually stored in a database) into 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, etc. There are so many variations and possibilities, the task would likely never be finished. So, rather than leave this idle for another couple of months, here is a tutorial on at least one approach to get data from a remote server into a mobile app.

The Process

I went with a standard 3-tier architecture to keep things simple.

Remote Database

For 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 use 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="Password";               //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 with your table name 
$result = mysql_query($sql);
$json = array(); 
if(mysql_num_rows($result)){while($row=mysql_fetch_row($result)){ 
$json[]=$row; 
} 
} 
mysql_close($db_name); 
echo json_encode($json); 
?>

Client

This is the Corona code that I use to pull the information from the remote server, decode the JSON formatted information, save it to a local SQLite file then load the contents of the SQLite file onto 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.

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, 20, 30 * row.id, native.systemFont, 24 )
    t:setTextColor( 255,255,255 )
  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))
    SaveData()
  end

end

network.request( "http://www.BurtonsMediaGroup.com/myMovies.php", "GET", networkListener )

And that wraps up the basics of the tutorial! For a detailed video explanation, check out the YouTube video:


Posted by . Thanks for reading...

28 Responses to “Downloading JSON Data From MySQL”

  1. J. A. Whye

    Nice tutorial!

    Something you mentioned right at the end is overlooked by a lot of people — and that’s using this technique as a way to update your app without submitting a “real” update for review.

    Most people (myself included) usually think of grabbing data like this as a way to deal with dynamic data — stuff that’s changing a lot. But think about an app for a pizza place — even if they add new pizzas to the menu just a couple times a year, a quick check when the app launches would allow you to pull that new pizza into place as soon as it’s available. No adding it to the code, doing a new build, submitting for review, etc.

    Jay

    Reply
  2. Roni

    As you are using a simple HTTP GET it can be access by any browser or http client.
    How would you secure you service? you wouldn’t like your database service to be attacked that easily.
    The client support is VERY limited around security…

    Reply
  3. james

    it really is too bad that it can’t handle propper authentication like SOAP or REST. I was able to get Titanium to use an authenticated API via soap.js. This is hugely important for a new educational game platform I’m working on. Please at least let us know if you’re working on this feature.

    Reply
  4. Cedric McMillan Junior

    @Roni – I guess there could be a Secret Key that is checked in the php before access to the remote database to make it more secure.

    @Inna – Thanks for the code. I’m wondering is the reason that you are importing the data into an SQLite table just in case you want to do some local sorting etc on it, rather than, say import the data directly into a basic table array?

    I’m not sure what the performance overheads are for setting up the SQLite method.
    (just wondering in case you could also get away with the other method i mentioned without using SQLite at all).

    I’m looking at using Corona for my next app and this is an area I’m very interested in, so thanks for the article!

    Reply
    • Roni

      Secret keys are VERY basic, using simple HTTP tools you can see the HTTP requests, with the ‘Secret’ key .
      This is not different from HTTP basic authentication that sends the user and password joined into base 64 on the soap header.

      Only the use of HTTPS or any kind of encryption would make it better.
      As far as I know both are not easy to do with lua (and so in Corona).

      Reply
      • Jason

        Hi Roni,

        The easiest way I have found to use HTTPS is to have the SSL enabled on the server with Basic authorisation (user name/password) for the page your hitting with your GET or POST and put the credentials in the header for the POST using the parameters in your corona client.

        The only issue I have come across is to be careful when setting up your virtualised hosts on 443 on your server. If you use apache, remember to correctly setup SSL.conf as well as httpd.conf as you may find you requests work in the simulator and on IOS but not Android.

        Cheers,

        J

        Reply
    • Roni

      Don’t get me wrong, I think you made a really nice code, and I do appreciate it is just a proof of concept,
      but it is touching the exact problem I have with Corona, as I find it much better for games and less for Apps that use services.

      You cannot even use text inputs on the simulator, so developing ‘IT’ this way is very hard…
      (compare to the real ease of developing a game using Corona)

      Reply
  5. RedEldorado

    Brian,

    Excellent tutorial. Great to see how simple it is to interface with a MySQL database on a remote server. You glued a lot of detached programming pieces together in 15 minutes! Thanks!

    Reply
  6. J. A. Whye

    @Cedric – not sure what Brian’s reason was for going to a database first instead of straight to a table, but I can tell you why I did that in my last app.

    I wanted the app to be useful on devices where there was no “always on” connection — so when there is a connection I grab from the online data and update the app’s database. When I display the data in the app I always pull from the database. If there’s a connection, the data shown is fresh. If there is no connection, at least there’s still data for them to work with.

    As far as security goes, there’s nothing stopping you from passing the PHP script a username/password. Here’s the URL my app pulls from: http://akleads.com/members/latest.php — but without a username and password passed in it only returns 5 records (by design, for trial users).

    I *don’t* deal with financial details of any kind so don’t worry about it too much, but basic security can be handled fairly easily.

    Reply
  7. Mario Roberti

    Spectacular! Great resource, and extremely informative. I appreciate the time and effort put into it! Can’t wait to implement it!

    Best regards,
    Mario

    Reply
  8. james mclain

    Newbie Question:

    Lets say there is an app that returns results of restaurant. You want to let the user search data say from 2 field ,”location, type”.

    Could you just search a JSON database through online and return those results?

    Or it would have to be converted to a SQLlite db first?

    So all db that users can search or call up have to be local on the device using Corona?

    Is the point of the JSON to be able to call data but not search through it by fields?

    Appreciate any help. I’m trying to write a basic app and would like to know the best app that users can input data from a PC with a web form, and then users can search and narrow down that data from there phones.

    Thank you!!!

    Reply
  9. Tanggu

    Hi I found some difficulties to execute your sample code above.
    I set up website as you mentioned and got the positive replay from server.

    It is OK to review the data from server like your code in SDK code page(line nbr 61)
    ==> print (“From server: “..myNewData)
    I got the data with this comment but …

    if I check with the comment print(movie) ==> line 22
    I got only “nil”
    I means that jason.decode(myNewData) doesn’t work.
    How should I solve this problem?
    Thanks for your advice.

    Reply
  10. Tanggu

    Hi I found some difficulties to execute your sample code above.
    I set up website as you mentioned and got the positive replay from server.

    It is OK to review the data from server like your code in SDK code page(line nbr 61)
    ==> print (“From server: “..myNewData)
    I got the data with this comment but …

    if I check with the comment print(movie) ==> line 22
    I got only “nil”
    It means that jason.decode(myNewData) doesn’t work.
    How should I solve this problem?
    Thanks for your advice.

    Reply
  11. osama

    Hello am new in android development will i want to make an android “Client server App” that let student enter his index and then retrieve his semester result

    i attached .docx file that explaine what i want exactly. am asking if there is someone can help me do this one .

    thanx advance

    Reply
  12. Aad

    Hi,

    Thanks for the info.
    I have copied the code ( php and lua) and the database exact.

    It works not as expected, the echo string from my server is:

    [["1","Fiddlers on the Roof","1971"],["2","The Matrix","1999"],["3","Batman","1987"],["4","stargate","2012"],["5","James Bond","2013"]]

    this differs from the string shown in the tutorial:
    { movie:[“1″, “Fi …..

    The database is exact the same as in the example and the php script is also the same .

    So the json function in my app does not work.

    Please can you give advice,

    thanks

    Reply
  13. cburzon

    Hi,
    Newbie Question:

    What happens if you are using more than one table on MySql Server ? you must crete more than PHP file to refer to each table ?

    thanks

    Reply
    • Rob Miracle

      That’s a tough question to answer because a lot “depends” on what you’re doing. You could have a database with multiple tables and a single PHP script that reads multiple tables and produces one output, or depending on your needs, you might have one table per one PHP script. Without knowing what you want to do, it will be hard to provide specific advice.

      Reply
  14. Ryan Hunter

    I am having the exact same issue where my JSON output looks like

    [["1","Fiddlers on the Roof","1971"],[“2″,”The …
    and not
    { movie:[“1″, “Fi …..

    I have a MySQL database with one table in it with 2 rows in that table.
    The PHP is an exact copy of your example.

    The script works if I change line 21 to “local index = counter”, then I get only the first line of results. If I change the counter in line 20 to 2, I get only the second row of results. I think it is an issue with the way the JSON is parsing, how it’s not putting that leading “movie” indexing.

    Reply
  15. Ryan Hunter

    Ok, I fixed my problem. Since it treats all my results as one big array entry, I changed line 30 to: index = counter
    and line 21 to: local index = counter
    and it fixed it.

    Reply
  16. Richie G

    This is a great tutorial… I was looking into this a while back and parked it due to work commitments… but its back on the radar again….

    Now Im no SQL or PHP expert … so my question is… how easy is it, or is it possible, to pass a variable or table to the PHP script to look up in the database.

    For example, if my database has 1000 rows… each with a unique StudentID…. and I want to lookup the infomation for 1 specific StudentID or multiple StudentIDs.. how would I do that?

    Cheers
    R

    Reply
  17. Olaf

    Hello,
    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?
    Cheers,
    Olaf

    Reply

Leave a Reply

  • (Will Not Be Published)