mysql | Burtons Media Group

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!