Android Retrofit MySQL Serverside Multi-Column Search/Filter ListView Tutorial
In this class we see how to perform a multi-column search filter against mysql database from our android app. We use Java to write our app and Retrofit as our HTTP Client. Furthermore we use ListView as our AdapterView. MySQL is our database while PHP is our serverside programming language.
Demo
Here’s the demo of the application:

Android Retrofit ServerSide Search Filter
Video Tutorial
We have a fast growing ProgrammingWizards TV YouTube Channel with tutorials like this. Here is this tutorial in video format:
1. PHP
First we need to write PHP code that will:
- Connect to mysql database using
mysqli
class. - Perform a multi-column search against mysql using SQL statements.
- Return results to a PHP array.
- JSON-encode that array and print it to the caller.
We use Object Oriented paradigm to code our PHP. We have only one file:
index.php
<?php
class Constants
{
//DATABASE DETAILS
static $DB_SERVER="localhost";
static $DB_NAME="spacecraftsDB";
static $USERNAME="root";
static $PASSWORD="";
//STATEMENTS
static $SQL_SELECT_ALL="SELECT * FROM spacecraftsTB";
}
class Spacecrafts
{
/*******************************************************************************************************************************************/
/*
1.CONNECT TO DATABASE.
1. RETURN CONNECTION OBJECT
*/
public function connect()
{
$con=new mysqli(Constants::$DB_SERVER,Constants::$USERNAME,Constants::$PASSWORD,Constants::$DB_NAME);
if($con->connect_error)
{
return null;
}else
{
return $con;
}
}
/*******************************************************************************************************************************************/
/*
1.SELECT FROM DATABASE.
*/
public function search($query)
{
$sql="SELECT * FROM spacecraftsTB WHERE name LIKE '%$query%' OR propellant LIKE '%$query%' OR destination LIKE '%$query%' ";
//$sql="SELECT * FROM spacecraftsTB WHERE name LIKE '%$query%' ";
$con=$this->connect();
if($con != null)
{
$result=$con->query($sql);
if($result->num_rows > 0)
{
$spacecrafts=array();
while($row=$result->fetch_array())
{
array_push($spacecrafts, array("id"=>$row['id'],"name"=>$row['name'],"propellant"=>$row['propellant'],"destination"=>$row['destination'],"image_url"=>$row['image_url'],"technology_exists"=>$row['technology_exists']));
}
print(json_encode(array_reverse($spacecrafts)));
}else
{
print(json_encode(array("No item Found that matches the query: ".$query)));
}
$con->close();
}else{
print(json_encode(array("PHP EXCEPTION : CAN'T CONNECT TO MYSQL. NULL CONNECTION.")));
}
}
public function handleRequest() {
if($_SERVER['REQUEST_METHOD'] == 'POST'){
$query=$_POST['query'];
$this->search($query);
} else{
$this->search("");
}
}
}
$spacecrafts=new Spacecrafts();
$spacecrafts->handleRequest();
//end
Setup
We are interested in setting up in two files:
- build.gradle(app) – We add our dependencies here.
- AndroidManifest.xml – We add internet connectivity permission here.
(a). build.gradle
Let’s go over to dependencies closure and add dependencies
dependencies { implementation fileTree(dir: 'libs', include: ['*.jar']) testImplementation 'junit:junit:4.12' implementation 'com.android.support:appcompat-v7:28.0.0' implementation 'com.android.support.constraint:constraint-layout:1.0.2' implementation "com.android.support:cardview-v7:28.0.0" implementation 'com.squareup.retrofit2:retrofit:2.4.0' implementation 'com.squareup.retrofit2:converter-gson:2.4.0' implementation 'com.squareup.picasso:picasso:2.71828' }
(b). AndroidManifest.xml
Add internet connectivity permission here. Also register our DetailsActivity
.
<uses-permission android_name="android.permission.INTERNET"/> <application> <activity android_name=".MainActivity"> .. </activity> <activity android_name=".DetailsActivity" android_parentActivityName=".MainActivity"/> </application>
Our Classes
We have only two files:
- MainActivity.java
- DetailsActivity.java
(a). MainActivity.java
Our main activity.
package info.camposha.retrofitserversidesearch; import android.content.Context; import android.content.Intent; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.support.v7.widget.SearchView; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.CheckBox; import android.widget.Filter; import android.widget.Filterable; import android.widget.ImageView; import android.widget.ListView; import android.widget.ProgressBar; import android.widget.TextView; import android.widget.Toast; import com.google.gson.annotations.SerializedName; import com.squareup.picasso.Picasso; import java.util.ArrayList; import java.util.List; import retrofit2.Call; import retrofit2.Callback; import retrofit2.Response; import retrofit2.Retrofit; import retrofit2.converter.gson.GsonConverterFactory; import retrofit2.http.Body; import retrofit2.http.Field; import retrofit2.http.GET; import retrofit2.http.POST; import retrofit2.http.FormUrlEncoded; public class MainActivity extends AppCompatActivity { //private static final String BASE_URL = "http://10.0.2.2"; or "http://10.0.3.2" or your computers ip address private static final String BASE_URL = "http://192.168.12.2";//replace this wih the ip address for your computer private static final String FULL_URL = BASE_URL+"/PHP/spaceship/"; class Spacecraft { @SerializedName("id") private int id; @SerializedName("name") private String name; @SerializedName("propellant") private String propellant; @SerializedName("destination") private String destination; @SerializedName("image_url") private String imageURL; @SerializedName("technology_exists") private int technologyExists; public Spacecraft(String name){ this.name=name; } public Spacecraft(int id, String name, String propellant,String destination, String imageURL, int technologyExists) { this.id = id; this.name = name; this.propellant = propellant; this.destination=destination; this.imageURL = imageURL; this.technologyExists = technologyExists; } /* *GETTERS AND SETTERS */ public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public String getPropellant() { return propellant; } public String getDestination() { return destination; } public String getImageURL() { return imageURL; } public int getTechnologyExists() { return technologyExists; } @Override public String toString() { return name; } } interface MyAPIService { @GET("/PHP/spaceship") Call<List<Spacecraft>> getSpacecrafts(); @FormUrlEncoded @POST("/PHP/spaceship/index.php") Call<List<Spacecraft>> searchSpacecraft(@Field("query") String query); } static class RetrofitClientInstance { private static Retrofit retrofit; public static Retrofit getRetrofitInstance() { if (retrofit == null) { retrofit = new Retrofit.Builder() .baseUrl(BASE_URL) .addConverterFactory(GsonConverterFactory.create()) .build(); } return retrofit; } } class ListViewAdapter extends BaseAdapter { private List<Spacecraft> spacecrafts; private Context context; public ListViewAdapter(Context context, List<Spacecraft> spacecrafts) { this.context = context; this.spacecrafts = spacecrafts; } @Override public int getCount() { return spacecrafts.size(); } @Override public Object getItem(int pos) { return spacecrafts.get(pos); } @Override public long getItemId(int pos) { return pos; } @Override public View getView(int position, View view, ViewGroup viewGroup) { if (view == null) { view = LayoutInflater.from(context).inflate(R.layout.model, viewGroup, false); } TextView nameTxt = view.findViewById(R.id.nameTextView); TextView txtPropellant = view.findViewById(R.id.propellantTextView); CheckBox chkTechExists = view.findViewById(R.id.myCheckBox); ImageView spacecraftImageView = view.findViewById(R.id.spacecraftImageView); final Spacecraft thisSpacecraft = spacecrafts.get(position); nameTxt.setText(thisSpacecraft.getName()); txtPropellant.setText(thisSpacecraft.getPropellant()); chkTechExists.setChecked(thisSpacecraft.getTechnologyExists() == 1); chkTechExists.setEnabled(false); if (thisSpacecraft.getImageURL() != null && thisSpacecraft.getImageURL().length() > 0) { Picasso.get().load(FULL_URL + "/images/" + thisSpacecraft.getImageURL()).placeholder(R.drawable.placeholder).into(spacecraftImageView); } else { Toast.makeText(context, "Empty Image URL", Toast.LENGTH_LONG).show(); Picasso.get().load(R.drawable.placeholder).into(spacecraftImageView); } view.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { Toast.makeText(context, thisSpacecraft.getName(), Toast.LENGTH_SHORT).show(); String techExists = ""; if (thisSpacecraft.getTechnologyExists() == 1) { techExists = "YES"; } else { techExists = "NO"; } String[] spacecrafts = { thisSpacecraft.getName(), thisSpacecraft.getPropellant(), thisSpacecraft.getDestination(), techExists, FULL_URL + "/images/" + thisSpacecraft.getImageURL() }; openDetailActivity(spacecrafts); } }); return view; } private void openDetailActivity(String[] data) { Intent intent = new Intent(MainActivity.this, DetailsActivity.class); intent.putExtra("NAME_KEY", data[0]); intent.putExtra("PROPELLANT_KEY", data[1]); intent.putExtra("DESTINATION_KEY", data[2]); intent.putExtra("TECHNOLOGY_EXISTS_KEY", data[3]); intent.putExtra("IMAGE_KEY", data[4]); startActivity(intent); } } private ListViewAdapter adapter; private ListView mListView; private ProgressBar mProgressBar; private SearchView mSearchView; private void initializeWidgets(){ mListView = findViewById(R.id.mListView); mProgressBar= findViewById(R.id.mProgressBar); mProgressBar.setIndeterminate(true); mProgressBar.setVisibility(View.VISIBLE); mSearchView=findViewById(R.id.mSearchView); mSearchView.setIconified(true); } private void populateListView(List<Spacecraft> spacecraftList) { adapter = new ListViewAdapter(this,spacecraftList); mListView.setAdapter(adapter); } @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); this.initializeWidgets(); /*Create handle for the RetrofitInstance interface*/ final MyAPIService myAPIService = RetrofitClientInstance.getRetrofitInstance().create(MyAPIService.class); //Call<List<Spacecraft>> call = myAPIService.getSpacecrafts(); final Call<List<Spacecraft>> call = myAPIService.searchSpacecraft(""); call.enqueue(new Callback<List<Spacecraft>>() { @Override public void onResponse(Call<List<Spacecraft>> call, Response<List<Spacecraft>> response) { mProgressBar.setVisibility(View.GONE); populateListView(response.body()); } @Override public void onFailure(Call<List<Spacecraft>> call, Throwable throwable) { mProgressBar.setVisibility(View.GONE); Toast.makeText(MainActivity.this, throwable.getMessage(), Toast.LENGTH_LONG).show(); } }); mSearchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() { @Override public boolean onQueryTextSubmit(String query) { return false; } @Override public boolean onQueryTextChange(String query) { final Call<List<Spacecraft>> call = myAPIService.searchSpacecraft(query); call.enqueue(new Callback<List<Spacecraft>>() { @Override public void onResponse(Call<List<Spacecraft>> call, Response<List<Spacecraft>> response) { mProgressBar.setVisibility(View.GONE); populateListView(response.body()); } @Override public void onFailure(Call<List<Spacecraft>> call, Throwable throwable) { populateListView(new ArrayList<Spacecraft>()); mProgressBar.setVisibility(View.GONE); Toast.makeText(MainActivity.this, "ERROR: "+throwable.getMessage(), Toast.LENGTH_LONG).show(); } }); return false; } }); } } //end
(b). DetailsActivity.java
Our details activity.
package info.camposha.retrofitserversidesearch; import android.content.Intent; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.widget.CheckBox; import android.widget.ImageView; import android.widget.TextView; import com.squareup.picasso.Picasso; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.Date; public class DetailsActivity extends AppCompatActivity { TextView nameDetailTextView,propellantDetailTextView,dateDetailTextView,destinationDetailTextView; CheckBox techExistsDetailCheckBox; ImageView teacherDetailImageView; private void initializeWidgets(){ nameDetailTextView= findViewById(R.id.nameDetailTextView); propellantDetailTextView= findViewById(R.id.propellantDetailTextView); dateDetailTextView= findViewById(R.id.dateDetailTextView); destinationDetailTextView=findViewById(R.id.destinationDetailTextView); techExistsDetailCheckBox= findViewById(R.id.techExistsDetailCheckBox); teacherDetailImageView=findViewById(R.id.teacherDetailImageView); } private String getDateToday(){ DateFormat dateFormat=new SimpleDateFormat("yyyy/MM/dd"); Date date=new Date(); String today= dateFormat.format(date); return today; } private void receiveAndShowData(){ //RECEIVE DATA FROM ITEMS ACTIVITY VIA INTENT Intent i=this.getIntent(); String name=i.getExtras().getString("NAME_KEY"); String propellant=i.getExtras().getString("PROPELLANT_KEY"); String destination=i.getExtras().getString("DESTINATION_KEY"); String technologyExists=i.getExtras().getString("TECHNOLOGY_EXISTS_KEY"); String imageURL=i.getExtras().getString("IMAGE_KEY"); //SET RECEIVED DATA TO TEXTVIEWS AND IMAGEVIEWS nameDetailTextView.setText(name); propellantDetailTextView.setText(propellant); dateDetailTextView.setText(getDateToday()); destinationDetailTextView.setText(destination); techExistsDetailCheckBox.setChecked(technologyExists.equalsIgnoreCase("YES")); techExistsDetailCheckBox.setEnabled(false); Picasso.get().load(imageURL).placeholder(R.drawable.placeholder).into(teacherDetailImageView); } @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_detail); initializeWidgets(); receiveAndShowData(); } }
Our Layouts
We have these layout files:
- activity_main.xml
- activity_detail.xml
- model
(a). activity_main.xml
This is the layout for the main activity.
<?xml version="1.0" encoding="utf-8"?> <LinearLayout android_layout_width="match_parent" android_layout_height="match_parent" android_orientation="vertical" tools_context=".MainActivity"> <TextView android_id="@+id/headerTxt" android_layout_width="match_parent" android_layout_height="wrap_content" android_text="Retrofit MySQL Serverside Search" android_padding="5dp" android_textAlignment="center" android_textStyle="bold" android_textAppearance="@style/TextAppearance.AppCompat.Large" android_textColor="@color/colorAccent" /> <ProgressBar android_id="@+id/mProgressBar" style="?android:attr/progressBarStyleHorizontal" android_layout_width="match_parent" android_layout_height="wrap_content" android_indeterminate="true" android_indeterminateBehavior="cycle" android_visibility="gone" /> <android.support.v7.widget.SearchView android_id="@+id/mSearchView" app_queryHint="Search.." android_layout_width="match_parent" android_layout_height="wrap_content" /> <ListView android_id="@+id/mListView" android_layout_weight="0.5" android_numColumns="auto_fit" android_layout_width="match_parent" android_layout_height="wrap_content" /> </LinearLayout>
(b). activity_detail.xml
This is the layout for the detail activity.
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout android_layout_width="match_parent" android_layout_height="match_parent" android_background="#009688" tools_context=".DetailsActivity" tools_showIn="@layout/activity_detail"> <android.support.v7.widget.CardView android_layout_width="match_parent" android_layout_height="match_parent" android_layout_margin="3dp" card_view_cardCornerRadius="3dp" card_view_cardElevation="5dp"> <ScrollView android_layout_width="match_parent" android_layout_height="match_parent"> <LinearLayout android_layout_width="match_parent" android_layout_height="match_parent" android_orientation="vertical"> <ImageView android_id="@+id/teacherDetailImageView" android_layout_width="match_parent" android_layout_height="250dp" android_layout_alignParentTop="true" android_padding="5dp" android_scaleType="fitXY" android_src="@drawable/placeholder" /> <LinearLayout android_layout_width="match_parent" android_layout_height="wrap_content" android_orientation="vertical"> <LinearLayout android_layout_width="match_parent" android_layout_height="wrap_content" android_orientation="horizontal"> <TextView android_id="@+id/nameLabel" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="NAME : " android_textAppearance="?android:attr/textAppearanceLarge" android_textStyle="bold" /> <TextView android_id="@+id/nameDetailTextView" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="Voyager" android_textAppearance="?android:attr/textAppearanceLarge"/> </LinearLayout> <LinearLayout android_layout_width="match_parent" android_layout_height="wrap_content" android_orientation="horizontal"> <TextView android_id="@+id/dateLabel" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="DATE : " android_textAppearance="?android:attr/textAppearanceLarge" android_textStyle="bold" /> <TextView android_id="@+id/dateDetailTextView" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="Today" android_textAppearance="?android:attr/textAppearanceLarge"/> </LinearLayout> <LinearLayout android_layout_width="match_parent" android_layout_height="wrap_content" android_orientation="horizontal"> <TextView android_id="@+id/destinationLabel" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="DESTINATION : " android_textAppearance="?android:attr/textAppearanceLarge" android_textStyle="bold" /> <TextView android_id="@+id/destinationDetailTextView" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="KY Cygni" android_textAppearance="?android:attr/textAppearanceLarge"/> </LinearLayout> <LinearLayout android_layout_width="match_parent" android_layout_height="wrap_content" android_orientation="horizontal"> <TextView android_id="@+id/propellantLabel" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="PROPELLANT : " android_textAppearance="?android:attr/textAppearanceLarge" android_textStyle="bold" /> <TextView android_id="@+id/propellantDetailTextView" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="Chemical" android_textAppearance="?android:attr/textAppearanceLarge"/> </LinearLayout> <LinearLayout android_layout_width="match_parent" android_layout_height="wrap_content" android_orientation="horizontal"> <TextView android_id="@+id/techExistsLabel" android_layout_width="wrap_content" android_layout_height="wrap_content" android_maxLines="1" android_padding="5dp" android_text="TECH EXISTS? : " android_textAppearance="?android:attr/textAppearanceLarge" android_textStyle="bold" /> <CheckBox android_id="@+id/techExistsDetailCheckBox" android_layout_width="wrap_content" android_layout_height="wrap_content" android_padding="5dp"/> </LinearLayout> </LinearLayout> </LinearLayout> </ScrollView> </android.support.v7.widget.CardView> </RelativeLayout>
(b). model.xml
Our row model layout.
<?xml version="1.0" encoding="utf-8"?> <android.support.v7.widget.CardView android_orientation="horizontal" android_layout_width="match_parent" android_layout_margin="5dp" card_view_cardCornerRadius="10dp" card_view_cardElevation="5dp" android_layout_height="match_parent"> <RelativeLayout android_layout_width="match_parent" android_layout_height="match_parent"> <ImageView android_layout_width="150dp" android_layout_height="150dp" android_id="@+id/spacecraftImageView" android_padding="5dp" android_scaleType="fitXY" android_src="@drawable/placeholder" /> <TextView android_layout_width="wrap_content" android_layout_height="wrap_content" android_textAppearance="?android:attr/textAppearanceLarge" android_text="Spacecraft Name" android_id="@+id/nameTextView" android_padding="5dp" android_textColor="@color/colorAccent" android_layout_alignParentTop="true" android_layout_toRightOf="@+id/spacecraftImageView" /> <TextView android_layout_width="wrap_content" android_layout_height="wrap_content" android_textAppearance="?android:attr/textAppearanceMedium" android_text="Propellant" android_textStyle="italic" android_id="@+id/propellantTextView" android_padding="5dp" android_layout_alignBottom="@+id/spacecraftImageView" android_layout_toRightOf="@+id/spacecraftImageView" /> <CheckBox android_layout_width="wrap_content" android_layout_height="wrap_content" android_id="@+id/myCheckBox" android_text="Tech Exists?" android_layout_alignParentRight="true" /> </RelativeLayout> </android.support.v7.widget.CardView>
Download
Register as a member to download code. I will support members as soon as possible when they encounter problems. If you are already registered, just login to download code from any lesson.
[download id=”7828″ template=”pmpro_button”]
Nice tutorial. Worked on localhost but when I hosted the files, I get jsonreader use lenient … error. Have any solution to that
Oclemy
Given that it works on localhost it may be a server side issue like inability to connect to mysql or that you have specified a wrong url in your code.