Posted By trav on May 27, 2013 | 0 comments
Overview: Interacting with a Remote MySQL Database in an Android Application!
One of my most requested tutorials is how to connect to a remote MySQL database from within an Android app. Unfortunately, a MySQL database and an Android app can’t communicate directly, so we will have to use a little bit of PHP to make this happen. In this mini series we will be creating an Android application that communicates with a remote database (MySQL) using PHP. We will be creating a simple PHP based web service that will allow our Android application to register a new user, login, and parse some JSON data (such as a news feed) within our app. It will be enough information for you to create a highscore system for a game, a content management system, or even to help you get started creating the next Facebook! So, let’s get started!
In order to do this, we will need to have a server, a MySQL database, some basic PHP knowledge, Eclipse, and the Android SDK. If you don’t know anything about PHP or SQL, don’t worry, we’ll keep it pretty simple. I will be using a shared server hosting plan from Host Gator, that I purchased awhile back and I will also be using XAMPP’s Apache server so you can also test locally if don’t have a hosting plan. If you do decide to sign up using our referral link, you can use the coupon code SPRING30 and currently get 30% off and 1 dollar domains which is a decent deal. (I’m probably not going to update this post, so the coupon code may be out of date if it isn’t spring 2013).
Follow the Remote Databases for Android Series!
- Part 1: Android Remote Databases and Servers Overview
- Part 2: Local Xampp Server and MySQL Database
- Part 3: Working with a Remote Server and MySQL
- Part 4: Creating Login and Registration PHP Scripts
- Part 5: Developing the Android Application
- Part 6: JSON Parsing and Android ListView Design
Introduction to the Android and Remote Databases Video
Introduction to the Android, PHP, MySql Databases, and JSON Mini Series
In this tutorial we are going to talk about the basic overview of our application and how Android, PHP, and our remote database will work together. If you already have an idea about how web services work, you could probably skip this tutorial, because we won’t be covering any Android code, setting up our project database, nor our php scripts in this tutorial.
**We could use a PHP framework (such as CodeIgnitor, Zend, CakePHP, etc) to make our web service a heck of alot easier to create, but instead we are going to code from scratch within this mini series. This way I won’t exclude any particular framework, and everyone should be able to get the basics**
What is a MySQL Database?
First, let’s cover the basics, what is a MySQL database? To keep this brief, a database is a place where we can store a lot of information. SQL is a pretty simple language to save, retrieve, update, and delete information from a database very efficiently. For example, our Android application may have 100,000 users and each user has their own custom homepage, we could find the user’s custom homepage from our MySQL database in well under a second, that’s pretty awesome! A MySQL database can have multiple different tables and each table will have rows and columns. It will be much easier to explain by example, but it’s basically a spreadsheet, so if you have ever used Excel, Google Spreadsheets, or anything of the sort, it will be a familiar concept.
Why do we need PHP?
As I’ve mentioned, our Android app and a remote MySQL database can’t communicate with each other directly without it being an incredibly tedious process. It would be like a Chinese person trying to talk to someone from Iceland. We don’t want to play charades if we don’t have to, so instead, we will get an interpreter. This interpreter will be PHP. PHP will do all of the talking for us. For example, if we want to create a new user for our application from our Android device, we will want our application to have some input fields, such as username, password, favorite kind of cheese, etc. When the user fills out this information and hits submit, we will pass the information to our PHP web service. The web service will connect to the MySQL Database, and find the “Users” table and insert a new row into the MySQL database with the information that was sent from our Android device. Once our row is created, we will have our webservice display some JSON data that will tell us whether we entered the data successfully or not. Lastly, our Android device will parse this JSON data and display a “Success” message, or a “Failure, please try again” message.
The process in visual form would look like this:
How does Android, PHP, SQL, JSON, and Remote Databases work together?
Now, that we’ve covered what a MySQL database is and why we need PHP, lets look at a simple example. Let’s say we want to create a new user and add some information into our database. Our Android app would have some EditTexts that will ask for the user’s name, password, favorite type of cheese, etc. When the user fills out this information and clicks submit, we will sent this information to our PHP web service, and our web service will make an SQL query which will add a new user to our current MySQL Database called “webservice” within the table “USERS”. Here is what our SQL query will look like:
INSERT INTO `mybringback_webservice`.`USERS` (`username` ,`password` ,`fav_cheese` ,`etc`)VALUES ('Travis', 'password1', 'Pepper Jack', 'etc...');We will tell PHP to call this query which will insert (add) the values into our USERS’ table which is located within our ‘webservice’ database which has the host of mybringback_. Once our webservice calls this query, we will have a new row in our “USERS” table. If we want to browse what our database would look like, it would look like this:
After our web service submits our query, our php script will tell us whether or not our query was successful by echoing some JSON data. The JSON data would look like this if there is a failure:
{ “successful” : 0, “response” : “Failure, Please Try Again!”}or like this if it was successful:
{ “successful” : 1, “response” : “Success, User Created!”}After our JSON response is created, we will tell our Android application to interpret the JSON response, this is called parsing. We will parse the data for the value “successful” and if it is a 1, we will know that we successfully created a user and the we will parse the data for “response” and display that message to the user on their Android device.
What’s up next?
That is about it for the basics of how an Android application interacts with a remote database using a web service. The best part about using a web service is that you can use the same web service for Android apps, iPhone apps, and even a desktop application if needed. In the next tutorial we will setup our development environment, our database, tables, users, and all of the software you will need if you don’t have a hosting plan.
