Connecting to database HELP PLEASE

Getting Started Newbie Questions
#1

Good afternoon ! I am relatively new to corona sdk and much younger still in tinkering with database, but I am VERY in need of making a connection in a database (from 000 WEBHOST). The database contains 4 columns and the app needs to show me a list of the information it contains there (The information I need to get is: Name, Date, Description and Status). But on another page of the App, I need to be able to create and edit records as well. Could someone for the love of God help me? : \ I’ve been studying this for a long time but I never understand anything and I really need it: \ From now on, thank you.

Good afternoon ! I am relatively new to corona sdk and much younger still in tinkering with database, but I am VERY in need of making a connection in a database (from 000 WEBHOST). The database contains 4 columns and the app needs to show me a list of the information it contains there (The information I need to get is: Name, Date, Description and Status). But on another page of the App, I need to be able to create and edit records as well. Could someone for the love of God help me? : \ I’ve been studying this for a long time but I never understand anything and I really need it: \ From now on, thank you.

#2

Hosted databases are not something you can generally directly connect to for various security reasons. If you want to use Corona for this, then you will need to be able to write some server scripts at your web host. These are typically done in a language like PHP and act as intermediary between your app and your database.  You would use Corona’s network.request() API to hit a URL on your website that is your PHP script. That PHP script would take either an HTTP GET or HTTP POST request and extract the data from it (as key-value pairs). The script would be responsible for sanitizing the data (removing any SQL Injection hacks, etc.) and then making the query to the database (likely a MySQL database, tough it sounds like the database exits already)  The script would then take the returned data from the database query and create a table of key-value pairs to return. Use something like JSON to encode the data and output it. That output will come back to the Corona network.request() call were you can then decode the JSON and turn it into a Lua table.

This all sounds like a lot, but it’s really not that complex. For instance, you don’t need to know JSON, you need to know how to use json_encode() in PHP and json.decode() in Corona.

Typically a MySQL query returns an “associatve array” in  PHP which is basically PHP’s version of a Lua table. So once you have that result your PHP script would simply do:

echo json_encode($myQueryResutls);

In Corona inside the network listener function, you would do:

    local myDataBaseQuery = json.decode(event.response) 

Between the two line of code you basically transfer the table from PHP to Corona.

You probably will have a unique server script for each action. In database terms this is called CRUD - Create, Read, Update, Delete. So one script will read your table. One script would take a new record and insert it into the database etc.

Robo

#3

Even with the idea that this program will only be used INTERNALLY in a company of about 15 employees, would I need to protect myself against hacks?

Sorry I really am VERY lay on this subject, and how do you talk a “step by step” about what I have to study? For example, how do you make a connection to the database? And what has to be written in json? Already have a post about it? I’m really very lost because I always understood a database as a very difficult thing, but I want to get this idea out of my head kkkk

#4

Do you have to protect from hacks for an internal app? If it were an internal app, perhaps less so, however you said you’re going to run it on 000webhost, which means it’s publicly facing. Putting private data on a publicly available website would likely require an even higher level of security. Any body with a web browser can run your scripts. If the database server can be accessed without a script, it’s even more at risk.

I would start here:  https://www.codeofaninja.com/2017/02/create-simple-rest-api-in-php.html

There are a couple of other tutorials that you should read before taking a deep dive into that one from the same author:

https://www.codeofaninja.com/2011/12/php-and-mysql-crud-tutorial.html

https://www.codeofaninja.com/2014/06/php-object-oriented-crud-example-oop.html

You won’t need to worry about the Javascript side of things, Corona’s network.request() replaces the Javascript in the setup.

Rob

#5

Hosted databases are not something you can generally directly connect to for various security reasons. If you want to use Corona for this, then you will need to be able to write some server scripts at your web host. These are typically done in a language like PHP and act as intermediary between your app and your database.  You would use Corona’s network.request() API to hit a URL on your website that is your PHP script. That PHP script would take either an HTTP GET or HTTP POST request and extract the data from it (as key-value pairs). The script would be responsible for sanitizing the data (removing any SQL Injection hacks, etc.) and then making the query to the database (likely a MySQL database, tough it sounds like the database exits already)  The script would then take the returned data from the database query and create a table of key-value pairs to return. Use something like JSON to encode the data and output it. That output will come back to the Corona network.request() call were you can then decode the JSON and turn it into a Lua table.

This all sounds like a lot, but it’s really not that complex. For instance, you don’t need to know JSON, you need to know how to use json_encode() in PHP and json.decode() in Corona.

Typically a MySQL query returns an “associatve array” in  PHP which is basically PHP’s version of a Lua table. So once you have that result your PHP script would simply do:

echo json_encode($myQueryResutls);

In Corona inside the network listener function, you would do:

    local myDataBaseQuery = json.decode(event.response) 

Between the two line of code you basically transfer the table from PHP to Corona.

You probably will have a unique server script for each action. In database terms this is called CRUD - Create, Read, Update, Delete. So one script will read your table. One script would take a new record and insert it into the database etc.

Robo

#6

Even with the idea that this program will only be used INTERNALLY in a company of about 15 employees, would I need to protect myself against hacks?

Sorry I really am VERY lay on this subject, and how do you talk a “step by step” about what I have to study? For example, how do you make a connection to the database? And what has to be written in json? Already have a post about it? I’m really very lost because I always understood a database as a very difficult thing, but I want to get this idea out of my head kkkk

#7

Do you have to protect from hacks for an internal app? If it were an internal app, perhaps less so, however you said you’re going to run it on 000webhost, which means it’s publicly facing. Putting private data on a publicly available website would likely require an even higher level of security. Any body with a web browser can run your scripts. If the database server can be accessed without a script, it’s even more at risk.

I would start here:  https://www.codeofaninja.com/2017/02/create-simple-rest-api-in-php.html

There are a couple of other tutorials that you should read before taking a deep dive into that one from the same author:

https://www.codeofaninja.com/2011/12/php-and-mysql-crud-tutorial.html

https://www.codeofaninja.com/2014/06/php-object-oriented-crud-example-oop.html

You won’t need to worry about the Javascript side of things, Corona’s network.request() replaces the Javascript in the setup.

Rob