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):

https://youtu.be/iIeJEBQYA10

If you are interested in diving deeper into Corona and Lua, check out our eBooks!