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.
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_recordsfunction and finally, returning a result back from the function. - Encode the result in JSON format and send it from PHP.
- You need to include above config.inc.php file in function
<?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→drawabledirectory. - 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.
- This file has to go under
<?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
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.
- Initialize all variables and set Array from
strings.xmlto Spinner and populate the list. - Based on user item selection from the Spinner, make a call to
AsyncRetrieveclass with parameter as item position. - 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/). TheAsyncRetrieveclass is responsible for making a call to PHP and retrieving results. - Parse results to JSON and populate the data to views.
- Initialize all variables and set Array from
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.
August 13, 2018 at 9:22 pm
thank you very much