Android MySQL Tutorial to Perform Basic CRUD Operation

Hello friends, Here is another Android MySQL Tutorial, in this post we will learn the basic CRUD operation in MySQL database from Android Application. So without wasting time lets start our Android MySQL Tutorial.

What is CRUD?

I guess many of you already know that what is CRUD. But if anyone don’t know, CRUD is an Acronym for the Basic Database Operations. In any database we do the following basic operations.

  • Creating a Record -> In the database we insert a record.
  • Reading Stored Records -> No point of saving data when we can’t read it back 😛 (LOL). So the second operation is reading the stored data back.
  • Updating Stored Records -> We may also need to update the existing data. So the third operation is update.
  • Deleting Records -> Lastly we may also need to delete the existing data from the database.

So basically in any database we perform the above mentioned operations. In this tutorial I am going to use MySQL and PHP.

Why PHP and MySQL and Why Not SQLite?

Android gives us a feature of SQLite to use as the RDBMS for our app. But we can’t use SQLite only if we are building an app. This is because we need a centralize server. SQLite is local for every device so if a user is using your application you will not be able manage your users if it is only in SQLite. That is why we need a centralize database where we can store our app data.

If you want to learn about the SQLite using the Latest Architecture Component ROOM. Then here is a complete FREE COURSE for Android Room Database. 

Do we have only PHP and MySQL for this?

Obviously NO? You can use any server side scripting language or database application. I am using here PHP and MySQL because it is easily available and I already know it. 😛 (LOL) But if you are an expert in Python or JAVA or NodeJS or basically any other technology then you can go with it. It will change only the server side coding. Android part will always be the same.

Building Web APIs

The first step is building the required Web APIs. This is because from the android application, to communicate with our web server we need an interface called API.

So our android device will send a request to our API,  then our API will perform the requested task and it will give us the response related to the task. You can see the below diagram for more clarification.

android mysql tutorial

The response that the web server gives should be in a standard structure, so that in our android side we can easily parse and get the information from the structure. For this everyone now uses JSON. If you heard this term for the first time then you should consider watching this 7 minute quick JSON tutorial.

I guess you got a basic idea about the task that we are going to do in this tutorial.

Creating Database

It is obvious that we need a database first 😛 . So here I am using XAMPP (You can go with wamp or lamp as well). So first create the following database.

mysql database

  • So open localhost/phpmyadmin and run the following query to create the above table.

Creating PHP Project

Now to perform the Database Operations we will create a PHP Project.

  • So inside htdocs (c:/xampp/htdocs) create a new folder (You can also use an IDE like PHP Storm to create project but remember create the project inside c:/xampp/htdocs only). 
  • I have given the name HeroApi to my project. You can give any name. But I would say give the same name or else you may lead up to some errors following the post if you are a newbie. 
  • Inside the project create two more folders named includes and Api. You can see the below screenshot for the directory structure that I am using. (I am using Sublime Text for coding server part).

php project structure

  • You see we have four php files (3 inside includes and 1 inside v1). So you create these files as well.

Project Structure

  • So we have the following things in our PHP project.
    • includes
      • Constants.php: In this file we will define all the required constants e.g., database name, username, password etc. 
      • DbConnect.php: This fill will contain a class where we will connect to our MySQL database.
      • DbOperation.php: The actual CRUD operation is performed inside this file.
    • v1
      • Api.php: This is our API, we will send request to this file only from the android side. And this file will handle all the API calls.

Defining Constants

  • First come inside the file Constants.php and write the following code.

Connecting to Database

  • Now inside DbConnect.php write the following code. I have explained the code using comments.

Performing Database Operations

  • Now we will do the CRUD operation inside the DbOperation.php file.

Handling API Calls

  • Now here come the main part, which is handling the API calls. So come inside the Api.php which is inside the v1 folder.

  • Now lets test the APIs.

Testing the API Calls

  • For testing the API Calls here I am using POSTMAN. It is a REST API Client for Google Chrome.
Create Operation

testing api create operation

Read Operation

testing api read operation

Update Operation

Upate Operation

Delete Operation

delete operation

You see all the operations are working absolutely fine. Now we can move ahead in creating our Android Project. But before moving to android side below you can see our API URLs.

