how to fetch data from a database online

Question

I have a layout.xml in which when I click a button, the app looks for data on an online database. Imagine an app that goes on a DB online to see how many items are left for online purchases.

Now, I set the check for internet connectivity, I can set an action through the onClick class and I use the HttpGet but, how do I look for an information in a database online? Is there any ID for this item, or parameters to be used? How did you set your database, so as to be used to let information be found?

Thanks in advance!

Answer 1

That is a large answer but lets try.

First of all you have to build a .php file that will export your database data into a json array.

An example is below:

<?php
try {
    $con = new PDO('mysql:host=yourserver(localhost in most cases);dbname=yourdatabasename', 'username', 'password');
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
    echo $e->getMessage();
    die();
}

$query = $con->query('SELECT * FROM tablename');
$data = array();
$data = $query->fetchAll(PDO::FETCH_ASSOC);
$arrayWithData['jsonArrayName'] = $data;
echo json_encode($arrayWithData);
?>

Now since you have a jsonArray with all database data you are going to have to set it as a String URL and call it through AsyncTask in your Activity or Fragment.

Another example is this:

Global Variables:

private String jsonResult;
private String url = "http://yourdomainorlocalhost/get_data_in_json_file.php";
ProgressDialog pDialog;
List<StockList> customList;
String[] justPrices;

And now comes the tricky part:

AsyncTask:

public class JsonReadTask extends AsyncTask<String, Void, String> {
        public JsonReadTask() {
            super();
        }
        //Create a ProgressDialog so the screen wont freeze while the Task Executes
        @Override
        protected void onPreExecute() {
            super.onPreExecute();
            pDialog = new ProgressDialog(ListLoaderActivity.this);
            pDialog.setTitle(R.string.waiting);
            pDialog.setProgressStyle(ProgressDialog.STYLE_SPINNER);
            pDialog.setMessage(getString(R.string.get_stocks));
            pDialog.setIndeterminate(true);
            pDialog.setCancelable(false);
            pDialog.setInverseBackgroundForced(true);
            pDialog.show();
        }

