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:

1. Connect to mysql database using mysqli class.
2. Perform a multi-column search against mysql using SQL statements.
3. Return results to a PHP array.
4. 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:

1. build.gradle(app) – We add our dependencies here.
2. 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:

1. MainActivity.java
2. 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:

1. activity_main.xml
2. activity_detail.xml
3. 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”]