Finalizing API Calls

  • The below table displays our API URLs with the Parameter and Method. Remember using localhost in android side will not work. You need to find your IP. I have my IP in the below table, but in your case you need to find yours. So if you are using a windows you can use ipconfig command to find IP and for MAC or Linux use the ifconfig command. For more details you can visit this tutorial.

 

  • POST: http://192.168.101.1/HeroApi/v1/Api.php?apicall=createhero
  • GET: http://192.168.101.1/HeroApi/v1/Api.php?apicall=getheroes
  • POST: http://192.168.101.1/HeroApi/v1/Api.php?apicall=updatehero
  • GET: http://192.168.101.1/HeroApi/v1/Api.php?apicall=deletehero&id=idvalue

Want to Explore Building RESTful APIs?

Here we built a very basic API that demonstrates the CRUD operation. But in real world scenarios we use FRAMEWORKS to make the API creating easy and structured. If you want to dig a bit more about building RESTful APIs. Then you should check the following course. It is absolutely FREE.

Android MySQL Tutorial

We have our Web Services, and now we can build the android application. So lets start.

Creating a new Project

  • Create a new Android Studio Project. Here I have created a project named MyHeroApp.
  • Once your project is loaded inside the package we will create all the helper classes that is required for thie project.

Creating Helper Classes

Class To Store API URLs

  • First create a class named Api.java and write the following code.

Hero Model Class

  • We also need a model class for our Hero. So create a class named Hero.java and write the following code. It will have only properties, constructor and getters.

Request Handler

  • We need to send GET and POST request to our API URLs, and for this I am creating a new class that will perform these tasks. So create a new class named RequestHandler.java and write the following code.

Defining Internet Permission in AndroidManifest

  • As we need to perform network request from our Application, we need to define internet permission for this. And because we are working with localhost our APIs are not secured i.e. we have HTTP URLs and not HTTPS for our APIs.
  • And by default your application are restricted to communicate with non HTTPS URLs (For security reasons). So you need to explicitly define that your app should be allowed to communicate with HTTP URLs. And to do this you need to add usesCleartextTraffic=”true” inside your opening application tag. (See the below code that is AndroidManifest.xml for my project.).

Designing User Interface

  • The below image shows how our final application will look.
Application UI
Application UI
  • So we need to design the above mentioned things. I have already designed the activity_main.xml so you can directly use the below code.

  • For the spinner I have used static entries using xml array. So you also need to create this array. For this go inside values -> strings.xml

  • Thats it for the User Interface part.
  • Now lets perform the CRUD operations in our MySQL database.

Class to Perform Network Request

  • The point is we cannot directly perform a network request in the application’s main thread. So for this we need an AsyncTask to perform the task in a separate thread. Hence we will create an inner class inside MainActivity.java.

Create Operation

  • Now lets save a new hero to our database. First we will define all the views. And we will attach a clicklistener to the button and inside the click event we will call the method to create a new record in the database.

  • Now we need to create the method createHero().

  • Now run the application and try adding a new hero.
android mysql tutorial - create operation
Android MySQL – Create Operation
  • Its working fine, you can check the database as well. Now lets move to the read operation.

Read Operation

  • We will display all the heroes from the database in a ListView, the ListView also have the Update and Delete Button.
  • So for this first we will create a custom Layout for our ListView.

List Layout

  • Create a layout resource file named layout_hero_list.xml.

Custom Adapter Class

  • Create one more inner class inside MainActivity class. We will name it HeroAdapter.

Retrieving Heroes from the Database

  • Create a method named readHeroes().

  • Now one more method we need to refresh the Hero List. So create a method named refreshHeroList().

  • Now also uncomment the commented method inside PeformNetworkRequest class and onCreate() method. You need to uncomment readHeroes() inside onCreate() and refreshHeroList() inside PerformNetworkRequest class. 
  • Now you can try running the application.
android mysql tutorial - read operation
Android MySQL – Read Operation
  • You can see it is also working fine. Now lets do the UPDATE operation.

Update Operation

  • For updating we will create a new method named updateHero().

  • Now just uncomment the method updateHero() inside the click listener of buttonAddUpdate.
  • And now we can try updating a record.
android mysql tutorial - update operation
Android MySQL – Update Operation
  • So the update is also working absolutely fine. Now lets move to the last operation which is delete.

Delete Operation

  • For delete also we need a new method. So create a method named deleteHero().

  • Now uncomment the method deleteHero() inside the HeroAdapter class. 
  • And lets test the delete operation as well.
