How to Connect an Android App to a MySQL Database

Connecting a mobile app to a database is a critical foundation of many of the world’s most popular applications. User accounts, news updates, and statistics are just a few examples of the data one may wish to store and pull. In this post, we are going to look at how to connect an Android app to a MySQL database using a free SkySilk VPS. Our Android app will be a mock stock price tracker, displaying the name and price of some made up stocks.

 

How to Connect an Android App to a MySQL Database: Setting up a MySQL Server

The first step in learning how to connect an Android app to a MySQL database is configuring the backend. We will need a MySQL server of course, but we will also need a simple API. Our app won’t connect directly to the database, instead, it will need to send requests to an API that we will write. This simple script will take the request, process it, and respond to the app. For this tutorial, we will be using a PhP script for our API. Since we will be using both MySQL and PhP, the LAMP stack template offered by SkySilk is perfect for us!

READ MORE: How To Deploy a LAMP Stack for MySQL VPS

Once the VPS is ready to go, navigate to the IP address and view the control panel.

The LAMP stack control panel

Click into Webadmin, and log in with the username ‘root’, and the password that you set when deploying the VPS. After logging in, navigate to the top toolbar -> servers -> MySQL Database Server. We are going to want to create a new database, so go ahead and click ‘Create a New Database’ on the top module. Here we are presented with a fresh database to configure.

For the sake of this tutorial, we are going to keep our database very simple. Name your database and table whatever you like, then move on to the fields. We will include an id field, which is the primary key and auto-increments. Then we will add two more fields, one for name and one for price.

A new database with three rows of data is created.

Click create, and the database will appear under the list of databases. Now click into the database, and you will be able to navigate into the table that was created as well. Click view data, and then add row. We will leave the id field blank, and make up a name and a price. And finally, add as many rows as you would like, all of which will be displayed in our Android app.

The table and its data are displayed.

Here we can see that we added three rows to our table. Now our database has some data we can pull!

Configuring the Web API

The next step in learning how to connect an Android app to a MySQL database is actually pulling our data. To do this, we will write a simple PhP script. Go ahead and open your favorite text or code editor and add the following code:

Note: Make sure on line 4 the parameters match your configuration. Username will be ‘root’, password will be the MySQL password you set when deploying your VPS, and ‘dbname’ will be whatever you named your database. Save the file as “stock_service.php”.

This script will create a database connection, select all rows from the table we specified, and then output any results as a JSON array. It now needs to be added to our web server in order for our app to access it.

Navigate to the top toolbar again, tools -> File Manager. Using the top toolbar on the page, create a new directory, and name it ‘api’.

A new directory 'api' is created in /var/www/

Click into the new ‘api’ directory and upload the ‘stock_service.php’ file. The script can now be called by its address on the web. If you want to test your script, navigate to ‘http://your_vps_ip_address/api/stock_service.php’, of course replacing ‘your_vps_ip_address’ with the IP of your VPS. If all goes well, you should see something like:

The JSON output of the crypto service PhP script.

This is the JSON array that the script outputs at the very end. Now we just need to build our Android app which will pull this data and display it in a much more aesthetically pleasing format.

How to Make an Android App to Display the Data

Open Android Studio and create a new project. Name it what you like and select empty activity. The first thing we need to do is allow our app to access files on the internet. Navigate to the AndroidManifest.xml file and make sure it has the internet user permission:

Next, let’s configure the UI for our app. Open the activity_main.xml file and add the following code:

 

Pulling and Parsing the Data

Finally, let’s add the functionality to pull and parse the data from the web API. Open MainActivity.java and add these lines of code:

Let’s discuss what happens here.

Developing an app? Read about issue tracking with GitLab and other Git repositories

The main attraction is the downloadJSON function. We call this function initially and pass the url for our PhP script. Then, upon receiving a response, we parse the JSON data and add it into an array titled ‘stocks’. Finally, we load the array into our list view.

Build and run your app, and you should see all the data from your MySQL database!

The Android App displays the data from the MySQL database. Congratulations, you've learned how to connect an Android App to a MySQL Database!

 

How to Connect an Android app to a MySQL Database: Closing Thoughts

Now that we’ve learned how to connect an Android app to a MySQL database, there are several things to do next:

  • Write functionality to send data to the MySQL database
  • Write functionality to edit rows
  • Add the ability to delete rows

A final note to consider is security. While this app is functional and good for testing purposes, it is not secure and ready for live users. Security itself is a huge topic to research and properly implement before allowing live users to access any databases you control.

Deploy a Self Hosted LAMP Server

That’s it for this post. Well done making it through, and now you know how to connect an Android app to a MySql Database!

And if you are curious about repeating the process on iOS, SkySilk has you covered.

Read More: How to Connect an iOS App to a MySQL Database

 

(Visited 17,833 times, 2 visits today)

Share this post with your friends