MySQL FULL JOIN

Here is a quick example on FULL JOIN in MySQL database.
FULL JOIN concept in MySQL is to return rows when there is a match in one of the tables. Further FULL JOIN or FULL OUTER JOIN is UNION of LEFT JOIN and the RIGHT JOIN of the both tables.
Consider that you have following two tables.

Table 1 : Persons

Eg : queries to create and populate the Persons table.

CREATE TABLE Persons
 (P_Id int, Name varchar(1));
 
INSERT INTO Persons
 (P_Id, Name)
VALUES
 (1, 'A'),
 (2, 'B'),
 (3, 'C'),
 (4, 'D'),
 (5, 'E');

Table 2 : Invoice


Eg : queries to create and populate Invoice table.

CREATE TABLE Invoice
 (Id int, P_Id int);
 
INSERT INTO Invoice
 (Id, P_Id)
VALUES
 (111, 3),
 (112, 3),
 (113, 1),
 (114, 1),
 (115, 15);

Now think that we want to join rows of both these tables on the condition of same P_Id. To perform that we need to get the UNION of RIGHT JOIN and the LEFT JOIN of both tables.
Here is the query to FULL JOIN operation.

SELECT Persons.Name, Persons.P_Id, Invoice.Id
FROM Persons
LEFT JOIN Invoice
  ON Persons.P_Id=Invoice.P_Id
UNION
SELECT Persons.Name, Persons.P_Id, Invoice.Id
FROM Persons
RIGHT JOIN Invoice
  ON Persons.P_Id=Invoice.P_Id

Resulting table :

I think that you can understand what happened here.

1. First result of LEFT OUTER JOIN of both tables.

2. Then result of RIGHT OUTER JOIN of both tables.

3. UNION of these two tables means rows when there is a match in one of the tables or FULL JOIN of these two tables.

If you understand the concept try to use following query to FULL JOIN these tables. Same concept and same result.

SELECT Persons.Name, Persons.P_Id, Invoice.Id
FROM Persons
LEFT JOIN Invoice
  ON Persons.P_Id=Invoice.P_Id
UNION
SELECT Persons.Name, Persons.P_Id, Invoice.Id
FROM Invoice
LEFT JOIN Persons
  ON Persons.P_Id=Invoice.P_Id

Thank you for watching. Always your ideas are welcome.
Happy coding!

Make Android Edit text field allows only for selected characters

There is a simple method to make a restrict or a limit on allowed characters in Android edit text field.
Eg: Suppose that you want to make a restrict where user only allows to type  a,e,i,o,u. You can easily do this by using following line in your layout.xml file

android:digits="aeiou"

Eg: (I used edit text in relative layout)

     <EditText
         android:id="@+id/editText1"
         android:digits="aeiou"
         android:layout_width="wrap_content"
         android:layout_height="wrap_content"
         android:layout_alignParentLeft="true"
         android:layout_alignParentTop="true"
         android:layout_marginLeft="54dp"
         android:layout_marginTop="123dp" >
         <requestFocus />
     </EditText>

How to enable internal web browser in Eclipse Juno on Ubuntu

Some times Eclipse default internal web browser will not work in your Ubuntu environment. Is that the case, you need to install webkit library for Ubuntu.
In order to install webkit libraries got to your terminal and run this command.

sudo apt-get install libwebkitgtk-1.0-0

This may take a while. After finishing the installation you should be able to open Eclipse internal web browser.

Window >> Show View >> Other >> General >> Internal Web Browser.

Enjoy :D

Android PHP JSON tutorial

Here I'm going to demonstrate how we can decode JSON encoded data in Android using very simple example. I used Android 2.2 for this.

First here is some extra information about JSON:

1. What is JSON

• JSON stands for JavaScript Object Notation
• JSON is lightweight text-data interchange format
• JSON is language independent 
• JSON is "self-describing" and easy to understand
                                                         (from w3schools)  

2. Click here to know benefits of JSON over XML

Now let's see simple example of JSON reading in Android.
In my project there is a PHP web service which encodes a string array in to JSON format. I'm going to access that web service using Android http client and then it decodes json data. I have tested this on Android 2.2

1. Here is the code for PHP

<?php
$data = array('name' => 'Froyo', 'version' => 'Android 2.2'); 
print (json_encode($data)); 
?> 

2. Contents of Android Layout Manifest and Activity files
 main.xml file

<?xml version="1.0" encoding="utf-8"?>
<AbsoluteLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
        android:id="@+id/textView1"
        android:layout_width="216dp"
        android:layout_height="36dp"
        android:layout_weight="1"
        android:layout_x="66dp"
        android:layout_y="192dp"
        android:textAppearance="?android:attr/textAppearanceMedium" />

</AbsoluteLayout>

manifest.xml 
You need to add internet permissions 
 

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.json.php"
    android:versionCode="1"
    android:versionName="1.0" >

    <uses-sdk android:minSdkVersion="8" />
    <uses-permission android:name="android.permission.INTERNET"/>

    <application
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name" >
        <activity
            android:name=".JSONExampleActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>

Android activity 

package com.json.php;

import android.app.Activity;
import android.os.Bundle;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import android.widget.TextView;
import org.apache.http.HttpResponse;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.json.JSONException;
import org.json.JSONObject;


public class JSONExampleActivity extends Activity {
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        HttpClient httpclient = new DefaultHttpClient();
        HttpPost httppost = new HttpPost("http://codeincloud.tk/json_android_example.php");
        TextView textView = (TextView)findViewById(R.id.textView1);
  try {
   
   HttpResponse response = httpclient.execute(httppost);
   String jsonResult = inputStreamToString(response.getEntity().getContent()).toString();
   JSONObject object = new JSONObject(jsonResult);

   String name = object.getString("name");
      String verion = object.getString("version");
      textView.setText(name + " - " + verion);
      
  } 
  catch (JSONException e) {
   e.printStackTrace();
  } 
  catch (ClientProtocolException e) {
   e.printStackTrace();
  } 
  catch (IOException e) {
   e.printStackTrace();
  }


       }
    private StringBuilder inputStreamToString(InputStream is) {
        String rLine = "";
        StringBuilder answer = new StringBuilder();
        BufferedReader rd = new BufferedReader(new InputStreamReader(is));
         
        try {
         while ((rLine = rd.readLine()) != null) {
          answer.append(rLine);
           }
        }
         
        catch (IOException e) {
            e.printStackTrace();
         }
        return answer;
       }
}

 Result

Click here to download the Android project.

That's it
 Happy coding! :)