Android Populating Spinner data from MySQL Database

My previous tutorial Android Populating Spinner data from SQLite Database explained how to pupulate spinner data from SQLite database. In this tutorial we are going to learn how to do the same, except the spinner data is being loaded from MySQL database. I choose PHP as server side technology which acts as a connecting layer between android app and mysql database.

If you haven’t tried connecting android with PHP and MySQL, How to connect Android with PHP, MySQL will give you complete overview of communicating android app with MySQL.

android populating spinner mysql database

Installing WAMP Server

WAMP lets you install Apache, PHP and MySQL with a single installer which reduces burden of installing & configuring them separately. Alternatively you can use XAMP, LAMP (on Linux) and MAMP (on MAC). WAMP also provides you phpmyadmin to easily interact with MySQL database.

Download & install WAMP from http://www.wampserver.com/en/. Choose the correct version which suits your operating system (32bit or 64bit). Once you have installed it, open the program from Start -> All Programs -> Wamp Server -> Start WampServer.

Open http://localhost/ and http://localhost/phpmyadmin/ to verify WAMP is installed successfully or not.

Creating MySQL Database

To demonstrate this tutorial I have taken an example of Food Categories API where we do two operations. Once is getting list of food categories and other is creating new food category. So let’s start creating a database for this.

Open phpmyadmin from http://localhost/phpmyadmin/ and create a database and tables using following SQL queries. Also you can use phpmyadmin GUI to create database and tables.

Creating database

CREATE DATABASE food;

Creating categories table & inserting some data.

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `categories` (`name`) VALUES
('Beverages'),
('Bread'),
('Cereals'),
('Cheese'),
('Citrus Fruits');

Creating PHP Project

On windows PC default location of the WAMP installation is C:\wamp. Go to this directory and open www directory. All the PHP projects will go into this directory.

The PHP project we are going to create will have following list of files.

Config.php – All the configuration variables like database name, username, password and other things goes here.
DbConnect.php – A class to open and close the database connection.
get_categories.php – To fetch list of food categories
new_category – To create a new food category in database.

I have used Netbeans IDE to develop my php project. This is optional, you can use your favorite IDE.

1. Create a new folder named food_api under C:\wamp\www directory for our project.

2. Create a new file named Config.php and write following code. Here we mentioned database name, username, password and host name. If your mysql database is having a password, don’t forget to define it here.

<?php
/**
 * Database configuration
 */
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_NAME', 'food');
?>

3. The first API call is listing all food categories. So create a file named get_categories.php with following content. Here we simply open the database connection, run a select query and build a response array and echo the json.

<?php
include_once './DbConnect.php';

function getCategories(){
    $db = new DbConnect();
    // array for json response
    $response = array();
    $response["categories"] = array();
    
    // Mysql select query
    $result = mysql_query("SELECT * FROM categories");
    
    while($row = mysql_fetch_array($result)){
        // temporary array to create single category
        $tmp = array();
        $tmp["id"] = $row["id"];
        $tmp["name"] = $row["name"];
        
        // push category to final json array
        array_push($response["categories"], $tmp);
    }
    
    // keeping response header to json
    header('Content-Type: application/json');
    
    // echoing json result
    echo json_encode($response);
}

getCategories();
?>

You can see the output of this file by opening http://localhost/food_api/get_categories.php

4. The second API call is creating a new food category. So create a file named new_category with following content. This api call accepts POST method and check for parameter name which is name of the new category to be created in database.

<?php

include_once './DbConnect.php';

function createNewCategory() {
    if (isset($_POST["name"]) && $_POST["name"] != "") {
        // response array for json
        $response = array();
        $category = $_POST["name"];
        
        $db = new DbConnect();

        // mysql query
        $query = "INSERT INTO categories(name) VALUES('$category')";
        $result = mysql_query($query) or die(mysql_error());
        if ($result) {
            $response["error"] = false;
            $response["message"] = "Category created successfully!";
        } else {
            $response["error"] = true;
            $response["message"] = "Failed to create category!";
        }
    } else {
        $response["error"] = true;
        $response["message"] = "Category name is missing!";
    }
    
    // echo json response
    echo json_encode($response);
}

