I have not posted about android tutorials since long, but recently i received a request to give an example on retrieval of filtered data in android using PHP MySQL. Here i am with the example along with source code.

Before we start with the code part, create a table in your database. Since, to retrieve data from MySQL it is required to create the connection between Android application and MySQL, using HttpUrlConnection class. The PHP script is also important to fire the SQL query and to create the connection. So let us start with the code part with some explanation.

 

How To Retrieve Filtered Data In Android Using PHP MySQL

Assuming that you have successfully created the table in the database. Here, in this example i have used the table with Country and City fields along with some dummy data. In Android application designing part  i have used EditText, Button and the Spinner component in this example.

EditText : EditText for entering the string

Button : Button for Search functionality

Spinner : Spinner for populating filtered data based on entered string in EditText

Here is the activity_mail.xml code part.

activity_mail.xml
<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout 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"
android:paddingBottom="@dimen/activity_vertical_margin"
android:paddingLeft="@dimen/activity_horizontal_margin"
android:paddingRight="@dimen/activity_horizontal_margin"
android:paddingTop="@dimen/activity_vertical_margin"
app:layout_behavior="@string/appbar_scrolling_view_behavior"
tools:context="com.app.srcecde.search_example1.MainActivity"
tools:showIn="@layout/activity_main">

<EditText
android:layout_width="100dp"
android:layout_height="wrap_content"
android:id="@+id/editText"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />

<Button
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Search"
android:id="@+id/search"
android:layout_alignBottom="@+id/editText"
android:layout_toRightOf="@+id/editText"
android:layout_toEndOf="@+id/editText" />
<Spinner
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:id="@+id/spinner1"
android:layout_below="@+id/search"
android:layout_marginTop="20dp" />

</RelativeLayout>
MainActivity.java
package com.app.srcecde.search_example1;

import android.content.ContentValues;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.design.widget.FloatingActionButton;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.support.v7.widget.Toolbar;
import android.util.Log;
import android.view.View;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;
import android.widget.Toast;

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

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

public class MainActivity extends AppCompatActivity {

Spinner spinner1;
EditText e;
InputStream is=null;
String result=null;
String line=null;
HttpURLConnection urlConnection = null;

String text;
String [] city;


@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar);
setSupportActionBar(toolbar);

StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build(); StrictMode.setThreadPolicy(policy);

spinner1 = (Spinner) findViewById(R.id.spinner1);
final List<String> list1 = new ArrayList<String>();
e = (EditText) findViewById(R.id.editText);
Button b = (Button) findViewById(R.id.search);

b.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
//Toast.makeText(getApplicationContext(), "Invalid IP Address",Toast.LENGTH_LONG).show();

text = e.getText().toString(); //Here i am storing the entered text in the string format in text variable

ContentValues values = new ContentValues();
values.put("1", text);  // This will append the entered text in the url for filter purpose


try {

URL url = new URL("http://192.168.43.94/proj/search_example.php?string1="+text);
urlConnection = (HttpURLConnection) url.openConnection();
urlConnection.setRequestMethod("POST");
urlConnection.connect();
is = urlConnection.getInputStream();
}
catch (Exception e)
{
Log.e("Fail 1", e.toString());
}

try
{
BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
StringBuilder sb = new StringBuilder();
while ((line = reader.readLine()) != null)
{
sb.append(line + "\n");
}
is.close();
result = sb.toString();
}
catch(Exception e)
{
Log.e("Fail 2", e.toString());
}


try
{
JSONArray JA=new JSONArray(result);
JSONObject json= null;

city = new String[JA.length()];


for(int i=0;i<JA.length();i++)
{
json=JA.getJSONObject(i);
city[i] = json.getString("city");

}
//                    Toast.makeText(getApplicationContext(), "Data Loaded", Toast.LENGTH_LONG).show();

for(int i=0;i<city.length;i++)
{
list1.add(city[i]);

}

spinner_fn();

}
catch(Exception e)
{

Log.e("Fail 3", e.toString());


}


}
});


FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
fab.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
Snackbar.make(view, "Replace with your own action", Snackbar.LENGTH_LONG)
.setAction("Action", null).show();
}
});
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.menu_main, menu);
return true;
}

@Override
public boolean onOptionsItemSelected(MenuItem item) {
// Handle action bar item clicks here. The action bar will
// automatically handle clicks on the Home/Up button, so long
// as you specify a parent activity in AndroidManifest.xml.
int id = item.getItemId();

//noinspection SimplifiableIfStatement
if (id == R.id.action_settings) {
return true;
}

return super.onOptionsItemSelected(item);
}

private void spinner_fn() {
// TODO Auto-generated method stub

ArrayAdapter<String> dataAdapter1 = new ArrayAdapter<String>(getApplicationContext(),
android.R.layout.simple_spinner_item, city);
dataAdapter1.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spinner1.setAdapter(dataAdapter1);

}

}

In this article i have passed the text variable as parameter in the URL for the filtered results in MainActivity.java.

Before we go through the PHP part, you need to add the permission in AndroidManifest.xml for INTERNET access. Add below line in the AndroidManifest.xml file.

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

Now let us go through the PHP code part. The parameter you have passed in the URL, is retrieved by the $_GET method in PHP. Below is the search_example.php file source code.

search_example.php
<?php
$host='127.0.0.1:3340';
$uname='root';
$pwd='toor';
$db='search';
$con = mysqli_connect($host,$uname,$pwd) or die("connection failed");
mysqli_select_db($con,$db) or die("db selection failed");

if($_GET['string1'])
{
$ia =$_GET['string1'];

$r=mysqli_query($con,"select city from example where country='$ia'");
while($row=mysqli_fetch_assoc($r))
{
$cls[]=$row;
//echo $fin."<br>";
}
echo json_encode($cls);
}
mysqli_close($con);

?>

The above was the full source code for the example. Below are the screenshots of the output, which is taken from the live-android device.

 

How To Retrieve Filtered Data In Android Using PHP MySQL
How To Retrieve Filtered Data In Android Using PHP MySQL

 

The above was the basic example on how to filter the data in Android using PHP MySQL. Explore the video on same for reference.

 


Subscribe for direct updates right in your inbox. Keep sharing. Stay tuned to Tech Tunes.

 

Follow me on Twitter