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:
[codebox 1]

 

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.

[codebox 2]

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

[tubepress video =iIeJEBQYA10]

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

  • Dr. Burton says:

    Not sure who David is, but happy to help!-)

  • 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

  • Dr. Burton says:

    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.

  • Dr. Burton says:

    Thank you Jacob, I’ll integrate the MySQL update into my next update!

  • JohnDortwegt says:

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

  • JohnDorwegt says:

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

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

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

  • Dr. Burton says:

    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.

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

  • Dr. Burton says:

    @Geovanny, I think the easiest solution would be to use a wild card character in place of the special characters.

  • 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

  • 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

  • Dr. Burton says:

    Are you using null values in the database? Try setting all of the fields so that null is not allowed.

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

    Just about to post the same comment. Took me a while to find it, not sure how that slipped through.

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

  • Dr. Burton says:

    If you add “\n” or “\r\n” between THE MATRIX and 1999 it will force a linefeed.

  • johny says:

    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 )

  • ian says:

    sir what is the problem of this?

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

  • Dr. Burton says:

    Usually a ‘index global’ error means that you have not created the variable with the local variable.

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

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

  • Downloading JSON Data From MySQL | Corona Labs says:

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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