Sunday, 19 July 2015

Android MySQL Database Operations

In this post we are going to learn about how to connect to MySQL Database from your Android Application and perform database operations. Here we create an android app that contain some login Activity through which the user can retrieve information from database and a registration Activity through which the user can add information into the database. 

First you need to have the following components installed in your development machine. 
1. Database : Here we use the MySQL database.
2. Web Server : Here we use the Apache Web Server.
3. Server side Scripting Language :  Here we use PHP for server side scripting.
4. Android Development environment : You must install android sdk and android studio.  

I recommend you to download and install WAMPSERVER. The wamp server installer contains the following components.
Apache Server Application
MySQL Database
PHP/phpMyAdmin

First we have to create the database and table in MySQL. You can use the phpMyAdmin for mange your MySQL databases. Here our database name is "webappdb" and table name is "user_info".  
The table contains three columns "name", "user_name" and "user_pass". 

For our android application create a new folder inside the public directory of the wamp server and name the folder as "webapp".
First we have to write the needed php scripts and put it in the public directory of the wamp server. Here we need three php scripts, first one is for establish a connection with the database, second one for add informations into database and the last one for retrieve informations.

1. init.php
 <?php  
 $db_name = "webappdb";  
 $mysql_user = "root";  
 $mysql_pass = "root";  
 $server_name = "localhost";  
 $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);  
 ?>  

2. register.php
 <?php  
 require "init.php";  
 $name = $_POST["user"];  
 $user_name = $_POST["user_name"];  
 $user_pass = $_POST["user_pass"];  
 $sql_query = "insert into user_info values('$name','$user_name','$user_pass');";  
 ?>  

3. login.php
 <?php  
 require "init.php";  
 $user_name = $_POST["login_name"];  
 $user_pass =  $_POST["login_pass"];  
 $sql_query = "select name from user_info where user_name like '$user_name' and user_pass like '$user_pass';";  
 $result = mysqli_query($con,$sql_query);  
 if(mysqli_num_rows($result) >0 )  
 {  
 $row = mysqli_fetch_assoc($result);  
 $name =$row["name"];  
 echo "Login Success..Welcome ".$name;  
 }  
 else  
 {   
 echo "Login Failed.......Try Again..";  
 }  
 ?>  



Create an android application contain a Login Activity and Register Activity. 

activity_main.xml (Login Activity layout)
 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
   xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"  
   android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"  
   android:paddingRight="@dimen/activity_horizontal_margin"  
   android:paddingTop="@dimen/activity_vertical_margin"  
   android:paddingBottom="@dimen/activity_vertical_margin" tools:context=".MainActivity"  
   android:background="#0BB990"  
   android:orientation="vertical"  
   >  
   <TextView  
     android:layout_width="wrap_content"  
     android:layout_height="wrap_content"  
     android:text="Login Form"  
     android:textAppearance="?android:textAppearanceLarge"  
     android:textStyle="bold"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="10dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="User Name"  
     android:id="@+id/user_name"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="70dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="Password"  
     android:id="@+id/user_pass"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="20dp"  
     android:inputType="textPassword"  
     />  
   <Button  
     android:layout_width="100dp"  
     android:layout_height="wrap_content"  
     android:text="Login"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="10dp"  
     android:onClick="userLogin"  
   />  
   <Button  
     android:layout_width="wrap_content"  
     android:layout_height="wrap_content"  
     android:text="Register Now"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="50dp"  
     android:onClick="userReg"  
     />  
 </LinearLayout>  
android-localhost
register_layout.xml (Register Activity Layout)
 <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"  
   xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent"  
   android:layout_height="match_parent" android:paddingLeft="@dimen/activity_horizontal_margin"  
   android:paddingRight="@dimen/activity_horizontal_margin"  
   android:paddingTop="@dimen/activity_vertical_margin"  
   android:paddingBottom="@dimen/activity_vertical_margin"  
   tools:context="com.easyway2in.mysqlconnect.Register"  
   android:orientation="vertical"  
   android:background="#0BB990"  
   >  
   <TextView  
     android:layout_width="wrap_content"  
     android:layout_height="wrap_content"  
     android:text="Registration Form"  
     android:textAppearance="?android:textAppearanceLarge"  
     android:textStyle="bold"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="10dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="Name"  
     android:id="@+id/name"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="40dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="User Name"  
     android:id="@+id/new_user_name"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="20dp"  
     />  
   <EditText  
     android:layout_width="250dp"  
     android:layout_height="wrap_content"  
     android:hint="Password"  
     android:id="@+id/new_user_pass"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="20dp"  
     android:inputType="textPassword"  
     />  
   <Button  
     android:layout_width="130dp"  
     android:layout_height="wrap_content"  
     android:text="Register"  
     android:layout_gravity="center_horizontal"  
     android:layout_marginTop="30dp"  
     android:onClick="userReg"  
     />  
 </LinearLayout>  

