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);
    }
}

Well, you have to transfer the data from your database server to your Android device. The best option is unfortunately JSON and a REST interface to your database. — zapl
– zapl, Commented Nov 2, 2012 at 18:40
You have to create a web service that will return data to you from mysql. You can read this tutorial. I tried using the mysql connector but it doesn't work for android. — Adrian C.
– Adrian C., Commented Nov 2, 2012 at 18:41
Server side, use JSON. Then my suggestion would be to use AsyncTask see example here. That's how I do it anyway — jnthnjns
– jnthnjns, Commented Nov 2, 2012 at 18:42
@AdrianC. Interesting tutorial. I would have used it, but the Android code looks like Visual Basic. What gives? — user1701467
– user1701467, Commented Nov 2, 2012 at 18:48
Basic4Android, that's why you saw visual basic. And to answer to your second question: REST( REpresentational State Transfer ) defines a set of architectural principles by which you can design Web services. You can have a web service done in grails, or have php script that will return json. Read Asok tutorial for Android part and this for the php part that would be your web service. — Adrian C.
– Adrian C., Commented Nov 2, 2012 at 18:59

jnthnjns · Accepted Answer · 2012-11-02 20:11:45Z

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>

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related