Hey Technoz, In this post, we will see the how to handle remote database like MySQL from the android app with PHP language as a server side language giving a JSON response. However, this Android PHP MySQL tutorial has two parts: one is server side coding, and another is client side coding.
Android PHP MySQL
Building Custom API
At first, we have to build the custom API (Application Programming Interface) for our application to perform tasks. Apparently, The app will send the request to the API, the API will interact with the database, perform necessary task, and return the results (JSON response) back to the application. Thus, the following figure will clear how the system will work.
Hope you get the idea from above figure. At first, We will first build the server side API.
Building Server Side API
Creating Database
We have to build a table required in this Android PHP MySQL tutorial. Use the below SQL query to create the required table.
CREATE TABLE IF NOT EXISTS `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `price` varchar(100) NOT NULL, `description` varchar(100) NOT NULL, PRIMARY KEY (`id`) )
Creating PHP files
Certainly, the php files does the work of server side with interacting the database. To clarify, I hope you are familiar working with php. If not, please see the series of php tutorials first. I am using Wampserver for this project. So create folder newprojectapp in www directory. At first, we will create the connection file configuration.php.
<?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "newdb"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); ?>
As a result, we have the table of product details in MySQL database, we will first write the code for adding the new product entry. So create file create_product.php and paste the following code in it.
<?php
//getting the database connection
require_once 'configuration.php';
//an array to display response
$response = array();
if($_POST['name'] && $_POST['price'] && $_POST['description']){
$name = $_POST['name'];
$price = $_POST['price'];
$description = $_POST['description'];
$stmt = $conn->prepare("INSERT INTO `products`(`name`, `price`, `description`) VALUES (?,?,?)");
$stmt->bind_param("sss",$name,$price,$description);
if($stmt->execute() == TRUE){
$response['error'] = false;
$response['message'] = "product created successfully!";
} else{
$response['error'] = true;
$response['message'] = "failed\n ".$conn->error;
}
} else{
$response['error'] = true;
$response['message'] = "Insufficient parameters";
}
echo json_encode($response);
For instance, The above file accepts the product name, price and description and adds an entry into the database. Therefore, we are here using array to show data and passing JSON response as an array. Similarly, the same mechanism is applied to all files.
Furthermore, create a file get_product_details.php to perform read operation with the following code in android php mysql.
<?php
//getting the database connection
require_once 'configuration.php';
//an array to display response
$response = array();
if($_POST['id']){
$id = $_POST['id'];
$stmt = $conn->prepare("SELECT name,price,description FROM products WHERE id = ?");
$stmt->bind_param("s",$id);
$result = $stmt->execute();
if($result == TRUE){
$response['error'] = false;
$response['message'] = "Retrieval Successful!";
$stmt->store_result();
$stmt->bind_result($name,$price,$description);
$stmt->fetch();
$response['name'] = $name;
$response['price'] = $price;
$response['description'] = $description;
} else{
$response['error'] = true;
$response['message'] = "Incorrect id";
}
} else{
$response['error'] = true;
$response['message'] = "Insufficient Parameters";
}
echo json_encode($response);
?>
In order to understand update operation, we will update the price of product with specific id. Lets create file update_price.php with the following code.
<?php
//getting the database connection
require_once 'configuration.php';
//an array to display response
$response = array();
if($_POST['id'] && $_POST['price']){
$id = $_POST['id'];
$price = $_POST['price'];
$stmt = $conn->prepare("UPDATE products SET price = ? WHERE id = ?");
$stmt->bind_param("ss",$price, $id);
if($stmt->execute() == TRUE){
$response['error'] = false;
$response['message'] = "Price Updated Successfully!";
} else{
$response['error'] = true;
$response['message'] = "Incorrect id";
}
} else{
$response['error'] = true;
$response['message'] = "Insufficient Parameters";
}
echo json_encode($response);
?>
Finally, the delete operation in this Android PHP MySQL tutorial can be performed from following code. Create php file delete_product.php and paste following code in it.
<?php
//getting the database connection
require_once 'configuration.php';
//an array to display response
$response = array();
if($_POST['id']){
$id = $_POST['id'];
$stmt = $conn->prepare("DELETE FROM products WHERE id = ?");
$stmt->bind_param("s",$id);
if($stmt->execute()){
$response['error'] = false;
$response['message'] = "Product Deleted Successfully!";
} else{
$response['error'] = true;
$response['message'] = "Product Deleted Failed!";
}
} else{
$response['error'] = true;
$response['message'] = "Insufficient Parameters";
}
echo json_encode($response);
?>
Lets Test the working
Finally, we have just completed the server side part of Android PHP MySQL tutorial. However, we need to check whether the above code is working as expected. I am here using ARC (Advanced Rest Client) chrome extension to send requests with POST parameters. But you can choose the same, or any other of your choice. Lets try to send requests on all files with the POST data parameters as needed and see the JSON response. See the screenshots below.
Some points about URLs
In this Android PHP MySQL tutorial, the localhost in the url will not work. Thus, we have to find out the ip address of PC and put it in the place of localhost. To find ip address, open command prompt and type “ipconfig” and press enter. You will get the ip. Copy it and replace in the URLs in your project.
The URLs we are using in this tutorial are specific purpose-related with each sending specific POST parameters. See the table below for clarification.
| URL | Purpose | POST Parameters |
|---|---|---|
| http://192.168.43.17/newprojectapp/create_product.php | Create new product | name, price, description |
| http://192.168.43.17/newprojectapp/get_product_details.php | Read product details | id |
| http://192.168.43.17/newprojectapp/update_price.php | Update product details | id, price |
| http://192.168.43.17/newprojectapp/delete_product.php | Delete product details | id |
Building Client Android App
Subsequently, we are going to build a client side android app. We are using HTTP POST requests. So, open your app-level build.gradle file and paste the following line in ‘android’ component.
android {
compileSdkVersion 27
defaultConfig {
applicationId "net.softglobe.tutorials"
minSdkVersion 16
targetSdkVersion 27
versionCode 1
versionName "1.0"
testInstrumentationRunner "android.support.test.runner.AndroidJUnitRunner"
//add this line
useLibrary 'org.apache.http.legacy'
}
}
Add the Java Backend Code
To sum up, we have only single activity to perform all CRUD operations in android php mysql. We will build a simple java class which will handle the requests and JSON response. create a new java class RequestHandler.java and paste the following code in it.
package net.softglobe.tutorials;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;
import javax.net.ssl.HttpsURLConnection;
public class RequestHandler {
//this method will send a post request to the specified url
//in this app we are using only post request
//in the hashmap we have the data to be sent to the server in keyvalue pairs
public String sendPostRequest(String requestURL, HashMap<String, String> postDataParams) {
URL url;
StringBuilder sb = new StringBuilder();
try {
url = new URL(requestURL);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setReadTimeout(15000);
conn.setConnectTimeout(15000);
conn.setRequestMethod("POST");
conn.setDoInput(true);
conn.setDoOutput(true);
OutputStream os = conn.getOutputStream();
BufferedWriter writer = new BufferedWriter(
new OutputStreamWriter(os, "UTF-8"));
writer.write(getPostDataString(postDataParams));
writer.flush();
writer.close();
os.close();
int responseCode = conn.getResponseCode();
if (responseCode == HttpsURLConnection.HTTP_OK) {
BufferedReader br = new BufferedReader(new InputStreamReader(conn.getInputStream()));
sb = new StringBuilder();
String response;
while ((response = br.readLine()) != null) {
sb.append(response);
}
}
} catch (Exception e) {
e.printStackTrace();
}
return sb.toString();
}
//this method is converting keyvalue pairs data into a query string as needed to send to the server
private String getPostDataString(HashMap<String, String> params) throws UnsupportedEncodingException {
StringBuilder result = new StringBuilder();
boolean first = true;
for (Map.Entry<String, String> entry : params.entrySet()) {
if (first)
first = false;
else
result.append("&");
result.append(URLEncoder.encode(entry.getKey(), "UTF-8"));
result.append("=");
result.append(URLEncoder.encode(entry.getValue(), "UTF-8"));
}
return result.toString();
}
}
Meanwhile, Lets discuss some important points in above code:
We have created a method which accepts the URL and the HashMap parameters which are to be sent along with the URL. (HashMap is a Map based collection class that is used for storing Key & value pairs as HashMap<Key, Value>.) Likewise, We are here using HTTPUrlConnection class to request a url. The method getPostDataString is getting the parameters to be sent and putting them in a single string. After the parameters are sent on specific URL, we check if the response is successful, read it, combine the JSON response as a single string and return to the calling function.
Designing the Layout
Furthermore, we will move towards the activity in android php mysql. To create a front view, create a new activity, if you don’t have already and paste the following code in the layout xml file. In my case, it is activity_main.xml.
<?xml version="1.0" encoding="utf-8"?>
<android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<ScrollView
android:layout_width="match_parent"
android:layout_height="wrap_content">
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="vertical"
android:layout_margin="10sp">
<TextView
android:layout_width="match_parent"
android:layout_height="12sp"
android:background="@color/colorAccent"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Add Product"
android:gravity="center_horizontal"
android:textSize="20sp"
android:textStyle="bold"/>
<EditText
android:id="@+id/name"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="name"/>
<EditText
android:id="@+id/price"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="price"/>
<EditText
android:id="@+id/desc"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="description"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Submit"
android:onClick="add_prod"/>
<TextView
android:layout_width="match_parent"
android:layout_height="12sp"
android:background="@color/colorAccent"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Show Product"
android:gravity="center_horizontal"
android:textSize="20sp"
android:textStyle="bold"/>
<LinearLayout
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:gravity="center_horizontal">
<EditText
android:id="@+id/id"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:hint="Enter id"/>
<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Go"
android:onClick="show_prod"/>
</LinearLayout>
<TextView
android:visibility="gone"
android:id="@+id/showname"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Product Name"
android:textSize="18sp"/>
<TextView
android:visibility="gone"
android:id="@+id/showprice"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Product Price"
android:textSize="18sp"/>
<TextView
android:visibility="gone"
android:id="@+id/showdesc"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Product Description"
android:textSize="18sp"/>
<TextView
android:layout_width="match_parent"
android:layout_height="12sp"
android:background="@color/colorAccent"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Update Product Price"
android:gravity="center_horizontal"
android:textSize="20sp"
android:textStyle="bold"/>
<EditText
android:id="@+id/id_update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter id"/>
<EditText
android:id="@+id/price_update"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter New Price"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Upadte Price"
android:onClick="update_price"/>
<TextView
android:layout_width="match_parent"
android:layout_height="12sp"
android:background="@color/colorAccent"/>
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Delete Product"
android:gravity="center_horizontal"
android:textSize="20sp"
android:textStyle="bold"/>
<EditText
android:id="@+id/id_delete"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Enter id"/>
<Button
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="Delete Product"
android:onClick="delete_product"
android:layout_marginBottom="20sp"/>
</LinearLayout>
</ScrollView>
</android.support.constraint.ConstraintLayout>
The above layout file has components required to fulfill our purpose of this android php mysql tutorial. Lets cover the java part in MainActivity.java.
package net.softglobe.tutorials;
import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import org.json.JSONException;
import org.json.JSONObject;
import org.w3c.dom.Text;
import java.util.HashMap;
public class MainActivity extends AppCompatActivity {
private EditText etName;
private EditText etPrice;
private EditText etDesc;
private EditText etId;
private EditText etIdUpdate,etPriceUpdate;
private EditText etIdDelete;
private TextView tvName, tvPrice, tvDesc;
//replace the ip addresses in following urls with yours
public static final String URL_ADD_PROD = "http://192.168.43.17/newprojectapp/create_product.php";
public static final String URL_SHOW_PROD = "http://192.168.43.17/newprojectapp/get_product_details.php";
public static final String URL_UPDT_PROD = "http://192.168.43.17/newprojectapp/update_price.php";
public static final String URL_DELETE_PROD = "http://192.168.43.17/newprojectapp/delete_product.php";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
etName = findViewById(R.id.name);
etPrice = findViewById(R.id.price);
etDesc = findViewById(R.id.desc);
etId = findViewById(R.id.id);
etIdUpdate = findViewById(R.id.id_update);
etPriceUpdate = findViewById(R.id.price_update);
etIdDelete = findViewById(R.id.id_delete);
tvName = findViewById(R.id.showname);
tvPrice = findViewById(R.id.showprice);
tvDesc = findViewById(R.id.showdesc);
}
//Add Product
public void add_prod(View view){
final String name = etName.getText().toString();
final String price = etPrice.getText().toString();
final String desc = etDesc.getText().toString();
class Product extends AsyncTask<Void, Void, String>{
ProgressDialog pdLoading = new ProgressDialog(MainActivity.this);
@Override
protected void onPreExecute() {
super.onPreExecute();
//this method will be running on UI thread
pdLoading.setMessage("\tLoading...");
pdLoading.setCancelable(false);
pdLoading.show();
}
@Override
protected String doInBackground(Void... voids) {
//creating request handler object
RequestHandler requestHandler = new RequestHandler();
//creating request parameters
HashMap<String, String> params = new HashMap<>();
params.put("name", name);
params.put("price", price);
params.put("description", desc);
//returing the response
return requestHandler.sendPostRequest(URL_ADD_PROD, params);
}
@Override
protected void onPostExecute(String s) {
super.onPostExecute(s);
pdLoading.dismiss();
try {
//converting response to json object
JSONObject obj = new JSONObject(s);
//if no error in response
if (!obj.getBoolean("error")) {
Toast.makeText(getApplicationContext(), obj.getString("message"), Toast.LENGTH_LONG).show();
}
} catch (JSONException e) {
e.printStackTrace();
Toast.makeText(MainActivity.this, "Exception: "+e, Toast.LENGTH_LONG).show();
}
}
}
Product prod_exec = new Product();
prod_exec.execute();
}
//Show Product
public void show_prod(View view){
final String id = etId.getText().toString();
class show_prod extends AsyncTask<Void, Void, String>{
ProgressDialog pdLoading = new ProgressDialog(MainActivity.this);
@Override
protected void onPreExecute() {
super.onPreExecute();
//this method will be running on UI thread
pdLoading.setMessage("\tLoading...");
pdLoading.setCancelable(false);
pdLoading.show();
}
@Override
protected String doInBackground(Void... voids) {
//creating request handler object
RequestHandler requestHandler = new RequestHandler();
//creating request parameters
HashMap<String, String> params = new HashMap<>();
params.put("id", id);
//returing the response
return requestHandler.sendPostRequest(URL_SHOW_PROD, params);
}
@Override
protected void onPostExecute(String s){
super.onPostExecute(s);
pdLoading.dismiss();
try{
//Converting response to JSON Object
JSONObject obj = new JSONObject(s);
//if no error in response
if (!obj.getBoolean("error")){
Toast.makeText(MainActivity.this, obj.getString("message"), Toast.LENGTH_LONG).show();
//Make TextViews Visible
tvName.setVisibility(View.VISIBLE);
tvPrice.setVisibility(View.VISIBLE);
tvDesc.setVisibility(View.VISIBLE);
//Set retrieved text to TextViews
tvName.setText("Name: "+obj.getString("name"));
tvPrice.setText("Price: "+obj.getString("price"));
tvDesc.setText("Description: "+obj.getString("description"));
}
} catch (Exception e ){
Toast.makeText(MainActivity.this, "Exception: "+e, Toast.LENGTH_SHORT).show();
}
}
}
show_prod show = new show_prod();
show.execute();
}
//Update Price
public void update_price(View view){
final String id = etIdUpdate.getText().toString();
final String price = etPriceUpdate.getText().toString();
class Update extends AsyncTask<Void, Void, String>{
ProgressDialog pdLoading = new ProgressDialog(MainActivity.this);
@Override
protected void onPreExecute() {
super.onPreExecute();
//this method will be running on UI thread
pdLoading.setMessage("\tLoading...");
pdLoading.setCancelable(false);
pdLoading.show();
}
@Override
protected String doInBackground(Void... voids) {
//creating request handler object
RequestHandler requestHandler = new RequestHandler();
//creating request parameters
HashMap<String, String> params = new HashMap<>();
params.put("id", id);
params.put("price",price);
//returing the response
return requestHandler.sendPostRequest(URL_UPDT_PROD, params);
}
@Override
protected void onPostExecute(String s){
super.onPostExecute(s);
pdLoading.dismiss();
try{
//Converting response to JSON Object
JSONObject obj = new JSONObject(s);
//if no error in response
if (!obj.getBoolean("error")){
Toast.makeText(MainActivity.this, obj.getString("message"), Toast.LENGTH_LONG).show();
}
} catch (Exception e ){
Toast.makeText(MainActivity.this, "Exception: "+e, Toast.LENGTH_SHORT).show();
}
}
}
Update update = new Update();
update.execute();
}
public void delete_product(View view){
final String id = etIdDelete.getText().toString();
class Delete extends AsyncTask<Void, Void, String>{
ProgressDialog pdLoading = new ProgressDialog(MainActivity.this);
@Override
protected void onPreExecute() {
super.onPreExecute();
//this method will be running on UI thread
pdLoading.setMessage("\tLoading...");
pdLoading.setCancelable(false);
pdLoading.show();
}
@Override
protected String doInBackground(Void... voids) {
//creating request handler object
RequestHandler requestHandler = new RequestHandler();
//creating request parameters
HashMap<String, String> params = new HashMap<>();
params.put("id", id);
//returing the response
return requestHandler.sendPostRequest(URL_DELETE_PROD, params);
}
@Override
protected void onPostExecute(String s){
super.onPostExecute(s);
pdLoading.dismiss();
try{
//Converting response to JSON Object
JSONObject obj = new JSONObject(s);
//if no error in response
if (!obj.getBoolean("error")){
Toast.makeText(MainActivity.this, obj.getString("message"), Toast.LENGTH_LONG).show();
}
} catch (Exception e ){
Toast.makeText(MainActivity.this, "Exception: "+e, Toast.LENGTH_SHORT).show();
}
}
}
Delete delete = new Delete();
delete.execute();
}
}
Note: In the above class, we are using the URLs to send the API request. You have to replace the IP addresses in these URLs with your device’s IP address. To know your IP address, connect your PC to internet, open command prompt, type
ipconfig/alland press enter. See the IPv4 Address and copy it. Then replace that address in the above URLs. (If you are gettingEnd of inputerror, then you must check this note)
As you see, in the above class, the functions for the CRUD operations are created and are triggered on clicking the buttons in xml file.
In each method above,we have created a private class which extends an AsyncTask. An AsyncTask is a class that allows us to perform background operations and publish results on the UI thread without having to manipulate threads and/or handlers. On button click, we have to perform some operations like getting values from EditTexts, mapping them in the HashMap, setting the TextViews, etc. we will do these things in the background. Above all, It has two implementation methods in our Android PHP MySQL tutorial, described as follows:
onPreExecute(): It performs the task on UI thread. We here start the flashing of ProgressBar.
doInBackground(Params… params): This method starts as soon as onPreExecute() is finished. It performs the major tasks. In this case, it attaches parameters to HashMap and we then call the sendPostRequest() method in RequestHandler class with URL and HashMap parameters. It returns the response to the onPostExecute() method.
onPostExecute(Result result): It receives the response from doInBackground() method and executes again on UI thread. Here we do the task of dismissing the ProgressBar.
However, we are getting our JSON response from server, in the onPostExecute() method, we use the JSONObject class and retrieve the Strings from the response using getString() method.
Finally, after the completion of class, we create the object of class and execute it.
Add Internet Permission
As we are using the internet for our project, so we have to add the internet permission in AndroidManifest.xml file as follows:
<uses-permission android:name="android.permission.INTERNET" />
Put this line just before starting of <application> tag.
Lets try running our creation!
Note: Please make sure that you have changed the IP address in your url with your own IP address. Also note that while running the project, you need to have WAMP or XAMPP server running and your PC should be connected to the internet in order to obtain the IP address.
Thus, We have completed the Android PHP MySQL tutorial. Now try running the app. If everything goes well, then you will see a success screenshots of the app as follows.
So far we are done with everything. Hope you understood everything. Alternatively, If you want to jump directly to the implementation, you can download the source code from the below link.
Download Source Code
If you have any problem implementing above tutorial, feel free to ask in the comments below. Please subscribe to our newsletter in order to receive such knowledgeable tutorials.
32 thoughts on “Android PHP MySQL CRUD operations”