android-database-tutorial

In this tutorial we use the HttpUrlConnection instead of HttpClient. HttpClient is deprecated form API level 22.
Ads By Google



Here the server is localhost and we test the application on an android virtual device. We need two URL. First one for registration purpose and the second one is for user Login.

Register URL : ""http://10.0.2.2/webapp/register.php";
Login URL"http://10.0.2.2/webapp/login.php";

Here we use the IP (10.0.2.2) -because the android virtual device use this default IP for connect to the localhost. You can also use your local computer IP address. This application use an AsyncTask to perform the server operations. 

MainActivity.java
 package com.easyway2in.mysqldbdemo;  
 import android.app.Activity;  
 import android.content.Intent;  
 import android.os.StrictMode;  
 import android.support.v7.app.ActionBarActivity;  
 import android.os.Bundle;  
 import android.view.Menu;  
 import android.view.MenuItem;  
 import android.view.View;  
 import android.widget.EditText;  
 public class MainActivity extends Activity{  
   EditText ET_NAME,ET_PASS;  
   String login_name,login_pass;  
   @Override  
   protected void onCreate(Bundle savedInstanceState) {  
     super.onCreate(savedInstanceState);  
     setContentView(R.layout.activity_main);  
    ET_NAME = (EditText)findViewById(R.id.user_name);  
     ET_PASS = (EditText)findViewById(R.id.user_pass);  
   }  
 public void userReg(View view)  
 {  
   startActivity(new Intent(this,Register.class));  
 }  
   public void userLogin(View view)  
   {  
   login_name = ET_NAME.getText().toString();  
     login_pass = ET_PASS.getText().toString();  
     String method = "login";  
     BackgroundTask backgroundTask = new BackgroundTask(this);  
     backgroundTask.execute(method,login_name,login_pass);  
   }  
 }  


BackgroundTask.java
 package com.easyway2in.mysqldbdemo;  
 import android.app.AlertDialog;  
 import android.content.Context;  
 import android.os.AsyncTask;  
 import android.widget.Toast;  
 import java.io.BufferedReader;  
 import java.io.BufferedWriter;  
 import java.io.IOException;  
 import java.io.InputStream;  
 import java.io.InputStreamReader;  
 import java.io.OutputStream;  
 import java.io.OutputStreamWriter;  
 import java.net.HttpURLConnection;  
 import java.net.MalformedURLException;  
 import java.net.URL;  
 import java.net.URLEncoder;  
 /**  
  * Created by prabeesh on 7/14/2015.  
  */  
 public class BackgroundTask extends AsyncTask<String,Void,String> {  
  AlertDialog alertDialog;  
   Context ctx;  
   BackgroundTask(Context ctx)  
   {  
    this.ctx =ctx;  
   }  
   @Override  
   protected void onPreExecute() {  
   alertDialog = new AlertDialog.Builder(ctx).create();  
     alertDialog.setTitle("Login Information....");  
   }  
   @Override  
   protected String doInBackground(String... params) {  
     String reg_url = "http://10.0.2.2/webapp/register.php";  
     String login_url = "http://10.0.2.2/webapp/login.php";  
     String method = params[0];  
     if (method.equals("register")) {  
       String name = params[1];  
       String user_name = params[2];  
       String user_pass = params[3];  
       try {  
         URL url = new URL(reg_url);  
         HttpURLConnection httpURLConnection = (HttpURLConnection) url.openConnection();  
         httpURLConnection.setRequestMethod("POST");  
         httpURLConnection.setDoOutput(true);  
         //httpURLConnection.setDoInput(true);  
         OutputStream OS = httpURLConnection.getOutputStream();  
         BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(OS, "UTF-8"));  
         String data = URLEncoder.encode("user", "UTF-8") + "=" + URLEncoder.encode(name, "UTF-8") + "&" +  
             URLEncoder.encode("user_name", "UTF-8") + "=" + URLEncoder.encode(user_name, "UTF-8") + "&" +  
             URLEncoder.encode("user_pass", "UTF-8") + "=" + URLEncoder.encode(user_pass, "UTF-8");  
         bufferedWriter.write(data);  
         bufferedWriter.flush();  
         bufferedWriter.close();  
         OS.close();  
         InputStream IS = httpURLConnection.getInputStream();  
         IS.close();  
         //httpURLConnection.connect();  
         httpURLConnection.disconnect();  
         return "Registration Success...";  
       } catch (MalformedURLException e) {  
         e.printStackTrace();  
       } catch (IOException e) {  
         e.printStackTrace();  
       }  
     }  
     else if(method.equals("login"))  
     {  
       String login_name = params[1];  
       String login_pass = params[2];  
       try {  
         URL url = new URL(login_url);  
         HttpURLConnection httpURLConnection = (HttpURLConnection)url.openConnection();  
         httpURLConnection.setRequestMethod("POST");  
         httpURLConnection.setDoOutput(true);  
         httpURLConnection.setDoInput(true);  
         OutputStream outputStream = httpURLConnection.getOutputStream();  
         BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(outputStream,"UTF-8"));  
       String data = URLEncoder.encode("login_name","UTF-8")+"="+URLEncoder.encode(login_name,"UTF-8")+"&"+  
           URLEncoder.encode("login_pass","UTF-8")+"="+URLEncoder.encode(login_pass,"UTF-8");  
         bufferedWriter.write(data);  
         bufferedWriter.flush();  
         bufferedWriter.close();  
         outputStream.close();  
         InputStream inputStream = httpURLConnection.getInputStream();  
         BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream,"iso-8859-1"));  
         String response = "";  
         String line = "";  
         while ((line = bufferedReader.readLine())!=null)  
         {  
           response+= line;  
         }  
         bufferedReader.close();  
         inputStream.close();  
         httpURLConnection.disconnect();  
         return response;  
       } catch (MalformedURLException e) {  
         e.printStackTrace();  
       } catch (IOException e) {  
         e.printStackTrace();  
       }  
     }  
     return null;  
   }  
   @Override  
   protected void onProgressUpdate(Void... values) {  
     super.onProgressUpdate(values);  
   }  
   @Override  
   protected void onPostExecute(String result) {  
    if(result.equals("Registration Success..."))  
    {  
      Toast.makeText(ctx, result, Toast.LENGTH_LONG).show();  
    }  
     else  
    {  
     alertDialog.setMessage(result);  
      alertDialog.show();  
    }  
   }  
 }  

