How do I retrieve a columns value from a table which is stored on a server?

Question

I have an android application. I have registered my domain for free on webhost. My domain name is "www.xyz.site" On webhost,in MySQL I have a database which has a table called "users", which contain a column called "amount". In one of my activity "Payment1Activity.java" of my android application I want to take that value from server and deduct some particular amount from it which is a textview in one of my xml file "perryroad.xml" and save the new amount back to my "amount" column of table "Users". My activity Payment1Activity.java is as follow :

package com.example.streetsystemparking;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.ResponseHandler;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.BasicResponseHandler;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONObject;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
import android.widget.RemoteViews;
//import com.example.streetsystemparking.MainActivity; 


public class Payment1Activity extends Activity {
Button b,br1;
EditText et,pass;
TextView tv;
HttpPost httppost;
StringBuffer buffer;
HttpResponse response;
HttpClient httpclient;
List<NameValuePair> nameValuePairs;
ProgressDialog dialog = null;
String status;
String uid;

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_payment1);
    b = (Button)findViewById(R.id.Button01);  
    et = (EditText)findViewById(R.id.accountno);
    pass= (EditText)findViewById(R.id.password);
   // tv = (TextView)findViewById(R.id.tv);

    b.setOnClickListener(new OnClickListener() {


        @Override
        public void onClick(View v) {

            dialog = ProgressDialog.show(Payment1Activity.this, "", 
                    "Validating user...", true);
             new Thread(new Runnable() {
                    public void run() {
                        payment();                        
                    }
                  }).start();               
        }
    });
 }