createNewCategory();
?>

The url for this api call is http://localhost/food_api/new_category.php.

Until now we are done with server side part. It’s time to move on to android part.

Creating Android Project

1. Create a new project in Eclipse from File ⇒ New ⇒ Android Application Project. I had left my main activity name as MainActivity.java and gave the package name as info.androidhive.spinnermysql

2. As this application is going to use internet, we need to add INTERNET permission in AndroidManifest.xml file. Open your AndroidManifest.xml file and add following permission.

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

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="info.androidhive.spinnermysql"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk
        android:minSdkVersion="8"
        android:targetSdkVersion="18" />
    
    <!-- Internet Permissions -->
    <uses-permission android:name="android.permission.INTERNET"/>

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/AppTheme" >
        <activity
            android:name="info.androidhive.spinnermysql.MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

3. Create a class file named Category.php. This model class will be useful to convert json data into objects.

package info.androidhive.spinnermysql;

public class Category {
	
	private int id;
	private String name;
	
	public Category(){}
	
	public Category(int id, String name){
		this.id = id;
		this.name = name;
	}
	
	public void setId(int id){
		this.id = id;
	}
	
	public void setName(String name){
		this.name = name;
	}
	
	public int getId(){
		return this.id;
	}
	
	public String getName(){
		return this.name;
	}

}

4. I am creating another class to take care of making API calls and handling the response. Create a class named ServiceHandler.java and write the following code. makeServiceCall() method should be called to make http calls.

package info.androidhive.spinnermysql;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.util.List;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.utils.URLEncodedUtils;
import org.apache.http.impl.client.DefaultHttpClient;

import android.util.Log;

public class ServiceHandler {

	static InputStream is = null;
	static String response = null;
	public final static int GET = 1;
	public final static int POST = 2;

	public ServiceHandler() {

	}

	/**
	 * Making service call
	 * @url - url to make request
	 * @method - http request method
	 * */
	public String makeServiceCall(String url, int method) {
		return this.makeServiceCall(url, method, null);
	}

	/**
	 * Making service call
	 * @url - url to make request
	 * @method - http request method
	 * @params - http request params
	 * */
	public String makeServiceCall(String url, int method,
			List<NameValuePair> params) {
		try {
			// http client
			DefaultHttpClient httpClient = new DefaultHttpClient();
			HttpEntity httpEntity = null;
			HttpResponse httpResponse = null;
			
			// Checking http request method type
			if (method == POST) {
				HttpPost httpPost = new HttpPost(url);
				// adding post params
				if (params != null) {
					httpPost.setEntity(new UrlEncodedFormEntity(params));
				}

				httpResponse = httpClient.execute(httpPost);

			} else if (method == GET) {
				// appending params to url
				if (params != null) {
					String paramString = URLEncodedUtils
							.format(params, "utf-8");
					url += "?" + paramString;
				}
				HttpGet httpGet = new HttpGet(url);

				httpResponse = httpClient.execute(httpGet);

			}
			httpEntity = httpResponse.getEntity();
			is = httpEntity.getContent();

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

		try {
			BufferedReader reader = new BufferedReader(new InputStreamReader(
					is, "UTF-8"), 8);
			StringBuilder sb = new StringBuilder();
			String line = null;
			while ((line = reader.readLine()) != null) {
				sb.append(line + "\n");
			}
			is.close();
			response = sb.toString();
		} catch (Exception e) {
			Log.e("Buffer Error", "Error: " + e.toString());
		}

		return response;

	}
}

5. Now design the interface for your main activity. I have created simple layout to insert new category and a spinner to show the categories fetched from MySQL database. Open activity_main.xml file and insert the following code.

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:padding="10dp" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="New food category" />
    
    <EditText android:id="@+id/txtCategory"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"/>
    
    <Button android:id="@+id/btnAddNewCategory"
        android:text="Create"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginBottom="40dp"/>
    