Add the Internet permission in Manifest file.
 <uses-permission android:name="android.permission.INTERNET"></uses-permission>"  

Watch Video Tutorial Of this Topic

Download This Android Studio Project
   
This is how an android application connect to MySQL database run on a remote server and perform basic database operations. I hope you got the concepts. Please share your experience via comments.


38 comments:

  1. Good day... I have one question.
    you said that this url is used for virtual device "http://10.0.2.2/webapp/register.php". Then what url should we use when we are checking apps installed in mobile.

    ReplyDelete
  2. This application works only on a Virtual Device. And it work with localhost only.

    ReplyDelete
    Replies
    1. and if we want to use in a real device please what need to do ?

      Delete
  3. hello PRABEESH R K !
    I test your code but having some problems.

    When I write PHP to connect mySQL database like this :
    $name = "eric";
    $user_name = "123";
    $user_pass = "456";
    and it insert into my user_info table successfully.

    But when i use Android emulator "Genymotion" to start my application.
    My database doesn't insert any data, after registering 'name', 'username' and 'password'.
    $name = $_POST["user"];
    $user_name = $_POST["user_name"];
    $user_pass = $_POST["user_pass"];
    I think my PHP connect to mySQL is successful, but application has something error to connect PHP.

    these URL = "http://127.0.0.1/webapp/register.php", "http://10.0.2.2/webapp/register.php" I tried.
    Help me please, THANKS.

    ReplyDelete
    Replies
    1. I'm experiencing the exact same thing! Checked the connection to the database it is alright but it doesn't show on the databse. Storing it using php works just not thru the database. Btw I used the the url he taught. I have been debugging for a some time alrdy. I need to master android and mysql connectivity for my project.

      Delete
    2. THIS WILLL HELP YOU
      ------------------------------------

      test your code using emulator thats comes with google(the default emulator),
      i think the IP for "Genymotion" is different , iam sure about that

      if you are using genymotion try 10.0.3.2 as the ip (eg:"http://10.0.3.2/webapp/register.php")
      if this does not work,update your geny motion to 2.2, then try this again

      Delete
  4. Hello and thank you very much for this amazing tutorial Prabeesh, I got a question though:
    You just wrote that this application works only on a virtual device, and it works with local host only,
    What do I need to do to make it work on an actual android device and on an actual server?

    Thanks in advance, Ben.

    ReplyDelete
    Replies
    1. You can use the same way to connect your android application to an on-line database. But you have to use the IP Address of the server in your android application....

      Delete
    2. You can use the same way to connect your android application to an on-line database. But you have to use the IP Address of the server in your android application....

      Delete
    3. sir can you plzz explain more about this...i have same problem

      Delete
  5. The application may be doing too much work on its main thread.
    Already have a pending vsync event. There should only be one at a time.


    When I run your app it gives me that warning and it doesn't make CRUD operations on database.
    I'm also using AsyncTask and I don't know why it gives me such error.

    Thanks in advance.

    ReplyDelete
  6. hello sir, thank you for the tutorial it worked perfectly.
    i have a few doubts regarding it. what if i want to go to a new activity after the login is successfull.
    where do i put the intent code???


    thank you in advance

    ReplyDelete
  7. could you please guide with some tutorial on android app connecting to server for retrieving and storing data most specifically web server(www.abc.com) ,something like this web domain name. I am really searching for this kind of code with explanation. Help me out. thanks in advance.

    ReplyDelete
  8. Sir Prabeesh
    I am working on this android app that have login activity and I'm curious if it's possible to have a button that takes me to a activity depending on what type of user am I. Example in the database my type of user is admin when I click the login button it will take me to admin.xml and if I am a student it will take me to student.xml

    ReplyDelete
    Replies
    1. Yes , definetely you can try defining a 'onClick' method and starting an intent which starts your activity.

      Regards.

      Delete
  9. how to use this application through online
    please send coding

    ReplyDelete
  10. Hello Sir.first of all Tons of thanks for all of ur priceless tutorials.I had one doubt about retrieving data from database,which is if i wish to retrieve particular user from db and i want to display that information another activity what i have to do which listener should i add for displaying those retrieved data in another activity(at run time). thanks before ur helpful reply. And I request u to continue this tutorial as continuity chapter on this same retrieval topic in diff activity.

    ReplyDelete
  11. hello sir, thank for the tutorial. But instead of dialog box, how to go next activity page after has correct username and password.. do you have any idea...

    ReplyDelete
  12. Hi Prabeesh...
    I have a problem while inserting the data. It is not getting saved. Registration successful message is not shown.
    In the log file it says - "getSlotFromBufferLocked: unknown buffer: 0xb3fd7390".
    Could you please guide me...

    Thanks

    ReplyDelete
  13. If You are Having Problem with Insertion into the Database
    Change Your register.php to



    PRABEESH R K ! Thanks So Much. God Bless You

    ReplyDelete
    Replies
    1. Can i know what changes you had made?
      i have the same problem

      Delete
  14. Hello sir can I use same wamp server IP 10.0.2.2 for xampp server ?

    ReplyDelete
  15. Hello Sir,
    How can you display the message in textview rather than dialog.

    ReplyDelete
  16. Hi
    I have follow your youtube and run the applicaton i meet this issue how to solve this

    This is the error - from android studio
    emulator: ERROR: unknown skin name 'WVGA800'

    ReplyDelete
  17. Hi

    Prabeesh

    I like your code wonderful working but i need little bit changes need.

    in this code refer login.php and register.php hosting ip navigate so i canot run this from in my android mobile

    so i need how to run this with in android mobile? can you help me

    Questions

    1. how to run this from android mobile - because login.php and register.php have the local machine ip suppose
    that pc no internet means how to connect this?

    2. suppose i install mysql in my mobile means which mobile app i install my android mobile

    can you fixed this issue thats helpful for me.

    I am waiting for your reply daily

    ReplyDelete
    Replies
    1. Watch this video .https://www.youtube.com/watch?v=vOKgO09edyk&list=PLshdtb5UWjSppGmM3IdygV6RusjU3KjlZ&index=6

      Delete
  18. Hello Sir,
    Where is the code for Register.java File???????

    ReplyDelete
  19. Hi Mr PRABEESH thank you for the tuts I have my app registering and login in perfectly. However when I try running on my android device it does not execute and code. all I see are button clicks operations. but not db activities.

    ReplyDelete
  20. Hi, I have tried this but my app exit and says "Unfortunately app stopped". I don't know why. Can you help me?

    ReplyDelete
  21. I get Registration Success message however there is no new record in mysql database.. What is the possible problem in my situation ?

    ReplyDelete
  22. hello sir,
    my whole code is correct and when i excecute my app
    I am getting erron in ""if(result.equals("Registration Success.."))"" and in my pakagename.com
    NullPointerException BackgroundActivity.onPostExecute(BackgroundActivity.java:183)

    ReplyDelete
  23. Sir i want to add auto text complete for entering city name: if a person select his state Guarat then in the next textbox when he will enter his city name then, the name of city only of gujarat state should be displayed as autotext complete & value should be displayed from database. can u tell me coding for this process.?

    ReplyDelete
  24. Hello Sir,

    Everytime I press the register button on Main Activity the app crashes. I have copied the code and tried to run it but it crashes everytime. Please help. Thanks!

    ReplyDelete
  25. sir in our eclipse version we donot have android.support.v7.app.ActionBarActivity; what to do now

    ReplyDelete
  26. hello prabeesh
    your code worked well for me.My question is after login is successful how should i navigate to next layout in the users account

    ReplyDelete
  27. there is no Register.java . i tried following your tutorial to make one but i got alot of errors. please end me a working register.java file

    ReplyDelete
  28. Hey What should i do to make the app Work on an actual android device and on an actual server?
    thank youu

    ReplyDelete