Looking for a good tutorial. MySQL connection

Solar2D General Questions/Discussion
#1

Hi all, I’m looking at creating a content managed app with corona… My requirements are pretty simple. At the moment, I want to post some values (with a web form) to a MySQL database, for arguement’s sake, let’s say I have a form-based drop-down, with numerical values; ‘2’, ‘4’, ‘6’ and ‘8’. When I submit that form, the value goes into an already established table row.

I would first like to connect to the database, I’ve already found this which is useful: http://coronalabs.com/blog/2012/11/20/how-to-download-json-data-from-mysql-using-corona-sdk/

I’m currently trying (hard) to find some instructional material, that shows me how I can make my Corona app interact with the database values, for example I would like my app to react to the specific data; - If _database_row_value is => 2 then image.alpha = 0.5 (just an example), but I don’t know how to refer to specific table values.

Any pointers would be really appreciated.

Thanks

#2

Are you able to get the database values in to Corona or are you having difficulty getting the data out of MySQL?

Also check this out:  http://omnigeek.robmiracle.com/2012/04/15/using-corona-sdk-with-rest-api-services/

Rob

#3

Thanks Rob… My problem is purely syntax. How to include SQL variables into LUA. I can GET the data, parse it and access it, there just seems to be a shortage of resources that show me how I can interact with the values.

#4

In the blog post I referred to, the MySQL query comes back to PHP as an Associative Array.  This is very similar in nature to a Lua table, i.e. key-value pairs.  However you have to transform the data into something that can be passed in an HTTP request, which generally requires that data being converted into a web-safe string.  The best way to do this is to use JSON (JavaScript Object Notation).  Both Corona SDK and PHP support API calls to encode tables/arrays into a JSON string and then decode them back into their native table structure.

The basic flow is this:

Corona SDK makes a network.request() to the PHP script on the server.

The PHP script makes the database query, gets back the associative array.

The PHP script calls json_encode() on the array which returns a string:    $myJSONstring = json_encode( $myReturnedDataBaseArray );

The PHP script uses it’s “echo” statement to print the string out to the Web Server’s output stream.

Corona SDK’s network.request() recogonizes it got data on that stream, reads it in and calls your listener function that you defined in the network.request() call.  The data returned by the script will be in the “event.response” variable.

In the listener script convert the event.response, which should be the encoded JSON string and turn it back into a Lua table:

local json = require( “json” )

local myDatabaseTable

…

local function handleDatabaseQuery( event )

      if event.phase == “ended” then

            myDatabaseTable = json.decode( event.response )

      end

end

network.request(“http://path.to/yourscript.php”, “GET”, handleDatabaseQuery)

or something similar to that.  Of course there is more to the handleDatabaseQuery function. You need to check for errors and so on, but that’s the basic flow.   At the point the handleDatabaseQuery function is called, your table (that I named myDatabaseTable) will have the results of the query from the PHP script.  If your query was something like:  SELECT name, address, age FROM person where ID = 12, then the result should be a lua table with members, name, address and age:

print( myDatabaseTable.name, myDatabaseTable.address, myDatabaseTable.age)

or something like that.

Rob

#5

Are you able to get the database values in to Corona or are you having difficulty getting the data out of MySQL?

Also check this out:  http://omnigeek.robmiracle.com/2012/04/15/using-corona-sdk-with-rest-api-services/

Rob

#6

Thanks Rob… My problem is purely syntax. How to include SQL variables into LUA. I can GET the data, parse it and access it, there just seems to be a shortage of resources that show me how I can interact with the values.

#7

In the blog post I referred to, the MySQL query comes back to PHP as an Associative Array.  This is very similar in nature to a Lua table, i.e. key-value pairs.  However you have to transform the data into something that can be passed in an HTTP request, which generally requires that data being converted into a web-safe string.  The best way to do this is to use JSON (JavaScript Object Notation).  Both Corona SDK and PHP support API calls to encode tables/arrays into a JSON string and then decode them back into their native table structure.

The basic flow is this:

Corona SDK makes a network.request() to the PHP script on the server.

The PHP script makes the database query, gets back the associative array.

The PHP script calls json_encode() on the array which returns a string:    $myJSONstring = json_encode( $myReturnedDataBaseArray );

The PHP script uses it’s “echo” statement to print the string out to the Web Server’s output stream.

Corona SDK’s network.request() recogonizes it got data on that stream, reads it in and calls your listener function that you defined in the network.request() call.  The data returned by the script will be in the “event.response” variable.

In the listener script convert the event.response, which should be the encoded JSON string and turn it back into a Lua table:

local json = require( “json” )

local myDatabaseTable

…

local function handleDatabaseQuery( event )

      if event.phase == “ended” then

            myDatabaseTable = json.decode( event.response )

      end

end

network.request(“http://path.to/yourscript.php”, “GET”, handleDatabaseQuery)

or something similar to that.  Of course there is more to the handleDatabaseQuery function. You need to check for errors and so on, but that’s the basic flow.   At the point the handleDatabaseQuery function is called, your table (that I named myDatabaseTable) will have the results of the query from the PHP script.  If your query was something like:  SELECT name, address, age FROM person where ID = 12, then the result should be a lua table with members, name, address and age:

print( myDatabaseTable.name, myDatabaseTable.address, myDatabaseTable.age)

or something like that.

Rob