        @Override
        protected String doInBackground(String... params) {
            HttpClient httpclient = new DefaultHttpClient();
            HttpPost httppost = new HttpPost(params[0]);
            try {
                HttpResponse response = httpclient.execute(httppost);
                jsonResult = inputStreamToString(
                        response.getEntity().getContent()).toString();
            } catch (Exception e) {
                Intent intent1 = new Intent(YourActivity.this,
                        RefreshActivity.class);
                startActivity(intent1);
                YourActivity.this.finish();
            }
            return null;
        }
        //Convert responce to String
        private StringBuilder inputStreamToString(InputStream is) {
            String rLine = "";
            StringBuilder answer = new StringBuilder();
            BufferedReader rd = new BufferedReader(new InputStreamReader(is));
            try {
                while ((rLine = rd.readLine()) != null) {
                    answer.append(rLine);
                }
            } catch (Exception e) {
                Intent intent1 = new Intent(YourActivity.this,
                        RefreshActivity.class);
                startActivity(intent1);
                YourActivity.this.finish();
            }
            return answer;
        }
 //Method that is being called after data have been downloaded
        @Override
        protected void onPostExecute(String result) {
            ListDrawer();
            pDialog.dismiss();
        }
    }// end async task
 // A method to execute task
    public void accessWebService() {
        JsonReadTask task = new JsonReadTask();
        task.execute(new String[]{url});
    }
 //Now that we have gotten the items fetched we can build the ListView
    public void ListDrawer() {
        customList = new ArrayList<StockList>();
        try {
            JSONObject jsonResponse = new JSONObject(jsonResult);
            JSONArray jsonMainNode = jsonResponse.optJSONArray("jsonArrayName");
            for (int i = 0; i < jsonMainNode.length(); i++) {
                JSONObject jsonChildNode = jsonMainNode.getJSONObject(i);
                name = jsonChildNode.optString("name");
                price = jsonChildNode.optString("price");
                price1 = jsonChildNode.optString("price1");
                price2 = jsonChildNode.optString("price2");
                price3 = jsonChildNode.optString("price3");
                price4 = jsonChildNode.optString("price4");
                price5 = jsonChildNode.optString("price5");
                price6 = jsonChildNode.optString("price6");
                price7 = jsonChildNode.optString("price7");
                price8 = jsonChildNode.optString("price8");
                price9 = jsonChildNode.optString("price9");
                price10 = jsonChildNode.optString("price10");
                price11 = jsonChildNode.optString("price11");
                price12 = jsonChildNode.optString("price12");
                price13 = jsonChildNode.optString("price13");
                price14 = jsonChildNode.optString("price14");
                price15 = jsonChildNode.optString("price15");

                image = jsonChildNode.optString("image");

                justPrices = new String[]{price1, price2,
                        price3, price4, price5, price6, price7, price8, price9,
                        price10, price11, price12, price13, price14, price15};
                justPrices = new String[]{"1st Day Value " + price1, "2nd Day Value " + price2, "3rd Day Value " + price3, "4th Day Value " + price4, "5th Day Value " + price5,
                        "6th Day Value " + price6, "7th Day Value " + price7, "8th Day Value " + price8, "9th Day Value " + price9,
                        "10th Day Value " + price10, "11th Day Value " + price11, "12th Day Value " + price12, "13th Day Value " + price13, "14th Day Value " + price14, "15th Day Value " + price15};
                customList.add(new StockList(name, price, image, justPrices));

            }
        } catch (Exception e) {
            //Handle the main screen if the Internet is too slow and redirect user to refresh the screen
            Intent intent1 = new Intent(YourActivity.this,
                    RefreshActivity.class);
            startActivity(intent1);
            ListLoaderActivity.this.finish();
        }
        //I have created a Custom Adapter class that extends ArrayAdapter to build my ListView as i would like
        ArrayAdapter adapter = new MyStocksAdapter(YourActivity.this, R.layout.list_item, customList);
        adapter.notifyDataSetChanged();
        startList.setAdapter(adapter);
    }

The adapter Class:

public class MyStocksAdapter extends ArrayAdapter<StockList> {

    public MyStocksAdapter(Context context, int resource, List<StockList> objects) {
        super(context, resource, objects);
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {

        ViewHolderItems holder;
        View stockView = null;
        StockList current = null;

        if(stockView == null){
            LayoutInflater li = LayoutInflater.from(getContext());
            stockView = li.inflate(R.layout.list_item, parent, false);
            current = getItem(position);

            holder = new ViewHolderItems();
            holder.stockViewName = (TextView)stockView.findViewById(R.id.stock_name);
            holder.stockViewPrice = (TextView)stockView.findViewById(R.id.stock_price);
            holder.stockViewImage = (ImageView)stockView.findViewById(R.id.imagestartinglist);
            stockView.setTag(holder);
        }else{
            holder = (ViewHolderItems)stockView.getTag();
        }



    if(current != null){
                holder.stockViewName.setText(current.getStockCurrentName());
                holder.stockViewPrice.setText(current.getStockCurrentPrice());
                //Using a library to download images and then set them to imageview
Ion.with(holder.stockViewImage).placeholder(R.drawable.ic_chat).error(R.drawable.ic_chat).load(current.getStockImage());
            }
            return stockView;
        }

        static class ViewHolderItems {
            TextView stockViewName, stockViewPrice;
            ImageView stockViewImage;
        }
    }

The Class for each Item in the ListView:

public class StockList {

    private String stockCurrentName;
    private String stockCurrentPrice;
    private String stockImage;
    private String[] restPrices;

    public StockList(String stockCurrentName, String stockCurrentPrice, String stockImage, String[] restPrices) {
        this.stockCurrentName = stockCurrentName;
        this.stockCurrentPrice = stockCurrentPrice;
        this.stockImage = stockImage;
        this.restPrices = restPrices;
    }

    public String getStockCurrentName() {
        return stockCurrentName;
    }

