Thursday, September 8, 2011

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);  
 ?>  

Wednesday, September 7, 2011

Google Weather API Widget (PHP) (No longer Working)




Note: Google has killed the free hidden weather API so this method no longer works

The Google Weather API is a hidden service for developers to get weather data for any location with ease.
Indeed, you just simply pass a city name or postal code, such as this Mountain View

Query by City
http://www.google.com/ig/api?weather=Mountain+View   

Query by Coordinates
http://www.google.com/ig/api?weather=,,,30670000,104019996 

To get an XML response like this:


This is the php code to get a widget like shown above. Line 20 defines the location for the widget.
Either you can use postal code, city or coordinates multiplied by 1000000 (Check Line No 21,22,23,25)

1:  <?php  
2:  function convert($temp)  
3:  {  
4:    // Converting Fahrenheit To Celsius, vice versa  
5:    global $config;  
6:    $temperature = $temp;  
7:    if( strtoupper($config['base-temp-unit']) == 'F' && strtoupper($config['display-temp-unit']) == 'C' )  
8:    {  
9:      // Converting Fahrenheit To Celsius  
10:      $temperature = round((5/9)*($temp-32));  
11:    }  
12:    if( strtoupper($config['base-temp-unit']) == 'C' && strtoupper($config['display-temp-unit']) == 'F' )  
13:    {  
14:      // Converting Celsius to Fahrenheit  
15:      $temperature = round((9/5)*$temp+32);  
16:    }  
17:    return $temperature;  
18:  }  
19:  $url = "http://www.google.com";  
20:  $location = "colombo"; // <city>,<country code>  
21:  $lat;  
22:  $lng;  
23:  //$location = ",,,30670000,104019996"; // Coordinates
24:  $weather_url = "{$url}/ig/api?weather={$location}";  
25:  //$weather_url = "{$url}/ig/api?weather=,,,{$lat},{$lng}";  
26:  $config['base-temp-unit'] = 'F'; // F=Fahrenheit, C=Celsius  
27:  $config['display-temp-unit'] = 'C'; // F=Fahrenheit, C=Celsius  
28:  if( $xmlData = file_get_contents($weather_url) )  
29:  {  
30:    $xml = new SimpleXMLElement($xmlData);  
31:    $eol = "\r\n";  
32:    // Display basic information  
33:       echo("<table width='70%' border='0' cellspacing='0' cellpadding='5'>");  
34:       echo("<tr>");  
35:       echo("<td width='11%' rowspan='4' align='center' valign='middle'>");  
36:       echo("<img src='{$url}{$xml->weather->current_conditions->icon->attributes()}' alt='' width='60' height='60' border='0' style='margin-right: 3px; vertical-align: top;>'");  
37:       echo("</td>");  
38:       echo("<td width='33%' align='left' valign='top'>{$xml->weather->current_conditions->temp_c->attributes()} C</td>");  
39:       echo("<td width='56%' rowspan='4' align='left' valign='top'><table width='100%' border='0' cellspacing='0' cellpadding='5' style='border-left:1px solid #CCCCCC'>");  
40:       echo("<tr>");  
41:       foreach( $xml->weather->forecast_conditions as $i => $result )  
42:    {  
43:       echo("<td align='center'>{$result->day_of_week->attributes()}</td>");  
44:       }  
45:       echo("</tr><tr>");  
46:       foreach( $xml->weather->forecast_conditions as $i => $result )  
47:    {  
48:       echo("<td align='center'><img src='{$url}{$result->icon->attributes()}'></td>");  
49:       }  
50:       echo("</tr><tr>");  
51:       foreach( $xml->weather->forecast_conditions as $i => $result )  
52:    {  
53:       echo("<td align='center'><table width='100%' border='0' cellspacing='0' cellpadding='5'><tr><td align='center'>".convert($result->high->attributes())." ".strtoupper($config['display-temp-unit'])."</td><td align='center'>".convert($result->low->attributes())." ".strtoupper($config['display-temp-unit'])."</td></tr></table></td>");  
54:       }  
55:       echo("</tr></table></td></tr><tr>");  
56:       echo("<td>{$xml->weather->current_conditions->condition->attributes()}</td>");  
57:       echo("</tr><tr>");  
58:       echo("<td>{$xml->weather->current_conditions->wind_condition->attributes()}</td>");  
59:       echo("</tr><tr>");  
60:       echo("<td>{$xml->weather->current_conditions->humidity->attributes()}</td>");  
61:       echo("</tr></table>");  
62:  }  
63:  ?>