How to get data from MySQL database and list in the ListView

Question

I've seen numerous questions/tutorials about this, but all of them either use some kind of premade database or work with JSON.

I already created a database and a table with simlpe data that resides on my MySQL server, which is running separately as a service on my development machine.

I want to pull the data from that table and list it in the ListView(MainActivity) of my Andoird app when it's launched(onCreate).

At the moment I'm using a premade list of car names.

Cars table(in mydb database)

id, name, model, year, vin, km, price

MainActivity.java

package com.example.myfirstproject;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.Menu;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.ListAdapter;
import android.widget.ListView;

public class MainActivity extends Activity implements OnItemClickListener  {

    protected String[] cars =  {"BMW", "Audi", "VW", "Ford", "Subaru"};

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        ListAdapter adapter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1, cars);
        ListView carsList = (ListView) findViewById(R.id.listCars);
        carsList.setAdapter(adapter);
        ListView list = (ListView) findViewById(R.id.listCars);
        list.setOnItemClickListener(this);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.activity_main, menu);
        return true;
    }

    public void onItemClick(AdapterView<?> arg0, View arg1, int arg2, long arg3) {
        Intent intent = new Intent(this, DetailsActivity.class);
        startActivity(intent);
    }
}

Answer 1

I am reluctant to post this answer due to all of the backlash I will get from using deprecated PHP/MySQL functions, but here goes:

Server Side (FYI, look into MySQLi or PDO_MySQL, mysql_connect is discouraged):

if ($_GET["selectedCompany"] == "true" && $_GET['companyName']) {

    $query = "Your query here";

    mysql_connect($dbserver, $dbusername, $dbpassword) or die(mysql_error());
    mysql_select_db($dbname) or die(mysql_error());

    $result = mysql_query($query) or die(mysql_error());  
    $num = mysql_numrows($result);
    $row = mysql_fetch_assoc($result);

    $i = 0;
    $rows = array();
    while ($i < $num) {

        $survey_lat['survey_lat'] = mysql_result($result, $i, "survey_lat");
        $survey_lng['survey_lng'] = mysql_result($result, $i, "survey_lng");
        $thumbnail = mysql_result($result, $i, "thumbnail");
        $finalImg['thumbnail'] = base64_encode($thumbnail);
        $sign_type['sign_type'] = mysql_result($result, $i, "sign_type");
        $object_lat['object_lat'] = mysql_result($result, $i, "object_lat");
        $object_lng['object_lng'] = mysql_result($result, $i, "object_lng");

        $finalArray = array_push($rows, array_merge($sign_type, $object_lat, $object_lng, $survey_lat, $survey_lng, $finalImg));

        $i++;
    }

    print json_encode($rows);
    mysql_close();
}

Client/Android side AsyncTask to retrieve (I modified the answer I gave before to reflect the mysql query and how to parse that JSON data):

class MyAsyncTask extends AsyncTask<String, String, Void> {

    private ProgressDialog progressDialog = new ProgressDialog(MainActivity.this);
    InputStream inputStream = null;
    String result = ""; 

    protected void onPreExecute() {
        progressDialog.setMessage("Your progress dialog message...");
        progressDialog.show();
        progressDialog.setOnCancelListener(new OnCancelListener() {
            public void onCancel(DialogInterface arg0) {
                MyAsyncTask.this.cancel(true);
            }
        });
    }

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

        String url_select = "http://yoururlhere.com/index.php?selectedCompany=true&companyName=examplevalue";

                ArrayList<NameValuePair> param = new ArrayList<NameValuePair>();

        try {
            // Set up HTTP post
            // HttpClient is more then less deprecated. Need to change to URLConnection
            HttpClient httpClient = new DefaultHttpClient();

            HttpPost httpPost = new HttpPost(url_select);
            httpPost.setEntity(new UrlEncodedFormEntity(param));
            HttpResponse httpResponse = httpClient.execute(httpPost);
            HttpEntity httpEntity = httpResponse.getEntity();

            // Read content & Log
            inputStream = httpEntity.getContent();
        } catch (UnsupportedEncodingException e1) {
            Log.e("UnsupportedEncodingException", e1.toString());
            e1.printStackTrace();
        } catch (ClientProtocolException e2) {
            Log.e("ClientProtocolException", e2.toString());
            e2.printStackTrace();
        } catch (IllegalStateException e3) {
            Log.e("IllegalStateException", e3.toString());
            e3.printStackTrace();
        } catch (IOException e4) {
            Log.e("IOException", e4.toString());
            e4.printStackTrace();
        }
        // Convert response to string using String Builder
        try {
            BufferedReader bReader = new BufferedReader(new InputStreamReader(inputStream, "iso-8859-1"), 8);
            StringBuilder sBuilder = new StringBuilder();

            String line = null;
            while ((line = bReader.readLine()) != null) {
                sBuilder.append(line + "\n");
            }

            inputStream.close();
            result = sBuilder.toString();

        } catch (Exception e) {
            Log.e("StringBuilding & BufferedReader", "Error converting result " + e.toString());
        }
    } // protected Void doInBackground(String... params)


    protected void onPostExecute(Void v) {

        //parse JSON data
        try{
            JSONArray jArray = new JSONArray(result);

            for(int i=0; i < jArray.length(); i++) {

                JSONObject jObject = jArray.getJSONObject(i);

                String signType = jObject.getString("sign_type");
                double object_lat = jObject.getDouble("object_lat");
                double object_lng = jObject.getDouble("object_lng");
                double survey_lat = jObject.getDouble("survey_lat");
                double survey_lng = jObject.getDouble("survey_lng");
                String thumbnailImg = jObject.getString("thumbnail");
                byte[] encodeByte = Base64.decode(thumbnailImg, Base64.DEFAULT);
                bmImg = BitmapFactory.decodeByteArray(encodeByte, 0, encodeByte.length);
                bmImg = Bitmap.createBitmap(bmImg);

            } // End for Loop

            this.progressDialog.dismiss();

        } catch (JSONException e) {

            Log.e("JSONException", "Error: " + e.toString());

        } // catch (JSONException e)


    } // protected void onPostExecute(Void v)

} //class MyAsyncTask extends AsyncTask<String, String, Void>