43

How to Download JSON data from MySQL Using Corona SDK

Posted by Dr. Burton on November 15, 2012 in Android, Corona, iPad, iPhone, Kindle Fire, Mobile |

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 thinks 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=”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();
$count=0;
if(mysql_num_rows($result)){
while($row=mysql_fetch_row($result)) {
$count = $count+1;
$json["movie".$count]=$row;
}
}
mysql_close($db_name);
echo json_encode($json);
?>
									

 

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.
--
-- Version: 1.0
-- Managed with http://CoronaProjectManager.com
--
-- Copyright 2012 Brian Burton. All Rights Reserved.
---- cpmgen main.lua
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 )
									

For a full video explanation, check out the YouTube video (15 minutes in length):

YouTube responded to TubePress with an HTTP 410 - No longer available

Tags: , , , , , , , , ,

43 Comments

  • Ken Cardita says:

    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

  • Ken Cardita says:

    Brian,

    so sorry — that’s what happens when I multitask – i.e. read your post while in a meeting with “David” 🙂

    Ken

  • 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.

  • JohnDortwegt says:

    Is it also possible to update data in the sql database on the server from the app? So the other way around?

    • Dr. Burton says:

      @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!

      • Vince says:

        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!

  • JohnDorwegt says:

    Oh i forgot to say: I love this post! I can really use it! so thanks for that. 🙂

  • Mike says:

    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

    • Mike says:

      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

      • Dr. Burton says:

        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!

      • Mike says:

        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!

  • patrick says:

    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!

  • Dave says:

    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.

    • Dr. Burton says:

      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.

  • Geovanny says:

    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.

  • Richard says:

    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

  • Dr. Burton says:

    @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.

  • Geovanny says:

    Muchas Gracias por su sugerencia Dr. Burton, fue de mucha utilidad. Saludos

  • Olaf says:

    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

    • Dr. Burton says:

      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.

      • Olaf says:

        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 🙂

  • Kenny says:

    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???

  • Dr. Burton says:

    @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)?

    • Kenny says:

      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

      • Dr. Burton says:

        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.

  • mik3langelo says:

    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

  • mik3langelo says:

    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

  • kebaboom says:

    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.

  • Rhino says:

    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

  • johny says:

    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 🙂

  • ian says:

    sir what is the problem of this?

    attemt to index global ‘json’ (a nil value)

  • ian says:

    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? 🙁

  • […] 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 […]

Leave a Reply

Copyright © 2010-2017 Burtons Media Group All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from BuyNowShop.com.