Android MySQL Connectivity via JSON

The most common way to connect to a remote MySQL database from an android device, is to put some kind of service into the middle. Since MySQL is usually used together with PHP, the easiest and most obvious way to write a PHP script to manage the database and run this script using HTTP protocol from the android system.

Earlier in my post I have mentioned a way of connection via mysql connector Link

This example source code provides two way synchronization (Phone to Server and from Server to Phone)

Send Data from SQLite to MySQL

      //Retrieve query results in cursor format
      startManagingCursor(cs);  
      if(cs!=null)  
      {  
           cs.moveToFirst();  
           jsonarray = new JSONArray();  
           while (cs.isAfterLast() == false) {  
           json = new JSONObject();  
      try {  
           json.put("a",cs.getString(cs.getColumnIndex("a")));  
           json.put("b",cs.getString(cs.getColumnIndex("b")));  
           json.put("c",cs.getString(cs.getColumnIndex("c")));   
           jsonarray.put(json);  
           cs.moveToNext();  
      }  
      catch (Exception e) {  
           Log.d("Android", "JSON Error");  
      }  
           }  
           try {  
           // Create a new HttpClient and Post Header  
           HttpClient httpclient = new DefaultHttpClient();  
           HttpPost httppost = new HttpPost("http://192.168.1.1/set_file.php");  
           // Post the data:  
           StringEntity se = new StringEntity(jsonarray.toString());  
           httppost.setEntity(se);  
           httppost.setHeader("Accept", "application/json");  
           httppost.setHeader("Content-type", "application/json");  
           // Execute HTTP Post Request  
           System.out.print(json);  
           HttpResponse response = httpclient.execute(httppost);  
           // for JSON:  
           if(response != null)  
           {  
                InputStream is = response.getEntity().getContent();  
                BufferedReader reader = new BufferedReader(new InputStreamReader(is));  
                StringBuilder sb = new StringBuilder();  
                String line = null;  
                try {  
                     while ((line = reader.readLine()) != null) {  
                     sb.append(line + "\n");  
                     }  
                } catch (IOException e) {  
                     e.printStackTrace();  
                     }   
                finally {  
                     try {  
                          is.close();  
                     } catch (IOException e) {  
                                         e.printStackTrace();  
                                    }  
                               }  
                          }  
           }  
           catch (ClientProtocolException e) {                 
           } catch (IOException e) {   
        }            
      }  
      cs.close();

Retreive Data from MySQL to SQLite

 String result = "";  
        //http get  
        try{  
              lastsync = URLEncoder.encode(lastsync,"utf-8");  
           String address = url + "/get.php?variable1="+variable1+"&variable2="+variable2;  
           Log.v("Sync", address);  
           HttpClient httpclient = new DefaultHttpClient();  
           HttpGet httpget = new HttpGet(address);  
           response = httpclient.execute(httpget);  
           HttpEntity entity = response.getEntity();  
           is = entity.getContent();  
        }catch(Exception e){  
            Log.e("log_tag", "Error in http connection "+e.toString());   
        }  
        //convert response to string  
        try{  
            BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);  
            StringBuilder sb = new StringBuilder();  
            String line = null;  
            while ((line = reader.readLine()) != null) {  
                 Log.v("Test",line);  
                sb.append(line + "\n");  
            }  
            is.close();  
            result=sb.toString();  
        }catch(Exception e){  
            Log.e("log_tag", "Error converting result "+e.toString());   
        }  
        set_tms_logsheet();         
        if(result.length()==5)  
        {  
        }  
        else {  
        //parse json data  
        try{  
            JSONArray jArray = new JSONArray(result);  
            for(int i=0;i<jArray.length();i++){  
                JSONObject json_data = jArray.getJSONObject(i);  
                String querylog= "insert or replace into table(a,b,c) values('"+json_data.getString("a")+"','"+json_data.getString("b")+"','"+json_data.getString("c")+"')";    
                dbA.insert(querylog);  
            }   
        }  
        catch(JSONException e){  
            Log.e("log_tag", "Error parsing data "+e.toString());   
        }  
        }  

get.php Code (Push Data to Phone from MySQL)

 <?php   
      require_once('Connections/connection.php');   
      mysql_select_db($database_localhost,$con);  
      $timestamp = $_GET['timestamp'];  
      $query_search = "select * from table where a = '".$timestamp."' ";  
      $query_exec = mysql_query($query_search) or die(mysql_error());  
      if (mysql_errno()) {   
   header("HTTP/1.1 500 Internal Server Error");  
   echo $query.'\n';  
   echo mysql_error();   
      }  
      else {  
      if( $query_exec!=null){  
      while($row=mysql_fetch_assoc($query_exec))  
     $output[]=$row;  
      print(json_encode($output));       
      }       
      else {echo "No Data"; }  
      mysql_close();  
 }  
 ?>  

set.php Code (Push Data to MySQL form SQLite)

 <?php  
      $json = file_get_contents('php://input');  
      $data = json_decode($json);  
      require_once('Connections/connection.php');   
      mysql_select_db($database_localhost,$con);  
      foreach ($data as $id => $jsons) {  
      $query= "insert into table(a,b,c) values(".$jsons->a.",".$jsons->b.",".$jsons->c.",'".$jsons->c."')";  
      //echo $query;  
      echo "\n";  
      $query_exec = mysql_query($query) or die(mysql_error());  
      }  
      mysql_close();  
      echo "Success";  
 ?>  

Connection.php (Database Connection)

 <?php  
 $hostname_localhost ="localhost:3306";  
 $database_localhost ="dbname";  
 $username_localhost ="username";  
 $password_localhost ="password";  
 $con = mysql_connect($hostname_localhost,$username_localhost,$password_localhost)  
 or  
 trigger_error(mysql_error(),E_USER_ERROR);  
 ?>