Working with your MySql database server using PHP, JSON

Author: Hoang Truong Dinh – S3275068
Tang Vinh Thanh – s3245715
Course: COSC2453 – Mobile Application Development
RMIT International University Vietnam
Lecturer: George Nguyen

Introduction

This document will introduce how to working around with your MySql database by you Android application.

When to apply this ?

If you want to store and retrieve data from MySql database server. You will need to implement a PHP API to receive and send data between your Android application and Database Server

For sending data to server we can user HTTP request like POST, for sending from API to your Android application you can use JSON, and use JSON_parse to convert JSONObject to your application’s object.

Advantages and Disadvantages

JSON is very popular nowadays because of its lightweight and easy to implement. However you need to do a little research in order to make it work smoothly

It is a little bit slow to transfer data from server to your application.

Requirements

You need to have database server, an API and your application to do this.

Steps

Here is the steps you need to implement for working with your MySql database

I. Building PHP API

1. Create DB_Connect.php file

2. Create DB_Functions.php file

3. Create index.php file

Copy all of file to your php server, you need remember your php api path

II. Create Class needed in your application

1. Create JSON_Parse.class file

public class JSONParser {

    static InputStream is = null;
    static JSONObject jObj = null;
    static String json = "";
    public JSONParser() {

    }

    public JSONObject getJSONFromUrl(String url, List params) {

        // Making HTTP request
        try {
            // defaultHttpClient
            DefaultHttpClient httpClient = new DefaultHttpClient();
            HttpPost httpPost = new HttpPost(url);
            httpPost.setEntity(new UrlEncodedFormEntity(params));

            HttpResponse httpResponse = httpClient.execute(httpPost);
            HttpEntity httpEntity = httpResponse.getEntity();
            is = httpEntity.getContent();

        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (ClientProtocolException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        try {
            BufferedReader reader = new BufferedReader(new InputStreamReader(
                    is, "iso-8859-1"), 8);
            StringBuilder sb = new StringBuilder();
            String line = null;
            while ((line = reader.readLine()) != null) {
                sb.append(line + "n");
            }
            is.close();
            json = sb.toString();
            Log.e("JSON", json);
        } catch (Exception e) {
            Log.e("Buffer Error", "Error converting result " + e.toString());
        }

        // try parse the string to a JSON object
        try {
            jObj = new JSONObject(json);
        } catch (JSONException e) {
            Log.e("JSON Parser", "Error parsing data " + e.toString());
        }

        // return JSON String
        return jObj;

    }
}

2. Create Class User_Functions for store function to send and receive JSON

public class UserFunctions {

    private JSONParser jsonParser;
 	//Change URL to your php api path
    private static String URL = "http://mekong.rmit.edu.vn/~s3275068/ah_login_api/";

    public UserFunctions(){
        jsonParser = new JSONParser();
    }

    public JSONObject getUserByUserName(String username){
        // Building Parameters
        List params = new ArrayList();
        // tag must match with the tag in PHP API
        params.add(new BasicNameValuePair("tag", "getuser"));
        params.add(new BasicNameValuePair("username", username));

		JSONObject json = jsonParser.getJSONFromUrl(URL, params);
        return json;
    }

    public JSONObject listAllUsers() {
		List params = new ArrayList();
		params.add(new BasicNameValuePair("tag", "listall"));

		JSONObject json = jsonParser.getJSONFromUrl(URL, params);
		return json;
	}
    
}

3. Example Function Convert JSON to your Object

public User getUser(String username){
     UserFunctions uf = new UserFunctions();
     JSONObject json = uf.getUserByUserName(username);
     String res = json.getString("success");
     if (Integer.parseInt(res) == 1){
     	 JSONObject json_user = json.getJSONObject("user");

     	 return new User(json_user.getString("username"), json_user.getString("password");
     }
     return null;
}

public ArrayList getAllUser(){
	ArrayList userlist = new ArrayList ();
	UserFunctions uf = new UserFunctions();
    JSONObject json = uf.listAllUsers();
    String res = json.getString("success");
    if(Integer.parseInt(res) == 1){
    	JSONArray json_user_array = json.getJSONArray("list");
    	for (int i = 0; i < json_user_array.length(); i++) {
				JSONObject jobj = (JSONObject) json_user_array.get(i);
				userlist.add(new User(jobj.getString("username"), jobj.getString("fullname")));
		}
    }
    return userlist;
}

REFERENCES
http://www.androidhive.info/2012/01/android-login-and-registration-with-php-mysql-and-sqlite/