Connecting Android App to Remote Database using PHP and MySQL


You may have come across applications in which sends and receives data from the server. The server will store and fetch the data in a remote database. In this tutorial, I will explain how you can store and retrieve data from a remote MySQL database using PHP and how can you access the data in the Android Application. This tutorial will help you in learning the following:

  • Adding data to a remote database in Android.
  • Updating data in a remote database in Android.
  • Fetching data from a remote database in Android.
  • Deleting records in a remote database in Android.

First of all, we will see how can we set up the server and database on your desktop and then we will see how to access the data from the server.

Installing WAMP Server

In order to create a web server on your local machine, you need to install WAMP server or XAMPP server or any other similar software. The installation process is simple: You just need to download the software and install the application. I personally use WAMP server and will be referring to it in the rest of the article.

Once you have installed wamp server, launch the program from Start => All Programs => WampServer => Start WampServer. You can verify whether the server has successfully started by launching http://localhost/ in your browser. You should see a screen as shown below:

Launching localhost in WAMP server

 

Creating database in MySQL

1. Open http://localhost/phpmyadmin/ in your browser. This will prompt you for inputting username and password. By default username is root and there is no password (leave the password field empty). Once you login you will see the screen as shown below:

MySQL PHPMyAdmin

2. Click on Databases and create new database say androiddeft. 

MySQL phpMyAdmin Creating new database

3. In order to store data in the database, you need to create tables inside the database. To achieve this, click on SQL tab and Copy & Paste the following query and click on Go.

MySQL phpMyAdmin Creating new Table

The above query creates movies table, used for storing movie details. We will be demonstrating the similar application used for demonstrating SQLite database in Android.

Connecting to MySQL database from PHP

1. Navigate to the www folder of your WAMP installation (Usually located in C:\wamp\www, if you haven’t changed during the installation). This is the folder where you PHP projects reside.

2. Create a folder named movies inside www directory and a subdirectory, db inside it.

3. Create a file named db_connect.php inside db folder and add the following code. This file contains database connection details as shown below and helps in connecting to MySQL database. This file has to be included in all files where we need to perform a database operation.

MySQL CRUD Operations using PHP

We will see how to perform the CRUD (Create-Retrieve-Update-Delete) operations in PHP.

Adding a new Movie (Inserting a row to the database)

Create a file add_movie.php inside movies folder and place the following code in it. This code helps in adding a new movie to the database. Here we check for mandatory parameters and prepare an insert query. The parameters need to be sent as POST parameter. If the movie got added successfully, then we display a success response. If there are any errors, then an error message is displayed.

When a row gets inserted successfully, we get the following response:

If there are any parameters missing in the request then we display following response:

If any error occurs then,

Fetching All Movies (Retrieving data from database)

Create file fetch_all_movies.php inside the PHP project and add the following code for fetching all movie details:

Here we query the database for movie_id and movie_name and we iterate over the fetched result and populate the result array.

On successful fetching of result we get the following response:

Fetching a single movie detail (Retrieving a single row from database)

We can fetch the details of a single movie by passing the movie_id in the request. That is by using the primary key of the table, we can fetch a unique row from the database.

Create a file get_movie_details.php inside the movies folder and add the following code. The code expects the mandatory parameter movie_id to be passed in an HTTP GET method.

Now if you hit http://localhost/movies/get_movie_details.php?movie_id=1 in your browser you should be able to see the following response:

When a movie is not found, you will get:

Updating a movie (Updating a row in database)

When you wish to update a row in the database, you send the column value that needs to be updated along with the primary key of the table.

Create a file named update_movie.php and add the following code:

On successful update you will get the following response;

If the movie is not found,

Deleting a movie (Deleting a row from the database)

When you want to delete a movie, you need to pass the movie_id in the request.

Create a file delete_movie.php and add the following code:

Here the code requires movie_id to be passed in an HTPP POST method. Once the movie gets deleted, you will get the following response:

Creating Android Project

Now we will see how to use the created APIs in the Android application.

1. Create a new project in Android Studio with the name Remote MySQL Connection.

2.  Add Internet and Check Network Status permissions in AndroidManifest.xml

3. Add the string variables to be used in this application to strings.xml

4. Create a package named helper inside the main package and add a class named CheckNetworkStatus.java. This class has a static method isNetworkAvailable, which checks the network status of the device.

5. Create a class HttpJsonParser inside the helper package. This class helper has a method makeHttpRequest which helps in making HTTP GET and POST requests. We are reusing this class from JSON Parsing Tutorial.

6. Add 2 buttons to activity_main.xml (I have renamed it to activity_remote_my_sql.xml) with labels View All Movies and Add New Movie:

7. Create a file list_background.xml inside drawable with the following code. This is used to style the list view, used to display the list of movies.

8. Create a layout file list_item.xml inside the layout folder. This layout file is used to display a custom ListView

9. Create a new Activity called MovieListingActivity with layout file activity_movie_listing.xml

The layout contains a ListView to display the movies list:

MovieListingActivity fetches the list of movies from the server using AsyncTask and populates the ListView

 

Movie Listing with Customized ListView

10. Create an activity MovieUpdateDeleteActivity with layout file activity_movie_update_delete.xml

This activity has EditTexts, which is used both to display and edit the movie details. It has 2 buttons, 1 to update the movie details and another to delete the movie

Below is the snapshot of the activity:

Updating new Movie

11. Create another activity named AddMovieActivity with layout file activity_add_movie.xml 

This activity helps in adding a new movie to the remote database.

Adding new Movie

12. Finally, update RemoteMySQLActivity with the following code. It calls MovieListingActivity and AddMovieActivity whenever the respective button is clicked.

Demonstration

Below video shows the demonstration of the app:

Source Code and APK file

You can download Source code and APK files from below links:

Download Android CodeDownload APKDownload PHP Code

Testing on Android Device

In this tutorial, I have used 192.168.43.72 as the base URL, which is my local IP address. You can replace this with your IP address or remote URL.

In order to test your application on your Android device,

1. Open httpd.conf file of your WAMP Installation (Located in D:\wamp\bin\apache\Apache2.4.4\conf in my case). You can also open it from the Notification Area Icons:

WAMP server Editing httpd configuration

Now Add the highlighted line inside Directory tag:

This allows the WAMP server to be accessed throughout the network.

2. Turn on WiFi Hotspot on your device and connect your device to PC.  Now you should be able to test the application.

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s