Get data from PHP and MySQL in Android Java

In this tutorial, we will teach you how you can get data from database using PHP and MySQL in Android Java. We will be using Volley for sending HTTP request.

Following will be the structure of users table from where we will fetch data. You can add more fields as per your need. Important thing here is auto-increment primary key ID:

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

We will be sending an HTTP request to a PHP server, here it is it’s code:

get-data.php

<?php

    $userId = $_POST["userId"];
    $conn = mysqli_connect("localhost", "root", "root", "tutorials");

    $sql = "SELECT * FROM `users` WHERE `id` = '" . $userId . "'";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0)
    {
        $row = mysqli_fetch_object($result);

        echo json_encode(array(
            "status" => "success",
            "message" => "Successfully logged in",
            "user" => $row
        ));
    }
    else
    {
        echo json_encode(array(
            "status" => "error",
            "message" => "User not found"
        ));
    }
    exit();

?>
  1. First we are getting userId which will be sent by android app using POST method.
  2. Then we are connecting with MySQL database using host, username, password and database name respectively.
  3. Then we are fetching data from users table using his ID.
  4. Check if record does not exists, then simply send an error message as response. All responses must be in JSON string.
  5. If exists, then simply fetch that row, and send in response. That will be received by android.

Now include Volley in your android project’s app/build.gradle:

dependencies {
    ...

    // To convert JSON string into Java class object
    implementation 'com.google.code.gson:gson:2.8.5'

    // To send an HTTP request to PHP server
    implementation 'com.android.volley:volley:1.1.1'
}

First, we need to define the structure of data received from server. Create a class in your android project’s java package named UserModel.java, you can add more fields as per need:

public class UserModel {

    private String id;
    private String name;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

Now simply send an HTTP request using Volley and convert the response into this UserModel class:

String url = "http://localhost/tutorials/get-data.php";
// Instantiate the RequestQueue.
RequestQueue queue = Volley.newRequestQueue(getApplicationContext());

// Request a string response from the provided URL.
StringRequest stringRequest = new StringRequest(Request.Method.POST, url,
        new Response.Listener<String>() {
            @Override
            public void onResponse(String response) {

                try {
                    JSONObject responseJson = new JSONObject(response);
                    String status = responseJson.getString("status");
                    String message = responseJson.getString("message");

                    if (status.contains("error")) {
                        Toast.makeText(getApplicationContext(), message, Toast.LENGTH_LONG).show();
                    } else {
                        UserModel userModel = new Gson().fromJson(responseJson.getString("user"), UserModel.class);

                        // do whatever you want with userModel
                    }
                } catch (JSONException e) {
                    e.printStackTrace();
                }
            }
        }, new Response.ErrorListener() {
    @Override
    public void onErrorResponse(VolleyError error) {
        error.printStackTrace();
        Toast.makeText(getApplicationContext(), error.getLocalizedMessage(), Toast.LENGTH_LONG).show();
    }
}) {
    @Override
    protected Map<String, String> getParams() {
        Map<String, String> params = new HashMap<>();
        params.put("userId", "1");

        return params;
    }

    @Override
    public Map<String, String> getHeaders() throws AuthFailureError {
        Map<String, String> params = new HashMap<>();
        params.put("Content-Type", "application/x-www-form-urlencoded");
        return params;
    }
};

// To prevent timeout error
stringRequest.setRetryPolicy(new DefaultRetryPolicy(50000, DefaultRetryPolicy.DEFAULT_MAX_RETRIES, DefaultRetryPolicy.DEFAULT_BACKOFF_MULT));

// Add the request to the RequestQueue.
stringRequest.setShouldCache(false);
queue.add(stringRequest);
  1. We are sending request to get-data.php file.
  2. The method should be POST. We are sending userId parameter and sending the value in it. You might be storing this ID in shared preference. Learn how to save values in Shared Preference from here.
  3. When the response is received, we are simply converting that into JSON. Getting status and message strings.
  4. If the status is “error”, then simply display a message in Toast.
  5. Then convert the “user” key into UserModel class object and save it in object named userModel.
  6. Now you can use that object however you want.

Now that you have learned to fetch data from database using PHP and MySQL and convert that into Android Java class object, learn how to save the data from android to PHP and MySQL.

Get data from database using AJAX, Javascript, PHP, MySQL

By the end of this tutorial, you will be able to get data from database using AJAX using simple Javascript (no jQuery). Benefits of using AJAX are:

  1. Page loads faster as there will be no PHP script running during page load.
  2. If you want to change data, you can do that easily without having to refresh the page.
  3. You can show the same data on multiple pages, without having to rewrite the query. Just call the same javascript function.

Where AJAX is used ?

Google Earth

When you scroll in Google Earth, an AJAX request is sent to get the images of new location.

Facebook

When you scroll at the end of page in Facebook, an AJAX request is sent to get the older posts.

Charts & Graphs

In charts, when you change the date value, the charts has been updated to selected date via AJAX.

We are going to show records of employees from database table. We are using database named classicmodel, it will be included in the source files.

Create an HTML table

First we are going to create a file to show the data from database called index.php

Create a simple HTML table and give a unique ID to <tbody> tag

<table>
    <tr>
	<th>First name</th>
	<th>Last name</th>
	<th>Job title</th>
    </tr>

