Insert new Record on Android online Database JSON

INTRODUCTIONThis tutorial will show how to insert a new record to an online table in MySQL, through a web server with PHP as the midle-tier. The client interface is Android.

We are going to prepare a web server using your machine, with MySQL database. The database scheme is as follow; Database server: localhost Database name: idioms Table : idiomlist {id, entry, meaning}

STEP 1: THE BACK-END & DATABASE (read more…)

This tutorial uses XAMP as the back-end. You need to download the XAMPP installer here (https://www.apachefriends.org/download.html) and install to C:\ . XAMPP contains Apache web server, PHP interpreter and MySQL database server.

Then turn the Apache and MySQL server.

Preparing the Database on localhost/phpMyAdmin – Your computer now has become a web server. GO to your browser and ask for http://localhost/phpmyadmin to access the MySQL database. Create a new database name idioms as shown in the figure below.

Than copy paste the SQL code below to create a table name idiomlist .

CREATE TABLE IF NOT EXISTS `idiomslist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry` varchar(255) NOT NULL,
  `meaning` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Once the table successfully created, you will see the new table is in the database.

The PHP server script to produce JSON data format.

Save all PHP script file in the wwwroot of XAMPP, normally at c->xampp->htdocs . Create a new folder named idiomjson in the htdocs. Inside this file you need to have these 3 PHP files; config.php, connect.php and insertnew.php.



config.php – the file contain the database info

<?php
/*
 * All database connection variables
 */
 
define('DB_USER', "root"); // db user (xampp default root)
define('DB_PASSWORD', ""); // db password (normally null in xampp)
define('DB_DATABASE', "idioms"); // database name
define('DB_SERVER', "localhost"); // db server

?>

connect.php – is the object to do database connection.

<?php
 
/**
 * A class file to connect to database
 */
class DB_CONNECT {
 
    // constructor
    function __construct() {
        // connecting to database
        $this->connect();
    }
 
    // destructor
    function __destruct() {
        // closing db connection
        $this->close();
    }
 
    /**
     * Function to connect with database
     */
    function connect() {
        // import database connection variables
        require_once __DIR__ . '/config.php';
 
        // Connecting to mysql database
        $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or die(mysql_error());
 
        // Selecing database
        $db = mysql_select_db(DB_DATABASE) or die(mysql_error()) or die(mysql_error());
 
        // returing connection cursor
        return $con;
    }
 
    /**
     * Function to close db connection
     */
    function close() {
        // closing db connection
        mysql_close();
    }
 
}
 
?>

insertnew.php – here is all the operations to INSERT a new record.

<?php
//insertnew.php
/*
 * Following code will create a new product row
 * All product details are read from HTTP GET Request
 */
 
// array for JSON response
$response = array();
 
// check for required fields
if (isset($_GET['entry']) && isset($_GET['meaning'])) {
 
    $entry = $_GET['entry'];
    $meaning = $_GET['meaning'];
 
    // include db connect class
    require_once __DIR__ . '/connect.php';
 
    // connecting to db
    $db = new DB_CONNECT();
 
    // mysql inserting a new row (idioms)
    $result = mysql_query("INSERT INTO idiomlist(entry, meaning) VALUES('$entry', '$meaning')");
 
    // check if row inserted or not
    if ($result) {
        // successfully inserted into database
        $response["success"] = 1;
        $response["message"] = "new IDIOM saved....";
 
        // echoing JSON response
        echo json_encode($response);
    } else {
        // failed to insert row
        $response["success"] = 0;
        $response["message"] = "Oops! An error occurred.";
 
        // echoing JSON response
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}
?>

The URL of the PHP script

First locate the IP number of your computer. Bare in mind to test your server, the PC and the smartphone must use the same WIFI connection. To determine PC IP address, try the instruction  in this website –> https://kb.iu.edu/data/aapa.html

I tried using the CMD (command line), run the ipconfig instruction and I get the following;

In my case my PC IP address is 172.16.16.183, so the URL for my insertnew.php is at http://172.16.16.183/idiomjson/insertnew.php

STEP 2: PREPARING THE ANDROID CLIENTCreate a new Android project (AddNewRecordJSON)

(click to enlarge)

UI Layout for Inserting a New Record

Use the TableLayout to ease the UI placement. There are two EditText; namely txtnewidiom and txtmeaning, and a button btnsavenew.

JSONParser.java - JSON Parser class is the class to handle JSON data format parsing.
Insert a new class by right clicking the package.

And copy-paste the following code for the JSONParser class.

package net.kerul.addnewrecordjson;//change to your package name

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.util.List;
 
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.utils.URLEncodedUtils;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONException;
import org.json.JSONObject;
 
import android.util.Log;
 
public class JSONParser {
 
    static InputStream is = null;
    static JSONObject jObj = null;
    static String json = "";
 
    // constructor
    public JSONParser() {
 
    }
 
    // function get json from url
    // by making HTTP POST or GET mehtod
    public JSONObject makeHttpRequest(String url, String method,
            List<NameValuePair> params) {
 
        // Making HTTP request
        try {
 
            // check for request method
            if(method == "POST"){
                // request method is POST
                // 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();
 
            }else if(method == "GET"){
                // request method is GET
                DefaultHttpClient httpClient = new DefaultHttpClient();
                String paramString = URLEncodedUtils.format(params, "utf-8");
                url += "?" + paramString;
                HttpGet httpGet = new HttpGet(url);
 
                HttpResponse httpResponse = httpClient.execute(httpGet);
                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();
        } 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;
 
    }
}

Now for the controller of the UI, edit the AddNewIdiom.java file. This is the main Java code for the app.

package net.kerul.addnewrecordjson;//change to your package name

import android.os.Bundle;
import android.app.Activity;
import android.view.Menu;
import android.view.View.OnClickListener;

import java.util.ArrayList;
import java.util.List;
 
import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONException;
import org.json.JSONObject;
 
import android.app.Activity;
import android.app.ProgressDialog;
import android.content.Intent;
import android.os.AsyncTask;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
 
public class AddNewIdiom extends Activity implements OnClickListener {
 
    // Progress Dialog
    private ProgressDialog pDialog;
 
    JSONParser jsonParser = new JSONParser();
    private EditText txtnewidiom;
    private EditText txtmeaning;
    private Button btnsavenew;
    private int success;//to determine JSON signal insert success/fail
 
    // url to insert new idiom (change accordingly)
    private static String url_insert_new = "http://172.16.16.183/idiomjson/insertnew.php";
 
    // JSON Node names
    private static final String TAG_SUCCESS = "success";
 
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_add_new_idiom);
 
        // Edit Text
        txtnewidiom = (EditText) findViewById(R.id.txtnewidiom);
        txtmeaning = (EditText) findViewById(R.id.txtmeaning);
 
        // Save button
        btnsavenew = (Button) findViewById(R.id.btnsavenew);
        // button click event
        btnsavenew.setOnClickListener(this);
        
    }
    @Override
    public void onClick(View v) {
        if (v.getId()==R.id.btnsavenew){
            //call the InsertNewIdiom thread
            new InsertNewIdiom().execute();
            if (success==1){
                Toast.makeText(getApplicationContext(), "New idiom saved...", Toast.LENGTH_LONG).show();
            }else{
                Toast.makeText(getApplicationContext(), "New idiom FAILED to saved...", Toast.LENGTH_LONG).show();
            }
        }
        
    }
 
    /**
     * Background Async Task to Create new Idioms
     * */
    class InsertNewIdiom extends AsyncTask<String, String, String> {
        //capture values from EditText
        String entry = txtnewidiom.getText().toString();
        String meaning = txtmeaning.getText().toString();
 
        /**
         * Before starting background thread Show Progress Dialog
         * */
        @Override
        protected void onPreExecute() {
            super.onPreExecute();
            pDialog = new ProgressDialog(AddNewIdiom.this);
            pDialog.setMessage("Saving the new IDIOM ("+entry+")...");
            pDialog.setIndeterminate(false);
            pDialog.setCancelable(true);
            pDialog.show();
        }
 
        /**
         * Inserting the new idiom
         * */
        protected String doInBackground(String... args) {
            
 
            // Building Parameters
            List<NameValuePair> params = new ArrayList<NameValuePair>();
            params.add(new BasicNameValuePair("entry", entry));
            params.add(new BasicNameValuePair("meaning", meaning));
 
            // getting JSON Object
            // Note that create product url accepts GET method
            JSONObject json = jsonParser.makeHttpRequest(url_insert_new,
                    "GET", params);
 
            // check log cat from response
            Log.d("Insert New Idiom Response", json.toString());
 
            // check for success tag
            try {
                success = json.getInt(TAG_SUCCESS);
 
                if (success == 1) {
                    // successfully save new idiom
                } else {
                    // failed to add new idiom
                }
            } catch (JSONException e) {
                e.printStackTrace();
            }
 
            //return null;
            return null;
        }
 
        /**
         * After completing background task Dismiss the progress dialog
         * **/
        protected void onPostExecute(String file_url) {
            // dismiss the dialog once done
            pDialog.dismiss();
        }
 
    }
}

AndroidManifest.xml - And don’t forget to add Internet UsesPermission in the AndroidManifest. As it needs to use the Internet connection for the device client to access the server.