    public void setStockCurrentName(String stockCurrentName) {
        this.stockCurrentName = stockCurrentName;
    }

    public String getStockCurrentPrice() {
        return stockCurrentPrice;
    }

    public void setStockCurrentPrice(String stockCurrentPrice) {
        this.stockCurrentPrice = stockCurrentPrice;
    }

    public String getStockImage() {
        return stockImage;
    }

    public void setStockImage(String stockImage) {
        this.stockImage = stockImage;
    }

    public String[] getRestPrices() {
        return restPrices;
    }

    public void setRestPrices(String[] restPrices) {
        this.restPrices = restPrices;
    }
}

Modify as you wish.

And the Layout for each list_item.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    android:fitsSystemWindows="true"
    android:descendantFocusability="blocksDescendants"
    android:layout_height="wrap_content">


<android.support.v7.widget.CardView
    android:id="@+id/card_view"
    android:layout_gravity="center"
    xmlns:card_view="http://schemas.android.com/apk/res-auto"
    android:layout_width="match_parent"
    card_view:cardBackgroundColor="@color/gold"
    android:layout_margin="5dp"
    card_view:cardCornerRadius="4dp"
    android:layout_height="80dp" >




    <LinearLayout
        android:orientation="horizontal"
        android:layout_width="wrap_content"
        android:padding="10dp"
        android:layout_height="wrap_content">

        <ImageView
            android:id="@+id/imagestartinglist"
            android:layout_width="65dp"
            android:layout_height="65dp"
            android:src="@drawable/ic_chat"/>

        <LinearLayout
            android:orientation="vertical"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:gravity="center_vertical|center_horizontal">

            <LinearLayout
                android:orientation="horizontal"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:gravity="center_vertical|center_horizontal">

                <TextView
                    android:id="@+id/textView2"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:layout_alignBaseline="@+id/stock_name"
                    android:layout_alignBottom="@+id/stock_name"
                    android:layout_marginLeft="23dp"
                    android:layout_toRightOf="@+id/imagestartinglist"
                    android:text="@string/stock_name"
                    android:textColor="#000"
                    android:gravity="center_vertical|center_horizontal" />

                <TextView
                    android:id="@+id/stock_name"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:layout_alignParentTop="true"
                    android:layout_marginLeft="15dp"
                    android:layout_toRightOf="@+id/textView2"
                    android:padding="6dp"
                    android:text="Small Text"
                    android:textAppearance="?android:attr/textAppearanceMedium"
                    android:textColor="#000"
                    android:gravity="center_vertical|center_horizontal" />

            </LinearLayout>

            <LinearLayout
                android:orientation="horizontal"
                android:layout_width="match_parent"
                android:layout_height="wrap_content"
                android:gravity="center_vertical|center_horizontal">

                <TextView
                    android:id="@+id/fake_name_day"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:layout_alignLeft="@+id/textView2"
                    android:layout_below="@+id/stock_name"
                    android:text="@string/current_price"
                    android:textAppearance="?android:attr/textAppearanceSmall"
                    android:textColor="#000"
                    android:gravity="center_vertical|center_horizontal" />

                <TextView
                    android:id="@+id/stock_price"
                    android:layout_width="wrap_content"
                    android:layout_height="wrap_content"
                    android:layout_alignBottom="@+id/fake_name_day"
                    android:layout_marginLeft="28dp"
                    android:textColor="#000"
                    android:layout_toRightOf="@+id/fake_name_day"
                    android:text="Large Text"
                    android:textAppearance="?android:attr/textAppearanceSmall"
                    android:gravity="center_vertical|center_horizontal" />

            </LinearLayout>

        </LinearLayout>

    </LinearLayout>
</android.support.v7.widget.CardView>
</RelativeLayout>

Hope i helped give you a small idea.

Answer 2

If you are talking about using your own or creating your own database, you can create a MySQL Database and write tables for your items. Then search, filter, add and delete items in your table using SQL queries.

You can also check out Parse which is a backend service with an Android SDK that allows you to store items and data online in the cloud.