void payment(){
try{            

httpclient=new DefaultHttpClient();
httppost= new HttpPost("http://tanushreedutta.site40.net/payment_new/check.php");
        //add your data
nameValuePairs = new ArrayList<NameValuePair>(2);
nameValuePairs.add(new BasicNameValuePair("accno",et.getText().toString().trim()));                                         
    nameValuePairs.add(new BasicNameValuePair("bpassword",
                                 pass.getText().toString().trim())); 
httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
        //Execute HTTP Post Request
response=httpclient.execute(httppost);
ResponseHandler<String> responseHandler = new BasicResponseHandler();
final String response = httpclient.execute(httppost, responseHandler);
        //System.out.println("Response : " + response); 
runOnUiThread(new Runnable() {
public void run() {
                //tv.setText("Response from PHP : " + response);
dialog.dismiss();
            }
        });
if(response.startsWith("User Found")){
            new Thread(new Runnable() {
                public void run() {

                    update();                         
                }

              }).start();   

    runOnUiThread(new Runnable() {
    public void run() {
        //Here I want to take that amount from mysql table and subtract the textview          
          (taken from perryroad.xml) value from it and store that new value back to                
          mysql table.                      
        Toast.makeText(Payment1Activity.this,"Payment Successful for block 1", 
                      Toast.LENGTH_SHORT).show();
Toast.makeText(Payment1Activity.this,"You reserved block 
                          1",Toast.LENGTH_LONG).show();

                    setResult(1); 
                }
            });
startActivity(new Intent (Payment1Activity.this,VacatingCredentials.class));


        }else{
            showAlert();        
        }

    }catch(Exception e){
        dialog.dismiss();
        System.out.println("Exception : " + e.getMessage());
    }
}
 public void update()
    {
        try
        {
        uid="1";
    status="2";
    HttpClient httpclient = new DefaultHttpClient();
HttpPost httppost = new HttpPost("http://tanushreedutta.site40.net/map/map.php");
            nameValuePairs = new ArrayList<NameValuePair>(2);

            nameValuePairs.add(new BasicNameValuePair("uid",uid));
            nameValuePairs.add(new BasicNameValuePair("status",status));
            httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
            response = httpclient.execute(httppost); 
           ResponseHandler<String> responseHandler = new BasicResponseHandler();
    final String response = httpclient.execute(httppost, responseHandler);
           // HttpEntity entity = response.getEntity();
           // is = entity.getContent();
            Log.e("pass 1", "connection success ");
        }
        catch(Exception e)
        {
            Log.e("Fail 1", e.toString());
            Toast.makeText(getApplicationContext(), "Invalid IP Address",
            Toast.LENGTH_LONG).show();
        }     
    }
public void showAlert(){
    Payment1Activity.this.runOnUiThread(new Runnable() {
        public void run() {
     AlertDialog.Builder builder = new AlertDialog.Builder(Payment1Activity.this);
       builder.setTitle("Payment Error.");
       builder.setMessage("User not Found.")  
       .setCancelable(false)
       .setPositiveButton("OK", new DialogInterface.OnClickListener() {
       public void onClick(DialogInterface dialog, int id) {
           Toast.makeText(Payment1Activity.this,"Invalid Account number or Password,Try 
                         Again",Toast.LENGTH_LONG).show();
                            }
                   });                     
            AlertDialog alert = builder.create();
            alert.show();               
        }
    });
   }

    }

My perryroad.xml file is as follow :

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:background="@drawable/p5" >

<TextView
    android:id="@+id/textView1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentTop="true"
    android:layout_marginLeft="10dp"
    android:layout_marginTop="40dp"
    android:text="Address: Perry Road, Bandra" 
    android:textAppearance="?android:attr/textAppearanceLarge" />

<TextView
    android:id="@+id/textView2"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_marginLeft="103dp"
    android:orientation="horizontal"
    android:text="West, Mumbai"
    android:textAppearance="?android:attr/textAppearanceLarge" />

 <TextView
    android:id="@+id/textView3"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
     android:layout_marginTop="25dp"
    android:layout_marginLeft="10dp"
    android:orientation="horizontal"
    android:text="Parking charges: Rs."
    android:textAppearance="?android:attr/textAppearanceLarge" />

 <TextView
     android:id="@+id/textView4"
     android:layout_width="wrap_content"
     android:layout_height="wrap_content"
     android:layout_marginLeft="220dp"
     android:orientation="horizontal"
     android:text="20"
     android:textAppearance="?android:attr/textAppearanceLarge" />

<Button
    android:id="@+id/b1"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@+id/editText1"
    android:layout_gravity="center"
    android:orientation="horizontal"
    android:layout_marginTop="50dp"
    android:text="Get Direction"
    android:textColor="#000000"
    android:textAppearance="?android:attr/textAppearanceLarge" />

<Button
    android:id="@+id/b2"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_marginTop="50dp"
    android:text="Get Map"
    android:layout_gravity="center"
     android:textColor="#000000"
    android:textAppearance="?android:attr/textAppearanceLarge" />

  </LinearLayout>

I want to access "20" from this textview4 through getText() so what is the syntax for that ? Can anyone help me with this ? Any suggestions or advices will be highly appreciated. Thank you.

Answer 1

There are few ways to achieve this functionality, it depends upon your level of expertise and the kind of data you want to get. I will give you few basic steps but you need to implement something and try it out.

steps for you client side coding; i.e. in your java class.

1. Create HttpClient

2. Create HttpGet or HttpPost and initialize it with the get or post URL. (again get or post is your choice depending upon kind of data)

3. execute your get/post object and you will have the response as object of HttpResponse

4. you can then use concepts of buffer reader, string builder, input stream, etc to fetch desired data.

steps for your server side code i.e. usually php file on server

-- you can use XML, JSON or any other already defined approach to fetch the data for that you will have to correctly encode JSON and then decode it on your client side code with JSONparser classes similar is the concept with XML

-- easier way if you are a beginner, just try to return one row at a time from server using mysql query and using 'echo' in php. use post method and pass different parameters to server, write different kind of mysql queries to get desired result. once you get along with them you can move on to the concepts of JSON. if you never done JSON before and will try to do it directly it might look bit confusing.

also please mention specific queries while posting question as this is very wide topic and can not be answered in one post.

Edit: don't forget to specify the permission to use internet in your manifest file and also use AsynkTask and threading to perform time consuming tasks

Edit 2 according to your comment you want to access one column value for certain user, is it right? for eg you have 5 columns; id, name, surname, password, amount and you only want to retrieve the amount for certain user. if this is what you are looking for then you need to write a mysql query such a way that it only returns column value for particular row (this needs to be done on server side in your php) or you can only fetch column value that you need from the JSON response by it's id (this needs to be done client side while parsing JSON response)

I have written an answer here on stakoverflow which is a basic example. if you look at my example in that I retrieve name and surname in JSON response (from server in php) but while parsing JSON object I only fetch surname by its id 'surname' (on client side in JSON parser code).

keep in mind that was just an example and if you have large database and you retrieve all data each time it will increase internet usage and will also be time consuming with slow internet connection. best way for professional applications is to write separate php files with mysql queries that only return necessary value/s.

I also did not understand what did you mean by your last 2-3 lines in your question can you please elaborate (question after the code).

PS: It would be also a good practice to use AsyncTask while developing android applications as they are dedicated threading mechanism for android.

Please upvote if my answer has helped you in anyway.