    <tbody id="data"></tbody>
</table>

Call AJAX to get data from database

To call an AJAX to get the data, first create an built-in XMLHttpRequest object and save in variable ajax.

<script>
    var ajax = new XMLHttpRequest();
</script>

Now call open(method, url, async) function from ajax object.

ajax.open("GET", "data.php", true);
  • First parameter is the method of request GET or POST.
  • Second is the name of file from where to get data. In this case data.php which will be created in next step.
  • Third is a boolean, whether the request is asynchronous or not. true for asynchronous. Asynchronous requests does not hang up the browser. Syncronized requests may block the browser if the script at data.php is very time-taking.

You can also send headers with your request using the following methods:

ajax.setRequestHeader("Accept", "application/json")
ajax.setRequestHeader("Authorization", "Bearer {token}")

You can pass as many headers as you want by calling the method setRequestHeader multiple times.

Call the send() function from ajax object to actually call an AJAX request. It accepts no parameter.

ajax.send();

ajax object has multiple functions that are called once the status of request is changed. For example, when server receives the request, if server does not exists, if server failed to respond etc. All these callbacks are received in a function called onreadystatechange.

ajax.onreadystatechange = function() {
    //
};

Now we have to respond only if the request is successful. In this onreadystatechange function, we have 3 variables readyState, status and responseText.

  • readyState will have value 4 when the request is done/completed.
  • status will have value 200 when everything goes right.
ajax.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
    	console.log(this.responseText);
    }
};

Get data from MySQL database using PHP

Now create a new file named data.php and open it in code editor. First, you have to connect with database:

<?php
$conn = mysqli_connect("localhost", "root", "", "classicmodels");
  • root is the name of database user.
  • Third parameter is the password of database. For XAMPP/WAMP, it will be an empty string.
  • classicmodels is the name of database.

Now, write your query to get the records from database. In my case, I am just getting all employees from database table named employees:

$result = mysqli_query($conn, "SELECT * FROM employees");

Create an array and loop through all database records returned from previous query, and save those records in that array:

$data = array();
while ($row = mysqli_fetch_object($result))
{
    array_push($data, $row);
}

Now, whatever you echo will returned to the AJAX callback function in index.php file. In case of an array, you have to convert the array into JSON format and then echo it.

echo json_encode($data);
exit();
  1. exit(); will prevent the code from further executing.

    At this point, if you run the index.php file, you will see similar to the following in your browser console:

Now, go back to index.php file and convert this JSON string back into an array:

ajax.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
    	console.log(this.responseText);
    	var data = JSON.parse(this.responseText);
    	console.log(data);
    }
};

JSON string should now be converted into an Javascript object named data.If you run index.php now and see the browser console, you will see something similar to the following:

json-to-object

Show data in HTML table

You got the data, now you just have to append it in table. Create an empty string variable named html:

var html = "";

Loop through all employees list:

for(var a = 0; a < data.length; a++) {
    //
}

Inside the loop, append the data in html variable:

for(var a = 0; a < data.length; a++) {
    var firstName = data[a].firstName;
    var lastName = data[a].lastName;
    var jobTitle = data[a].jobTitle;

    html += "<tr>";
        html += "<td>" + firstName + "</td>";
        html += "<td>" + lastName + "</td>";
        html += "<td>" + jobTitle + "</td>";
    html += "</tr>";
}

After the loop, append the html variable in <tbody> tag:

document.getElementById("data").innerHTML += html;

Now, your index.php file should look like this:

<table>
    <tr>
	<th>First name</th>
	<th>Last name</th>
	<th>Job title</th>
    </tr>

    <tbody id="data"></tbody>
</table>

<script>
    var ajax = new XMLHttpRequest();
    ajax.open("GET", "data.php", true);
    ajax.send();

    ajax.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            var data = JSON.parse(this.responseText);
            console.log(data);

            var html = "";
            for(var a = 0; a < data.length; a++) {
                var firstName = data[a].firstName;
                var lastName = data[a].lastName;
                var jobTitle = data[a].jobTitle;

                html += "<tr>";
                    html += "<td>" + firstName + "</td>";
                    html += "<td>" + lastName + "</td>";
                    html += "<td>" + jobTitle + "</td>";
                html += "</tr>";
            }
            document.getElementById("data").innerHTML += html;
        }
    };
</script>

And your data.php file should be like this:

<?php
$conn = mysqli_connect("localhost", "root", "", "classicmodels");
$result = mysqli_query($conn, "SELECT * FROM employees");

$data = array();
while ($row = mysqli_fetch_object($result))
{
    array_push($data, $row);
}

echo json_encode($data);
exit();

Run the index.php file and you will see your data will be displayed via AJAX. So that’s how you can get data from the database using AJAX and display it in an HTML table.

There is more

This is just to get the data from the database. Follow this tutorial to learn complete CRUD (Create, Read, Update and Delete) operation in AJAX.

[wpdm_package id=’93’]