    <TextView android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="Select food category"/>
    
    <Spinner android:id="@+id/spinFood"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"/>

</LinearLayout>

The above xml produces following UI

android spinner inserting into mysql

6. Open your MainActivity.java file add the basic code like declaring required variables, button click event, spinner listener etc.,

URL_NEW_CATEGORY – Url to create new category
URL_CATEGORIES – Url to get list of categories

public class MainActivity extends Activity implements OnItemSelectedListener {

	private Button btnAddNewCategory;
	private TextView txtCategory;
	private Spinner spinnerFood;
	// array list for spinner adapter
	private ArrayList<Category> categoriesList;
	ProgressDialog pDialog;

	// API urls
	// Url to create new category
	private String URL_NEW_CATEGORY = "http://10.0.2.2/food_api/new_category.php";
	// Url to get all categories
	private String URL_CATEGORIES = "http://10.0.2.2/food_api/get_categories.php";

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

		btnAddNewCategory = (Button) findViewById(R.id.btnAddNewCategory);
		spinnerFood = (Spinner) findViewById(R.id.spinFood);
		txtCategory = (TextView) findViewById(R.id.txtCategory);
		
		categoriesList = new ArrayList<Category>();

		// spinner item select listener
		spinnerFood.setOnItemSelectedListener(this);

		// Add new category click event
		btnAddNewCategory.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				if (txtCategory.getText().toString().trim().length() > 0) {
					
					// new category name
					String newCategory = txtCategory.getText().toString();

					// Call Async task to create new category
					new AddNewCategory().execute(newCategory);
				} else {
					Toast.makeText(getApplicationContext(),
							"Please enter category name", Toast.LENGTH_SHORT)
							.show();
				}
			}
		});

		new GetCategories().execute();		
	}
}

Getting list of categories and showing them in Spinner

