Android populate spinner from mysql database

In this Android tip, I am going to show you how to load Mysql data into a Spinner widget. The connection from Android to Mysql requires internet permission. So, you add the following line to the AndroidManifest.xml file.

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

On your Mysql server, you create a database called admindb. In the database, you create tblinterviewees table. The table has three columns: iid, iname, and itel. The iid is auto-increment field. The iname and itel fields have VARCHAR data type. Then add some data to the table shown in below.




sample data



You open the activity_main.xml file and add a Spinner to the layout.

<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"
   tools:context=".MainActivity"
  >
   <Spinner
     android:id="@+id/spinner"
     android:layout_width="match_parent"
     android:layout_height="wrap_content">
     </Spinner>
</LinearLayout>


In the res/layout directory, you create spinner_layout.xml file that defines item template of the Spinner.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
   android:orientation="vertical"
   android:layout_width="match_parent"
   android:layout_height="match_parent"
   >
   <TextView
     android:id="@+id/txt"
     android:layout_width="match_parent"
     android:layout_height="wrap_content"
     android:padding="10dp"
     />
</LinearLayout>

You open a text editor (e.g. notepad), copy and paste the following PHP code, and save the file as getInterviewees.php in your root directory of you web server (www). The getInterviewees.php file is used to connect to the Mysql database and return a JSON string that contains all names in the iname column of the tblInterviewees table.

		<?php
		$DB_USER='test'; 
		$DB_PASS='test'; 
		$DB_HOST='localhost';
		$DB_NAME='admindb';
		$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
		/* check connection */
		if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
		}		
$mysqli->query("SET NAMES 'utf8'"); $sql="SELECT iname FROM tblinterviewees"; $result=$mysqli->query($sql); while($e=mysqli_fetch_assoc($result)){ $output[]=$e; }
print(json_encode($output)); $mysqli->close();
?>


The shown below Java code written in the MainActivity class, will execute the getInterviewees.php, get the JSON string, parse it, and display the result in the Spinner. To connect from Android emulator to your local Mysql, you need use the 10.0.2.2 ip address. If you connect to a Mysql database on a remote server, simply change the address that points to the PHP file that you want to execute (e.g http://www.example.com/getInterviewees.php).

import android.os.AsyncTask;
import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
public class MainActivity extends ActionBarActivity {
     ArrayList<String> listItems=new ArrayList<>();
     ArrayAdapter<String> adapter;
     Spinner sp;
     protected void onCreate(Bundle savedInstanceState) {
     super.onCreate(savedInstanceState);
     setContentView(R.layout.activity_main);
     sp=(Spinner)findViewById(R.id.spinner);
     adapter=new ArrayAdapter<String>(this,R.layout.spinner_layout,R.id.txt,listItems);
     sp.setAdapter(adapter);

     }
   public void onStart(){
     super.onStart();
     BackTask bt=new BackTask();
     bt.execute();
   }
   private class BackTask extends AsyncTask<Void,Void,Void> {
     ArrayList<String> list;
     protected void onPreExecute(){
        super.onPreExecute();
        list=new ArrayList<>();
     }
     protected Void doInBackground(Void...params){
        InputStream is=null;
        String result="";
        try{
           HttpClient httpclient=new DefaultHttpClient();
           HttpPost httppost= new HttpPost("http://10.0.2.2:8080/getInterviewees.php");
           HttpResponse response=httpclient.execute(httppost);
           HttpEntity entity = response.getEntity();
           // Get our response as a String.
           is = entity.getContent();
        }catch(IOException e){
           e.printStackTrace();
        }

        //convert response to string
        try{
           BufferedReader reader = new BufferedReader(new InputStreamReader(is,"utf-8"));          
           String line = null;
           while ((line = reader.readLine()) != null) {
             result+=line;
           }
           is.close();
           //result=sb.toString();
        }catch(Exception e){
           e.printStackTrace();
        }
        // parse json data
        try{
           JSONArray jArray =new JSONArray(result);
           for(int i=0;i<jArray.length();i++){
             JSONObject jsonObject=jArray.getJSONObject(i);
             // add interviewee name to arraylist
             list.add(jsonObject.getString("iname"));
           }
        }
        catch(JSONException e){
           e.printStackTrace();
        }
        return null;
     }
     protected void onPostExecute(Void result){
        listItems.addAll(list);
        adapter.notifyDataSetChanged();
     }
   }
}


spinner mysql database

Posted by: Dara | post date: 05-24-2015 | Subject: Android Apps Development

write programming tip




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.