AndroidCSS

Android material design Programming Blog

Android MySQL PHP: App to display Statistics based on date range

Android MySQL PHP to display statistics

With the power of three powerful words Android MySQL PHP, you can develop any Android application which involves the fetching of data from the back-end server.

If you look at the AdSense Android application from Google, you like to see., earning statistics for days, months, and years in a subsequent row. In this tutorial, we going to develop a similar kind of application which involves fetching of statistic data from MySQL PHP. Let’s get started.

Download Code From Github

Video Demo

MySQL

Structure of table tbl_revenue

CREATE TABLE IF NOT EXISTS `tbl_revenue` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `total_cost` int(5) NOT NULL DEFAULT '0',
  `discount` int(5) NOT NULL DEFAULT '0',
  `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Table tbl_revenue holds data row like this.

INSERT INTO `tbl_revenue` (`order_id`, `total_cost`, `discount`, `date_time`) VALUES
(1, 250, 10, '2016-05-29 10:51:59'),
(2, 450, 5, '2016-05-29 10:51:59'),
(3, 300, 30, '2016-04-12 00:00:00'),
(4, 150, 10, '2016-06-25 13:02:35');

PHP

config.inc.php

The below code is responsible for establishing the connection to the database. Copy this code to your blank PHP file and save it to your server directory with a name config.inc.php

Note: Don’t forget to change first four variables(the server name, username, password, and database name) in the file.

<?php

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "test_date_range";

try {
    	$connection = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    	$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    	die("OOPs something went wrong");
    }

?>

revenue-fetch.php

Let’s see the code in details.

    • You need to include above config.inc.php file in function fetch_records, as we need connection object to query the database.
    • From Andriod, we will get a POST data as position based on user selection(ex: today, yesterday, this month and so on).
    • Inside switch statement, we are determining which query string to be passed as a parameter to the fetch_records function and finally, returning a result back from the function.
    • Encode the result in JSON format and send it from PHP.
<?php

  /* Check if post is set from android and feed query to
   * function based on position value
   */

  if(isset($_POST['position']))
  {
    $output="";	
    switch($_POST['position'])
    {
	case 0: $query_str="DATE(date_time) = CURDATE()";
	$output = fetch_records($query_str);
	break;
					
	case 1: $query_str="DATE(date_time) = DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )";
	$output = fetch_records($query_str);
	break;
			
	case 2: $query_str="WEEKOFYEAR(date_time)=WEEKOFYEAR(NOW())";
	$output = fetch_records($query_str);
	break;
			
	case 3: $query_str="MONTH(date_time) = MONTH(NOW()) AND YEAR(date_time) = YEAR(NOW())";
	$output = fetch_records($query_str);
	break;
			
	case 4: $query_str="YEAR(date_time) = YEAR(CURDATE() - INTERVAL 1 MONTH)
	AND MONTH(date_time) = MONTH(CURDATE() - INTERVAL 1 MONTH)";
	$output = fetch_records($query_str);
	break;
			
	case 5: $query_str="YEAR(date_time) = YEAR(NOW())";
	$output = fetch_records($query_str);
	break;
			
	case 6: $query_str="DATE(date_time)";
	$output = fetch_records($query_str);
	break;
		 
    }
		
   header('Content-type: application/json');
   echo json_encode($output);
    
 }

 // Define function to return data from mysql	
 function fetch_records($query_line) { require('config.inc.php'); $sql = 'SELECT COUNT(order_id) as orders,SUM(total_cost) as total,  SUM(discount) as discount,(SUM(total_cost) - SUM(discount)) as net_total   FROM tbl_revenue where ' . $query_line; $stmt = $connection->prepare($sql); $stmt->execute(); $row = $stmt->fetch(PDO::FETCH_ASSOC); if($row['orders']==0) { $output=array('orders' => 0, 'total' => 0, 'discount' => 0, 'net_total' => 0); return $output; }else{ return $row; } } ?>

Android

Defining Array and Style

strings.xml

Define an array of all items where we need to populate into the spinner in strings.xml file

<resources>
    <string name="app_name">DateRangeExample</string>
    <string-array name="date_array">
        <item>Today so far</item>
        <item>Yesterday</item>
        <item>This Week so far</item>
        <item>This Month so far</item>
        <item>Last Month</item>
        <item>This Year so far</item>
        <item>LifeTime</item>
        <item>Custom</item>
    </string-array>
</resources>

selector_box.xml

      • This file has to go under res→drawable directory.
      • This selector file makes our container item edges to be the 2dp rounded corner and gives specific background color.
      • We use this file as background to container item.
<?xml version="1.0" encoding="utf-8"?>
<layer-list xmlns:android="http://schemas.android.com/apk/res/android">
    <item>
        <shape android:shape="rectangle">
            <solid android:color="#AAA"/>
            <corners android:radius="2dp" />
        </shape>
    </item>

    <item
        android:left="0dp"
        android:right="0dp"
        android:top="0dp"
        android:bottom="2dp">
        <shape android:shape="rectangle">
            <solid android:color="@android:color/white"/>
            <corners android:radius="2dp" />
        </shape>
    </item>
</layer-list>

Creating Layout File

Let’s define our application’s user interface.

activity_main.xml

Layout file for Statistics
The above image illustrates how the below code looks in design view.

At the bottom we have a Spinner and to make layout scrollable, we added each text container inside the ScrollView.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:background="#CCC">

    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="1">

    <LinearLayout
        android:orientation="vertical"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">

        <LinearLayout
            android:orientation="vertical"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:padding="20dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_marginTop="2dp"
            android:background="@drawable/selector_box_all">

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceMedium"
                android:text="Total Orders"
                android:textColor="#999"
                android:gravity="center"/>

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textSize="35dp"
                android:text="0"
                android:id="@+id/textOrders"
                android:textColor="#333"
                android:gravity="center"/>

        </LinearLayout>

        <LinearLayout
            android:orientation="vertical"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:padding="20dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_marginTop="2dp"
            android:background="@drawable/selector_box_all">

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceMedium"
                android:text="Total Amount"
                android:textColor="#999"
                android:gravity="center"/>

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textSize="35dp"
                android:text="Rs. 0"
                android:id="@+id/textTotal"
                android:textColor="#333"
                android:gravity="center"/>

        </LinearLayout>

        <LinearLayout
            android:orientation="vertical"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:padding="20dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_marginTop="2dp"
            android:background="@drawable/selector_box_all">

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceMedium"
                android:text="Total Discount"
                android:textColor="#999"
                android:gravity="center"/>

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textSize="35dp"
                android:text="Rs. 0"
                android:id="@+id/textDiscount"
                android:textColor="#333"
                android:gravity="center"/>

        </LinearLayout>

        <LinearLayout
            android:orientation="vertical"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:padding="20dp"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp"
            android:layout_marginTop="2dp"
            android:background="@drawable/selector_box_all">

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textAppearance="?android:attr/textAppearanceMedium"
                android:text="Net Total"
                android:textColor="#999"
                android:gravity="center"/>

            <TextView
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:textSize="35dp"
                android:text="Rs. 0"
                android:id="@+id/textNetTotal"
                android:textColor="#333"
                android:gravity="center"/>

        </LinearLayout>

    </LinearLayout>

    </ScrollView>

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:layout_weight="0"
        android:background="#fff"
        android:padding="10dp">

        <ImageView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:padding="5dp"
            android:src="@mipmap/ic_date_range_black_24dp" />

        <Spinner
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/spinner"
            android:padding="5dp"
            android:layout_gravity="center_horizontal" />

    </LinearLayout>

</LinearLayout>

Creating Activity File

MainActivity.java

Let’s discuss this activity in details.

      1. Initialize all variables and set Array from strings.xml to Spinner and populate the list.
      2. Based on user item selection from the Spinner, make a call to AsyncRetrieve class with parameter as item position.
      3. Make sure your URL address is accessible from a browser and define the same in AsyncRetrieve class(in place of http://192.168.1.7/). The AsyncRetrieve class is responsible for making a call to PHP and retrieving results.
      4. Parse results to JSON and populate the data to views.
package com.androidcss.daterangeexample;
import android.app.ProgressDialog;
import android.net.Uri;
import android.os.AsyncTask;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.AdapterView.OnItemSelectedListener;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

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;

public class MainActivity extends AppCompatActivity implements OnItemSelectedListener {

    // CONNECTION_TIMEOUT and READ_TIMEOUT are in milliseconds
    public static final int CONNECTION_TIMEOUT = 10000;
    public static final int READ_TIMEOUT = 15000;
    Spinner spinner;
    TextView textOrders;
    TextView textTotal;
    TextView textDiscount;
    TextView textNetTotal;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        textOrders = (TextView) findViewById(R.id.textOrders);
        textTotal = (TextView) findViewById(R.id.textTotal);
        textDiscount = (TextView) findViewById(R.id.textDiscount);
        textNetTotal = (TextView) findViewById(R.id.textNetTotal);

        spinner = (Spinner) findViewById(R.id.spinner);
        // Setup item selected listener
        spinner.setOnItemSelectedListener(this);

        // Apply Adapter for spinner
        ArrayAdapter<CharSequence> adapter = ArrayAdapter.createFromResource(this,
                R.array.date_array, android.R.layout.simple_spinner_item);
        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        spinner.setAdapter(adapter);

    }

    @Override
    public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
        new AsyncRetrieve(position).execute();
    }

    @Override
    public void onNothingSelected(AdapterView<?> parent) {

    }

    private class AsyncRetrieve extends AsyncTask<String, String, String> {

        ProgressDialog pdLoading = new ProgressDialog(MainActivity.this);
        HttpURLConnection conn;
        URL url = null;
        int position;

        public AsyncRetrieve(int position){
            this.position = position;
        }

        //this method will interact with UI, here display loading message
        @Override
        protected void onPreExecute() {
            super.onPreExecute();

            pdLoading.setMessage("\tLoading...");
            pdLoading.setCancelable(false);
            pdLoading.show();

        }

        // This method does not interact with UI, You need to pass result to onPostExecute to display
        @Override
        protected String doInBackground(String... params) {
            try {
                // Enter URL address where your php file resides
                url = new URL("http://192.168.1.7/revenue-fetch.php");

            } catch (MalformedURLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {

                // Setup HttpURLConnection class to send and receive data from php and mysql
                conn = (HttpURLConnection) url.openConnection();
                conn.setReadTimeout(READ_TIMEOUT);
                conn.setConnectTimeout(CONNECTION_TIMEOUT);
                conn.setRequestMethod("POST");

                // setDoOutput to true as we recieve data from json file
                conn.setDoInput(true);
                conn.setDoOutput(true);

                Uri.Builder builder = new Uri.Builder()
                        .appendQueryParameter("position", position + "");
                String query = builder.build().getEncodedQuery();

                OutputStream os = conn.getOutputStream();
                BufferedWriter writer = new BufferedWriter(
                        new OutputStreamWriter(os, "UTF-8"));
                writer.write(query);
                writer.flush();
                writer.close();
                os.close();
                conn.connect();

            } catch (IOException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }

            try {

                int response_code = conn.getResponseCode();

                // Check if successful connection made
                if (response_code == HttpURLConnection.HTTP_OK) {

                    // Read data sent from server
                    InputStream input = conn.getInputStream();
                    BufferedReader reader = new BufferedReader(new InputStreamReader(input));
                    StringBuilder result = new StringBuilder();
                    String line;

                    while ((line = reader.readLine()) != null) {
                        result.append(line);
                    }

                    // Pass data to onPostExecute method
                    return (result.toString());

                } else {

                    return ("unsuccessful");
                }

            } catch (IOException e) {
                e.printStackTrace();

            } finally {
                conn.disconnect();
            }

            return("error");

        }

        // this method will interact with UI, display result sent from doInBackground method
        @Override
        protected void onPostExecute(String result) {

            pdLoading.dismiss();
            JSONArray jArray = null;

            if(result.equals("unsuccessful"))
            {
                Toast.makeText(MainActivity.this,"Connection Problem. Http is not ok.",Toast.LENGTH_LONG).show();

            }else if(result.equals("error")){

                Toast.makeText(MainActivity.this,"Possibly Exception! Check your terminal for errors.",Toast.LENGTH_LONG).show();

            }else{

                try {
                    //Toast.makeText(MainActivity.this,result.toString(),Toast.LENGTH_LONG).show();
                    JSONObject json_data = new JSONObject(result);
                    textOrders.setText(json_data.getInt("orders") + "");
                    textTotal.setText("Rs. " + json_data.getInt("total"));
                    textDiscount.setText("Rs. " + json_data.getInt("discount"));
                    textNetTotal.setText("Rs. " + json_data.getInt("net_total"));

                } catch (JSONException e) {
                    e.printStackTrace();
                }

            }
        }

    }
}

Note: Don’t forget to add internet permissions to AndroidManifest.xml file.

Related Posts:


Gururaj P Kharvi

An Entrepreneur, Web and Android developer from India.

Author archive @androidcss on Twitter

June 3, 2018

Android

Previous post

Leave a Reply

Your email address will not be published.

*

Please follow & like us :)

Facebook
Facebook
GOOGLE
GOOGLE
http://androidcss.com/android/android-mysql-php-statistics">
Twitter
YOUTUBE
YOUTUBE
LINKEDIN

Newsletter

About | Policy | Disclaimer

Creative Commons Licence — Up ↑