android mysql tutorial - delete operation
Android MySQL – Delete Operation
  • So it is working fine as well. And we have done with all the basic CRUD operations.

The complete code for MainActivity

  • If you had some confusions following the above steps here is the complete code for MainActivity.java.

  • So after following every step correctly your application will behave as shown below.
Android MySQL Tutorial
Android MySQL Tutorial

Android MySQL Tutorial – Source Code Download

  • If you are still facing troubles you can get my source code from below. It has everything, the server side scripts, database file and android project.

So thats all for this Android MySQL Tutorial friends. CRUD Operation is needed in almost every application so it is a very important thing. If you are having any confusions or queries regarding this Android MySQL tutorial don’t hesitate in asking on comments. And if you liked the post then PLEASE SHARE IT. Thank You 🙂 

100 thoughts on “Android MySQL Tutorial to Perform Basic CRUD Operation”

  1. Just blowed away bro..!! just trying for almost two days…bt you made it so simple…10 minutes job…bt it took more than a days count bt failed even…thankx bro…cheers (y)

    Reply
  2. Just want to say you rock man!
    Thank you very much for this tutorial, other tutorial use some depecrated class, but this one is up-to-date!

    Reply
  3. I have tried many times, but always the error in the layout / activity_main.xml file in the block below:
     
     

    ERROR RETURN:
    Error: (52, 30) No resource found that matches the given name (at ‘entries’ with value ‘@ array / teams’).

    Can anyone help?

    WIN7 / 64 / ANDROID STUDIO ver 2.3.3

    thanks

    Reply
  4. Great turorial!

    I’m using this example for creating my own project.
    Only i’m using Volley.

    The problem is that i get an ‘Invalid API call’ error message.
    How do i pass the ‘apicall’ to the php-file? Map getParams?

    Thanks

    Reply
  5. hello mr belal, thank you for your tutorial, i really appreciate it, this tutorial is rally easy to understand, but i have some question, i’m learning your code and because of my laziness i only make 2 attribute on my sql table, just id and name, every operation is working fine, but when im insert data and try to update there’s some error, it says

    Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables in /home/faisal/Public/Web/android_sql_api/includes/DbOperation.php on line 24

    And Heres Line 24 on my DbOperation File

    21. //Create operation
    22. function insertData($name) {
    23. $stmt = $this->con->prepare(“INSERT INTO myTable (name) VALUES (?)”);
    24. $stmt->bind_param(“ssis”, $name);
    25. if($stmt->execute())
    26. return true;
    27. return false;
    28. }

    And This is My Insert Case on main php

    case ‘insertData’:

    //first check the parameters required for this request are available or not
    availableParameters(array(‘name’));

    //create a new operation object
    $db = new DbOperation();

    //create a record by call insert method
    $result = $db->insertData(
    $_POST[‘name’]
    );

    //if the record is created adding success to response

    if($result) {
    //record is created means no error
    $response[‘error’] = false;

    //in message we have a success message
    $response[‘message’] = ‘Record added successfully’;

    //reload the data
    $response[‘data’] = $db->getData();

    } else {

    //if record is not added that means there is an error
    $response[‘error’] = true;

    //and we have error message
    $response[‘message’] = ‘some error occurred, please try again’;

    }

    break;

    i’m on the test step and using Postman chrome app as you recommended, thank you, i really appreciate if you want to help

    sorry for english

    Reply
  6. i get this error when i try to run the app

    Error:(98, 70) Gradle: error: URL_CREATE_HERO has private access in Api
    Error:(103, 70) Gradle: error: URL_READ_HEROES has private access in Api
    Error:(137, 70) Gradle: error: URL_UPDATE_HERO has private access in Api
    Error:(151, 70) Gradle: error: URL_DELETE_HERO has private access in Api

    Please help

    Reply
  7. Sir Belal, my add form is consist of 2 activities, how will I add it in the database if fields are divided in to two activities.
    If I had a chance to show you the activity, to make it clear.

    Reply
  8. Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables in
    C:\xampp\htdocs\www\includes\dboperations.php on line
    28

    {“error”:true,”message”:”Some error occurred please try again”}

    Can you help me?

    Reply
  9. Hi, i have a problem when i put the url on POST MAN showing this problem:

    “error”: true,
    “message”: “Parameters name, realname, rating, teamaffiliation missing”

    what could it means? i nwe in android, please helpppp D:

    Reply
  10. Hello sir,This is really great tutorial.
    I want to insert table data in thai language and retrive it in same.
    i am able to insert data in thai language but unable to get response in thai.
    any solution??

    Reply
  11. AoA, hi i have Coppied every thing in a sample project, but i am getting
    {“error”:true,”message”:”Invalid API Call”}
    this when i run API.php

    kindly tell me what is this where is API calling.
    P.S em newbie to PHP and andriod.
    help would be appriciated thanks

    Reply
  12. {“error”:true,”message”:”Parameters name, realname, rating, teamaffiliation missing”}

    What is this error ?? I’m getting the above error while testing api with postman

    Reply
  13. when testing the api on postman, errors occurred pls why

    Warning: mysqli::__construct(): (HY000/1049): Unknown database ‘android’ in
    C:\xampp\htdocs\HeroApi\includes\DbConnect.php on line
    27

    Failed to connect to MySQL: Unknown database ‘android’

    Warning: mysqli::prepare(): Couldn’t fetch mysqli in
    C:\xampp\htdocs\HeroApi\includes\DbOperation.php on line
    27

    Fatal error: Uncaught Error: Call to a member function bind_param() on null in C:\xampp\htdocs\HeroApi\includes\DbOperation.php:28
    Stack trace:
    #0 C:\xampp\htdocs\HeroApi\v1\Api.php(59): DbOperation->createHero(‘Spiderman’, ‘Peter Parker’, ‘4’, ‘Fantastic Four’)
    #1 {main}
    thrown in
    C:\xampp\htdocs\HeroApi\includes\DbOperation.php on line
    28

    Reply
  14. Warning: mysqli::__construct(): (HY000/2002): No connection could be made because the target machine actively refused it.
    in
    C:\xampp\htdocs\HeroApi\includes\DbConnect.php on line
    27

    Failed to connect to MySQL: No connection could be made because the target machine actively refused it.

    Warning: mysqli::prepare(): Couldn’t fetch mysqli in
    C:\xampp\htdocs\HeroApi\includes\DbOperation.php on line
    27

    Fatal error: Uncaught Error: Call to a member function bind_param() on null in C:\xampp\htdocs\HeroApi\includes\DbOperation.php:28
    Stack trace:
    #0 C:\xampp\htdocs\HeroApi\v1\Api.php(59): DbOperation->createHero(‘wolverine’, ‘Logan’, ‘5’, ‘Justice League’)
    #1 {main}
    thrown in
    C:\xampp\htdocs\HeroApi\includes\DbOperation.php on line
    28

    this is my error sir, already subscribed at your youtube channel sir, please help. thankyou.

    Reply
  15. hi bilal thank you for this tutorials. dear bilal i got some issues :
    java.net.SocketTimeoutException: failed to connect to /192.168.0.108 (port 80) after 15000ms

    please help me out thanks in advance

    Reply
  16. Everything is fine, no error looking to my app. but from emulator I cant create Heros, and not showing the heroes list in the list view and also not posting data to my database

    Reply
  17. Wow I found you tutorial to be very simple an step-by-step. Good job. How ever I suffer from a developmental brain damage and am disabled where even simple thing scan be very confusing to work out in practical applications. I I were you give you some money could you help me to build a fairly simple crud reader app for my website which is also for the disabled and home bound. Thank you so very much for you efforts here.

    Reply
  18. hi bilal thank you for this tutorials. u inspire me to learn more android program.

    can i add edittext “Textwatcher” to this program ? what should i do to implement the code to make edittext “Textwatcher”. thanks in advance

    Reply
  19. How do I create an ADMIN PANEL, When ever admin does those actions, all users must get a notification may be a text message, or email or simple push up notification. in android studio

    Reply
  20. Hello…..great tutorial…. but Add and update doesnt work for me… some problem in POST metod…can you helpme?

    Reply
  21. Hey, great guide, I just have a question. I would like to store the data downloaded from database somewhere inside the application as a variable so I could use it later.
    I tried to read the code and also debug to see which variable is used to get data from server (php) to the actual layouts but I couldn’t find that and I have no idea where to look at. Any advice? 🙂

    Reply
  22. Hi Belal, thank you very much for your hard work whit all tutorials. I’ve have a question about the search operation. I have updated api.php and dboperations.php by adding a search function in order to select the heroes by ID. How can I implement a search field in the android application in order to display the herodisplay them in editable text fields?
    Can you please help me in order to undestand how to?
    Thanks you!
    Regards

    Reply
  23. Great work. Well done. I want to add searchHero() method to display a single record where id is provided.
    here is my code
    // DbOperation.php
    function searchHero($id){

    $stmt = $this->con->prepare(“SELECT * FROM heros where id=?”);
    $stmt->bind_param(“fk_s”, $id);
    if($stmt->execute())
    return true;
    return false;
    }
    //Api.php
    case ‘searchHero’:
    $db = new DbOperation();
    $result = $db->searchMedicineDetailsAmharic($_POST[‘id’]);
    if($result){
    $response[‘error’] = false;
    $response[‘message’] = ‘Medicine Searched successfully’;
    $response[‘medicines’] = $db->getHero();
    }else{
    $response[‘error’] = true;
    $response[‘message’] = ‘Some error occurred please try again’;
    }
    break;

    But when I call the funtion, it does not retrieve the result
    {“error”:true,”message”:”Some error occurred please try again”}

    Please help.

    Reply
  24. Super Tutorial Belal Khan, even though I was looking for a tutorial that would work without using volley, retrofit or apache.
    Thank you so much.
    Are there any examples with dowload and image upload without the use of libraries?
    What is the advantage of using third-party libraries?
    I’m new to the programming world, especially android, sorry if the question was stupid.
    I wait, and thank you again.
    (Used by Google Translate)

    Reply
  25. Hello thank you very much for this great tutorial
    When i test with an emulator it works very well but if i connect my android it does’nt work, please help me

    Reply
  26. Very useful tutorial.
    It explains why everything is being done, rather than just presenting a bunch of code!
    I shall let you know how my App comes along!

    Reply
  27. Hi bilal.. Above code not working in my case when I connected my real device with USB and trying to get expected output but not showing anything after clicking on Add button.. Looking forward your positive response..

    Reply
  28. Thank You Very Much for this tutourial, Thumbs Up and keep it up, We strongly follow your tuts….

    Just one question, how can I modify such code to save the data of these heroes for offline viewing (My Aim is to have an app that synchs MySQL and SQLite Database in both directions) like what Microsoft ToDo App does.

    Can You Help me

    Reply
  29. Thank you! It’s very clear for understanding. But this method is working for API 27. For API 28 I have some error with connection with database. Could you help me? I can give you more info about my problem.

    Reply
  30. Hi..thank you for this tut. It has really helped me.
    Though mine is a quick one. I have tested the endpoints with postman and they are all well.
    Create is working perfectly on android.

    The Problem is am not able to perform a GET request from server to the listview. on postman get request is working perfectly . WHille on my device it’s not.

    I have gone the extra mile of downloading the source code but it’s not populating the listview. Could the problem be with my phone or what?

    Reply
  31. Hi thank you for the tutorial. It really helped me.
    Though am having this issue of postman is working perfectly with CRUD service, and am able to create(POST) from android studio.

    The funny thing is the data list is not populating on my device even with the source code.

    What might be wrong?

    Reply
  32. Question, Can I use this approach if my database is uploaded in a web server? If yes then how? Should I just change the base url to the url of the web server where my database and .php files are located? Thanks.

    Reply
  33. Awesome!

    Very precise and step by step guide. Even those with a limited knowledge of android application can create this app.

    Reply
  34. thank you very much Belal for this very accurate tutorial.

    One thing I will add, in case the app is not working or it is loading for a long time, is make an inbound rule on the Windows firewall Advanced Settings for httpd.exe (in my case the location of the executable is , which is the actual Apache server in case you are accessing from local area network. Also on the Advanced tab check Private under Profiles to be able to access the web server over the LAN from this Android example.

    Again, thank you.

    Reply
  35. Good day sir, i have this error while creating a hero

    error: cannot find symbol
    RequestHandler requestHandler = new RequestHandler();

    i badly need your help sir, Thanks!

    Reply
  36. I need help!
    My API does not work! It used to work very good, but one day all of my API”s gave me errormessage.

    Is there somebody who can help me?

    This is the error that i get, and again… it used to work…

    {“error”:true,”message”:”Invalid API Call”}

    Reply

Leave a Comment