7. I defined an Async method to fetch list of categories from MySQL and showing them in spinner. Add the following code after onCreate block in MainActivity.java. I also added another method populateSpinner() to takes care of loading the data into spinner. This async method should be called in onCreate method like new GetCategories().execute()

	/**
	 * Async task to get all food categories
	 * */
	private class GetCategories extends AsyncTask<Void, Void, Void> {

		@Override
		protected void onPreExecute() {
			super.onPreExecute();
			pDialog = new ProgressDialog(MainActivity.this);
			pDialog.setMessage("Fetching food categories..");
			pDialog.setCancelable(false);
			pDialog.show();

		}

		@Override
		protected Void doInBackground(Void... arg0) {
			ServiceHandler jsonParser = new ServiceHandler();
			String json = jsonParser.makeServiceCall(URL_CATEGORIES, ServiceHandler.GET);

			Log.e("Response: ", "> " + json);

			if (json != null) {
				try {
					JSONObject jsonObj = new JSONObject(json);
					if (jsonObj != null) {
						JSONArray categories = jsonObj
								.getJSONArray("categories");						

						for (int i = 0; i < categories.length(); i++) {
							JSONObject catObj = (JSONObject) categories.get(i);
							Category cat = new Category(catObj.getInt("id"),
									catObj.getString("name"));
							categoriesList.add(cat);
						}
					}

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

			} else {
				Log.e("JSON Data", "Didn't receive any data from server!");
			}

			return null;
		}

		@Override
		protected void onPostExecute(Void result) {
			super.onPostExecute(result);
			if (pDialog.isShowing())
				pDialog.dismiss();
			//populateSpinner();
		}

	}

	/**
	 * Adding spinner data
	 * */
	private void populateSpinner() {
		List<String> lables = new ArrayList<String>();
		
		txtCategory.setText("");

		for (int i = 0; i < categoriesList.size(); i++) {
			lables.add(categoriesList.get(i).getName());
		}

		// Creating adapter for spinner
		ArrayAdapter<String> spinnerAdapter = new ArrayAdapter<String>(this,
				android.R.layout.simple_spinner_item, lables);

		// Drop down layout style - list view with radio button
		spinnerAdapter
				.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

		// attaching data adapter to spinner
		spinnerFood.setAdapter(spinnerAdapter);
	}

If you run the project now, you should see the mysql data loaded into spinner. Use LogCat to debug the errors.

android populating spinner data from mysql database

Inserting a new food category into MySQL

8. I added another async method to insert a new food category into MySQL database. This method is called in create new button click event using new AddNewCategory().execute(newCategory)

/**
	 * Async task to create a new food category
	 * */
	private class AddNewCategory extends AsyncTask<String, Void, Void> {

		boolean isNewCategoryCreated = false;

		@Override
		protected void onPreExecute() {
			super.onPreExecute();
			pDialog = new ProgressDialog(MainActivity.this);
			pDialog.setMessage("Creating new category..");
			pDialog.setCancelable(false);
			pDialog.show();

		}

		@Override
		protected Void doInBackground(String... arg) {

			String newCategory = arg[0];

			// Preparing post params
			List<NameValuePair> params = new ArrayList<NameValuePair>();
			params.add(new BasicNameValuePair("name", newCategory));

			ServiceHandler serviceClient = new ServiceHandler();

			String json = serviceClient.makeServiceCall(URL_NEW_CATEGORY,
					ServiceHandler.POST, params);

			Log.d("Create Response: ", "> " + json);

			if (json != null) {
				try {
					JSONObject jsonObj = new JSONObject(json);
					boolean error = jsonObj.getBoolean("error");
					// checking for error node in json
					if (!error) {	
						// new category created successfully
						isNewCategoryCreated = true;
					} else {
						Log.e("Create Category Error: ", "> " + jsonObj.getString("message"));
					}

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

			} else {
				Log.e("JSON Data", "Didn't receive any data from server!");
			}

			return null;
		}

		@Override
		protected void onPostExecute(Void result) {
			super.onPostExecute(result);
			if (pDialog.isShowing())
				pDialog.dismiss();
			if (isNewCategoryCreated) {
				runOnUiThread(new Runnable() {
					@Override
					public void run() {
						// fetching all categories
						new GetCategories().execute();
					}
				});
			}
		}

	}

Run and the test the app again to check insertion.

Complete Code

Following is the complete code of MainActivity.java

package info.androidhive.spinnermysql;

import java.util.ArrayList;
import java.util.List;

import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.app.Activity;
import android.app.ProgressDialog;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;

public class MainActivity extends Activity implements OnItemSelectedListener {

	private Button btnAddNewCategory;
	private TextView txtCategory;
	private Spinner spinnerFood;
	// array list for spinner adapter
	private ArrayList<Category> categoriesList;
	ProgressDialog pDialog;

	// API urls
	// Url to create new category
	private String URL_NEW_CATEGORY = "http://10.0.2.2/food_api/new_category.php";
	// Url to get all categories
	private String URL_CATEGORIES = "http://10.0.2.2/food_api/get_categories.php";

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

		btnAddNewCategory = (Button) findViewById(R.id.btnAddNewCategory);
		spinnerFood = (Spinner) findViewById(R.id.spinFood);
		txtCategory = (TextView) findViewById(R.id.txtCategory);
		
		categoriesList = new ArrayList<Category>();

		// spinner item select listener
		spinnerFood.setOnItemSelectedListener(this);

		// Add new category click event
		btnAddNewCategory.setOnClickListener(new View.OnClickListener() {

			@Override
			public void onClick(View v) {
				if (txtCategory.getText().toString().trim().length() > 0) {
					
					// new category name
					String newCategory = txtCategory.getText().toString();

					// Call Async task to create new category
					new AddNewCategory().execute(newCategory);
				} else {
					Toast.makeText(getApplicationContext(),
							"Please enter category name", Toast.LENGTH_SHORT)
							.show();
				}
			}
		});

		new GetCategories().execute();

	}

	/**
	 * Adding spinner data
	 * */
	private void populateSpinner() {
		List<String> lables = new ArrayList<String>();
		
		txtCategory.setText("");

		for (int i = 0; i < categoriesList.size(); i++) {
			lables.add(categoriesList.get(i).getName());
		}

		// Creating adapter for spinner
		ArrayAdapter<String> spinnerAdapter = new ArrayAdapter<String>(this,
				android.R.layout.simple_spinner_item, lables);

		// Drop down layout style - list view with radio button
		spinnerAdapter
				.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);

		// attaching data adapter to spinner
		spinnerFood.setAdapter(spinnerAdapter);
	}

	/**
	 * Async task to get all food categories
	 * */
	private class GetCategories extends AsyncTask<Void, Void, Void> {

		@Override
		protected void onPreExecute() {
			super.onPreExecute();
			pDialog = new ProgressDialog(MainActivity.this);
			pDialog.setMessage("Fetching food categories..");
			pDialog.setCancelable(false);
			pDialog.show();

		}

		@Override
		protected Void doInBackground(Void... arg0) {
			ServiceHandler jsonParser = new ServiceHandler();
			String json = jsonParser.makeServiceCall(URL_CATEGORIES, ServiceHandler.GET);

			Log.e("Response: ", "> " + json);

			if (json != null) {
				try {
					JSONObject jsonObj = new JSONObject(json);
					if (jsonObj != null) {
						JSONArray categories = jsonObj
								.getJSONArray("categories");						

						for (int i = 0; i < categories.length(); i++) {
							JSONObject catObj = (JSONObject) categories.get(i);
							Category cat = new Category(catObj.getInt("id"),
									catObj.getString("name"));
							categoriesList.add(cat);
						}
					}

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

			} else {
				Log.e("JSON Data", "Didn't receive any data from server!");
			}

			return null;
		}

		@Override
		protected void onPostExecute(Void result) {
			super.onPostExecute(result);
			if (pDialog.isShowing())
				pDialog.dismiss();
			//populateSpinner();
		}

	}

	/**
	 * Async task to create a new food category
	 * */
	private class AddNewCategory extends AsyncTask<String, Void, Void> {

		boolean isNewCategoryCreated = false;

		@Override
		protected void onPreExecute() {
			super.onPreExecute();
			pDialog = new ProgressDialog(MainActivity.this);
			pDialog.setMessage("Creating new category..");
			pDialog.setCancelable(false);
			pDialog.show();

		}

		@Override
		protected Void doInBackground(String... arg) {

			String newCategory = arg[0];

			// Preparing post params
			List<NameValuePair> params = new ArrayList<NameValuePair>();
			params.add(new BasicNameValuePair("name", newCategory));

			ServiceHandler serviceClient = new ServiceHandler();

			String json = serviceClient.makeServiceCall(URL_NEW_CATEGORY,
					ServiceHandler.POST, params);

			Log.d("Create Response: ", "> " + json);

			if (json != null) {
				try {
					JSONObject jsonObj = new JSONObject(json);
					boolean error = jsonObj.getBoolean("error");
					// checking for error node in json
					if (!error) {	
						// new category created successfully
						isNewCategoryCreated = true;
					} else {
						Log.e("Create Category Error: ", "> " + jsonObj.getString("message"));
					}

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

			} else {
				Log.e("JSON Data", "Didn't receive any data from server!");
			}

			return null;
		}

		@Override
		protected void onPostExecute(Void result) {
			super.onPostExecute(result);
			if (pDialog.isShowing())
				pDialog.dismiss();
			if (isNewCategoryCreated) {
				runOnUiThread(new Runnable() {
					@Override
					public void run() {
						// fetching all categories
						new GetCategories().execute();
					}
				});
			}
		}

	}

	@Override
	public void onItemSelected(AdapterView<?> parent, View view, int position,
			long id) {
		Toast.makeText(
				getApplicationContext(),
						parent.getItemAtPosition(position).toString() + " Selected" ,
				Toast.LENGTH_LONG).show();

	}

	@Override
	public void onNothingSelected(AdapterView<?> arg0) {		
	}
}
Ravi is hardcore Android programmer and Android programming has been his passion since he compiled his first hello-world program. Solving real problems of Android developers through tutorials has always been interesting part for him.