How to read data from online database on Android

A big piece of applications you are going to create as Android developer will contain at least some kind of integration with an online database.  As I am an Android developer and I struggled a lot in the past to make things work properly I will show you a very simple way to do it.

We are going to consider that you have an online server (real server of with hosting services), but if you don’t have one you can use the example script running on our server, just to get familiar with the concept.

Ok, so the first thing you have to do is to create a database. You can do it using phpMyAdmin (you can find it on the cPanel of your hosting service website) or using raw command line (if you have access).

In this example, we are gonna do it via phpMyAdmin. Click on SQL tab and then create a new database with the command shown in the picture bellow.

 

Screenshot_2017-08-14_18-24-32

If you get an error message that probably means the you have to create the database from cPanel > “MySQL databases” menu.

The next  thing you have to do of cource is to create your database. We created a simple one with just a name (as a varchar(100)) and a number as you can see below.

rect

tabke

db

When your database is ready to use, it’s time for the  php part. You need to create a simple php to access the database and print the result with JSON format.  You can place the php script wherever you want. The “home” directory is the public_html. That mean that if you put it there you can access it as “www.yourdomain.com/the_script”. If you place it in folders you need the whole path.

We created a simple script that calls the “select * from NameAndNumber” and print the result. The code is very simple and you can review it below.

<?php
 
$servername = "your_server_name_here";
$username = "your_user_name_here";
$password = "your_password_here";
 
$con = mysql_connect($servername, $username, $password);
 
if ( !$con) {die('Could not connect: ' .mysql_error()); }
 
mysql_select_db("showme25_test_json", $con);
 
$result = mysql_query("select * from NameAndNumber; ");
 
while($row = mysql_fetch_assoc($result)) {
        $output[]=$row;
}
 
print (json_encode(array('results' => $output)));
 
mysql_close($con);
 
 exit(1);
?>

You can see the results of that on https://showmeyourcode.org/test.php  (because we placed the script on “public_html” folder.

resJSON

Now, if you want to use a “where” statement on your mysql query you have to pass a variable to the script. You can do this with $_GET.

Take a look at the code below first:

<?php
 
$servername = "your_server_name_here";
$username = "your_user_name_here";
$password = "your_password_here";
 
$numb = $_GET['num'];
 
$con = mysql_connect($servername, $username, $password);
 
if ( !$con) {die('Could not connect: ' .mysql_error()); }
 
mysql_select_db("showme25_test_json", $con);
 
$result = mysql_query("select * from NameAndNumber where number=$numb; ");
 
while($row = mysql_fetch_assoc($result)) {
        $output[]=$row;
}
 
print (json_encode(array('results' => $output)));
 
mysql_close($con);
 
 exit(1);
?>

 

First you need to read a number from the outside world (from you app for example). You need to name this variable “num” as you define here:

$numb = $_GET[‘num’];

Then you can use the variable $numb in your code. Here you just return the name and the number where the number equals the $numb value. For example if you give the number 30 you get “TinyRick”.

You can define the first variable using the “?” symbol after the “php” as you can see in the image below. If you want to pass an extra variable you need to use “&”.  An example could be:
“showmeyourcode.org/test2.php?num=30&other_var=’MillionAnts”.

tinyPick

In the same way you can create Insert, Delete, Drop, Update etc queries.

Now let’s dive on the Android part.

The first thing that you have to do ( after creating a new project) is to add the Volley library to your project. You can do this simply by adding the line bellow on app’s build.gradle on the dependencies codeblock:

compile ‘com.android.volley:volley:1.0.0’

Volley is on JCenter, so this is the only thing that you have to do!

Also you need to add the Internet permissions in the Manifest.

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />

In the demo app  that we created (you can see the  code in the end of the post), we created two buttons. The first one will call the test.php and show the results and the second one the test2.php. We will not cover the code the listeners, it’s pretty basic and self -explainable. We will focus on the functions that the listeners call.

The first listener calls the test.php with the function JSONProccess1 and saves the results in allNames and allNumbers arraylists. After that we print it to the screen using a simple toast as you can see in the code below.

Call of the function (first button) :

JSONProccess1("https://showmeyourcode.org/test.php");

private void JSONProccess1  ( String loginURL ) {
 
        requestQueue = Volley.newRequestQueue(this);
        // output = (TextView) findViewById(R.id.jsonData);
        JsonObjectRequest jor = new JsonObjectRequest(Request.Method.GET, loginURL, null,
                new Response.Listener<JSONObject>() {
                    @Override
                    public void onResponse(JSONObject response) {
 
                        try {
 
                            JSONArray ja = response.getJSONArray("results");
 
                            for (int i = 0; i < ja.length(); i++) {
 
                                JSONObject jsonObject = ja.getJSONObject(i);
 
                                String name = jsonObject.getString("name");
                                String number = jsonObject.getString("number");
 
                                allNames.add(name);
                                allNumbers.add(number);
                            }
 
                            for (int i=0; i< allNumbers.size(); i++ ) {
                                Toast.makeText(MainActivity.this, allNames.get(i) + ", " + allNumbers.get(i), Toast.LENGTH_SHORT).show();
                            }
 
                        } catch (JSONException e) {
                            e.printStackTrace();
                        }
                    }
                },
                new Response.ErrorListener() {
                    @Override
                    public void onErrorResponse(VolleyError error) {
                        Log.e("Volley", "Error");
 
                    }
                }
        );
        requestQueue.add(jor);
 
 
    }

 

On the second button we are gonna call test2.py. Here, we suppose that we know that the result will be just one, so we don’t have to  use a for loop. We just use getJSONObject(0) to get the first. Everything else is straight forward.

Call of the function (second button) :

JSONProccess2("https://showmeyourcode.org/test2.php?num=30");

private void JSONProccess2  ( String loginURL ) {
 
        requestQueue = Volley.newRequestQueue(this);
        // output = (TextView) findViewById(R.id.jsonData);
        JsonObjectRequest jor = new JsonObjectRequest(Request.Method.GET, loginURL, null,
                new Response.Listener<JSONObject>() {
                    @Override
                    public void onResponse(JSONObject response) {
 
                        try {
 
                            JSONArray ja = response.getJSONArray("results");
 
 
 
                            JSONObject jsonObject = ja.getJSONObject(0);
 
                            String name = jsonObject.getString("name");
                            String number = jsonObject.getString("number");
 
                            Toast.makeText(MainActivity.this, name + ", " + number , Toast.LENGTH_SHORT).show();
 
                        } catch (JSONException e) {
                            e.printStackTrace();
                        }
                    }
                },
                new Response.ErrorListener() {
                    @Override
                    public void onErrorResponse(VolleyError error) {
                        Log.e("Volley", "Error");
 
                    }
                }
        );
        requestQueue.add(jor);
 
 
    }

You can read the full code clicking the button bellow! Hope that was helpful.

Get source code

 

Facebook Profile photo

Studying in Computer Engineering & Informatics Department. Member of the fedora community. Interested in mobile apps and machine learning.

Leave a Reply

Your email address will not be published. Required fields are marked *