samedi 5 janvier 2013

Populate an Android Spinner with JSON data from a RESTful web API

In the following tutorial I will explain how to populate and Android Spinner (almost equivalent to a java combo box) using JSON data fetched from a REST API. This tutorial DOES NOT explain how to set up and android app or components of an android app; it only explains how to populate an Android spinner. Hoping we are clear with what this does let's begin.

I will use JSON data from the following RESTful API http://www.cmcredit.com/apps/apis/map/

It is a country/region/city database I put together from various sources and you query by sending some NVP parameters. I will also explain how to do this in our android application. Just as a quick example calling the API with the NVP {call: countrylist} will do the get request: http://www.cmcredit.com/apps/apis/map/?call=countrylist and return a country listing. To get regions in a country you could call the api with the NVPs {call: countryregions, country_id: 42}. This API is used for demo purposes ONLY.

We will use data returned from http://www.cmcredit.com/apps/apis/map/?call=countrylist which is of the following format:
{
 "success":true,
 "message":"",
 "data":
  [
   {"country_id":"42","name":"cameroon","iso2":"CM","currency":"XAF"},
   {"country_id":"43","name":"canada","iso2":"CA","currency":"CAD"},
   {"country_id":"44","name":"cape verde","iso2":"CV","currency":"CVE"},
   {"country_id":"45","name":"cayman islands","iso2":"KY","currency":"KYD"},
   {"country_id":"46","name":"central african republic","iso2":"CF","currency":"XAF"},
   {"country_id":"47","name":"chad","iso2":"TD","currency":"XAF"}
  ]
}


Create the various Layouts
1. Activity layout: one TextView and one Spinner (res/layout/main.xml)


 
  

    
  


The TextView will simply display the text of the android:text attribute on the screen and the spinner will be populated by our Activity class.

2. The layout of each item in the spinner (res/layout/spinner_item.xml)



    
    
    




Define the Activity class
Secondly we define our activity class that will be launched when you start your application (if you set it as the main activity in the AndroidManifest.xml)

SpinnerDemo.java
package com.canda.android.spinnerdemo;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutionException;

import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.Spinner;
import android.widget.Toast;
import android.app.Activity;

public class SpinnerDemo extends Activity {
 // JSON Node names
 private static final String TAG_DATA = "data";
 private static final String TAG_ID_COUNTRY = "country_id";
 private static final String TAG_NAME = "name";
 private static final String TAG_ISO = "iso2";
 private static final String TAG_CURRENCY = "currency";
 private static final String MAP_API_URL = "http://www.cmcredit.com/apps/apis/map";
 private BackGroundTask bgt;

 // Fields
 Spinner countryField;

 ArrayList countryList = new ArrayList();

 @Override
 public void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_home);
  buildCountryDropDown();
 }

 public void buildCountryDropDown() {
  // Building post parameters, key and value pair
  List apiParams = new ArrayList(1);
  apiParams.add(new BasicNameValuePair("call", "countrylist"));

  bgt = new BackGroundTask(MAP_API_URL, "GET", apiParams);

  try {
   JSONObject countryJSON = bgt.execute().get();
   // Getting Array of countries
   JSONArray countries = countryJSON.getJSONArray(TAG_DATA);

   // looping through All countries
   for (int i = 0; i < countries.length(); i++) {

    JSONObject c = countries.getJSONObject(i);

    // Storing each json item in variable
    String id = c.getString(TAG_ID_COUNTRY);
    String name = c.getString(TAG_NAME);
    String iso = c.getString(TAG_ISO);
    String currency = c.getString(TAG_CURRENCY);

    // add Country
    countryList.add(new Country(id, name.toUpperCase(), iso, currency));
   }

   // bind adapter to spinner
   countryField = (Spinner) findViewById(R.id.countryField);
   CountryAdapter cAdapter = new CountryAdapter(this, android.R.layout.simple_spinner_item, countryList);
   countryField.setAdapter(cAdapter);
   
   countryField.setOnItemSelectedListener(new OnItemSelectedListener(){

    @Override
    public void onItemSelected(AdapterView parent, View view, int position, long id) {
     Country selectedCountry = countryList.get(position);
     showToast(selectedCountry.getName() + " was selected!");
    }

    @Override
    public void onNothingSelected(AdapterView parent) {}
    
   });

  } catch (JSONException e) {
   e.printStackTrace();
  } catch (InterruptedException e) {
   e.printStackTrace();
  } catch (ExecutionException e) {
   e.printStackTrace();
  }
 }

 public void showToast(String msg) {
  Toast.makeText(this, "Toast: " + msg, Toast.LENGTH_LONG).show();
 }
}



Code Explanation

First we define some strings that represent the keys of our JSON data (TAG_DATA to TAG_CURRENCY), then we define our API end point MAP_API_URL.

In Android development ,it is recommended not to make http requests on the same thread that displays the user interface (i.e requests are best made asynchronous and not as a blocking operation). This is why I defined a background class called BackGroundTask.java (Code is at the end of this article), that handles all such request. Android provides a class called AsyncTask which enables asynchronous requests so my BackgroundTask class will just be extending this class and overriding some core functions. You can read more about this from the Android documentation.

Define the Spinner countryField as in the layout and finally we define the ArrayList that will hold the list of countries returned from the the API. Each entry in the ArrayList is of type Country (the definition of Country.java class is also given at the end of this article)

The onCreate is called when your activity is started and in it we set the content view and call our buildCountryDropdown() method.

What happens in the buildCountryDropDown() method?

1. Set the parameters to be sent to the API by defining a NameValuePair list.
List apiParams = new ArrayList(1); 
apiParams.add(new BasicNameValuePair("call", "countrylist"));


2. Start the background task
bgt = new BackGroundTask(MAP_API_URL, "GET", apiParams);

Initiating our background class, we pass as parameters the API end point, the method to use for the request and the NVP parameters. 3. Get response from the background task

JSONObject countryJSON = bgt.execute().get();

The try..catch block that follows is pretty straight forward. From the JSON object shown above, you will notice that the necessary data itself is in a JSON array, which is why to get the data, we use

JSONArray countries = countryJSON.getJSONArray(TAG_DATA);


Each item in the countries JSONArray is a JSONObject so to get the entries of each item in the countries Array, we use

JSONObject c = countries.getJSONObject(i); 


where i is the current position of the iteration. And finally to get the value of each entry you use the getString() method of the JSONObject class. For example to get the id of a country, you use

String id = c.getString(TAG_ID_COUNTRY);


At the end of the for loop after getting the values of the entries in a country, you add the country to the countryList ArrayList defined in the beginning.

countryList.add(new Country(id, name, iso, currency));

(The code defining the country class is given at the end of this topic.)

The last and final thing that needs to be done to complete our demo is to bind the country list to the spinner. This is done using an ArrayAdapter and because we have a custom type Country we define our own custom adapter called CountryAdapter, that extends the ArrayAdapter class. If the list we wanted to bind to the spinner was a list of Strings, we need not define a custom adapter. we counld directly use the ArrayAdapter.


//get country spinner view from the layout
countryField = (Spinner) findViewById(R.id.countryField);
//define adapter to be used when displaying the country list
CountryAdapter cAdapter = new CountryAdapter(this, android.R.layout.simple_spinner_item, countryList);
//bind the adapter to the spinner
countryField.setAdapter(cAdapter);

  
//set a listener for selected items in the spinner  
countryField.setOnItemSelectedListener(new OnItemSelectedListener(){

 @Override
 public void onItemSelected(AdapterView parent, View view, int position, long id) {
Country selectedCountry = countryList.get(position); showToast(selectedCountry.getName() + " was selected!"); } });


And that will be it. For detailed explanations on Android Classes used in this tutorial, you can visit the Android Developers site.

Other Classes Necessary for this tutorial:

Country.java
package package com.canda.android.spinnerdemo;

public class Country {
    private String id;
    private String name;
    private String iso2;
    private String currency;
    
    public Country(String i, String n, String iso, String curr)
    {
        id = i;
        name = n;
        iso2 = iso;
        currency = curr;
    }
    
    public String getId()
    {
        return id;
    }
    
    public String getName()
    {
        return name;
    }
    
    public String getISO2()
    {
        return iso2;
    }
    
    public String getCurency()
    {
        return currency;
    }
    
    public String toString()
    {
        return name;
    }
}



CountryAdapter.java

package com.canda.android.spinnerdemo;

import java.util.ArrayList;

import android.app.Activity;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ArrayAdapter;
import android.widget.TextView;

import com.zingersystems.apps.hellotaxi.R;
import com.zingersystems.apps.hellotaxi.models.Country;

public class CountryAdapter extends ArrayAdapter
{
    private Activity context;
    ArrayList data = null;

    public CountryAdapter(Activity context, int resource, ArrayList data)
    {
        super(context, resource, data);
        this.context = context;
        this.data = data;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) 
    {   // Ordinary view in Spinner, we use android.R.layout.simple_spinner_item
        return super.getView(position, convertView, parent);   
    }

    @Override
    public View getDropDownView(int position, View convertView, ViewGroup parent)
    {   // This view starts when we click the spinner.
        View row = convertView;
        if(row == null)
        {
            LayoutInflater inflater = context.getLayoutInflater();
            row = inflater.inflate(R.layout.simple_spinner_item, parent, false);
        }

        Country item = data.get(position);

        if(item != null)
        {   // Parse the data from each object and set it.
            TextView countryId   = (TextView) row.findViewById(R.id.item_id);
            TextView countryName = (TextView) row.findViewById(R.id.item_value);
            if(countryId != null)
            {
                countryId.setText(item.getId());
            }
            if(countryName != null){
             countryName.setText(item.getName());
            }

        }

        return row;
    }
}



BackGroundTask.java
package com.canda.android.spinnerdemo;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
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.os.AsyncTask;
import android.util.Log;

public class BackGroundTask extends AsyncTask<String, String, JSONObject> {
 
 List postparams = new ArrayList();
 String URL = null;
 String method = null;
 static InputStream is = null;
 static JSONObject jObj = null;
 static String json = "";

 public BackGroundTask(String url, String method, List params) {
  this.URL = url;
  this.postparams = params;
  this.method = method;
 }

 @Override
 protected JSONObject doInBackground(String... params) {
  // TODO Auto-generated method stub
  // Making HTTP request
  try {
   // Making HTTP request
   // check for request method

   if (method.equals("POST")) {
    // request method is POST
    // defaultHttpClient
    DefaultHttpClient httpClient = new DefaultHttpClient();
    HttpPost httpPost = new HttpPost(URL);
    httpPost.setEntity(new UrlEncodedFormEntity(postparams));

    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(postparams, "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, "utf-8"), 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;

 }
}



Ignore the closing tags at the end of the code snippets, the script formatting the code snippets just messes up.

lundi 5 novembre 2012

The Password Manager - PassMan

This might not be new but anyway, I had a problem and I created this application to solve my problem. I decided to put it up hoping it will help some other people. First and foremost it is free meaning you use at your own cost.

Problem
I have more than 25 accounts over the internet and each has a unique log-in and password. On my PC (mac & co. users already out of the equation :P) all the passwords are saved and I need not remember the user names and passwords. However when not on my pc and need to access a service online, I am required to log-in and most often I forget which log-in goes to which service. This is the problem I faced.

Solution
My solution to this was to have just one log-in detail which gives me access to all other log-in details - and hence PassMan. With PassMan you are able to securely save all your passwords in your mobile and retrieve them at anytime and on the fly. All you need is an internet connection on your mobile and a 'super' user that connects to PassMan.

When PassMan is first installed on your mobile, you are required to first create an account which is unique for each user. Two users can't have same account name and an account can't be duplicated even if you change your mobile phone. In case you loose your mobile phone or change your mobile phone, all you need to do is install PassMan on the new mobile and use same log-in details of the previous install.

This is the first version of this application and thus negative feedback is welcome. The servers responsible for the PassMan web service might be moved when the userbase becomes large. Users will however be informed if there will be any 'temporal no-service'. PassMan is currently avaliable ONLY for android users and versions of other platforms will be released only on demand. You can drop a short message at cyril.tata@hotmail.com.

Below is a simulation to give you an insight on how passman works. Start by clicking on "Create Phone Account". If you like it and you are on the android platform, get in touch for the apk file <cyril.tata@hotmail.com>. Because it is free, I did not put it on the market.

ENJOY!

samedi 8 septembre 2012

Finger print matching with Locality Sensitive Hashing

Have you ever wondered how finger print is saved and matched against you at a later time?

For some very important official documents we dirty our fingers with black jelly and place it on a scanner and later with that finger print we can be fished out in any circumstance. In most crime scenes for example you see police and related trying to get hold of as many finger prints as they can. Of course we know they go search the finger prints against a huge data storage. But how does it work? I give you hint about one of the methods used in finger print matching

It is a technique called Locality Sensitive Hashing (LSH)(an Object Search technique). First of all how is a finger print structured that it can even be stored in the first place and how is it stored? Finger prints consist of what is called is minutiae, which I can't claim to really understand (it's a technical term in the finger print domain). Minutiae consists of elements like cross over, core, bifurcation, ridge ending, island, delta, pore,.. etc. You might want to refer to a biometrics book for more details. When you place your finger on a scan, a 'grid' object is created and the cells of this grid can contain minutiae or not.

The minutia of your finger print is stored in the cells of the grid. Many types of data structures can be used to store grid elements as 'objects'. As an example a cell can be marked 1 if it contains minutae or 0 if it doesn't. With this representation of your finger print in a grid whose cells contain your minutiae, matching a print simply means performing an object search on some huge data storage. Now how is this done with Locality Sensitive Hashing?

Let's 'define' (a real analysis student with eat me for this) a function f(x) = 1 if print x has minutiae match in some k cells of the grid. (print x can be a sample of your finger print)

Suppose p is the probability that a print x has minutiae at a particular position (cell), then

P(f(x) = 1) = pk

i.e the probability that the print x has minutiae in all k positions. For example if p = 0.3 and k = 4 then the probability that the print has minutiae in all 4 positions is 0.0081.

Note that the function f depends on the k cells you choose from the grid. If you choose other cells in the grid, you might have to change the function f.

Now suppose you left your print in a crime scene :P call it print y and the police took the print to the lab. Let q be the probability that print y will have minutiae in some k cells IF x does, then the probability that the function f gives a match for both prints is

P(f(x) = f(y) = 1) = (pq)^k.

If q = 0.9 (quite high) then (pq)k = 0.0053 (quite low) - might not be you :-D. You can't conclude a match at this point. The probability is quite low.

ut what if we took b of such functions (say b = 1024 - that's a lot of functions to define). Each function chosen correspond to particular k cells in the grid.

Now what is the chance that at least one of such functions out of the thousands actually produce a match in both prints? i.e what is the probability that a function will satisfy f(x) = f(y) almost surely for every x, y?

  • (pq)k is the probability that f(x) and f(y) are both 1 for some particular k cells.
  • 1 - (pq)k is the chance that f(x) and f(y) are not 1 for these particular k cells
  • (1 - (pq)k)b is the chance that none of the b functions selected actually match for each print.
  • 1 - (1 - (pq)k)b is the chance that at least one of the b functions matches which is 0.996 for p = 0.3, k = 4 and q = 0.9

So by using many of such functions, we get a high chance that at least one of them will give us a match IF the prints are from the same person. (Think of how model designers come up with such functions and how many are actually implemented).

What if prints x and y DON’T belong to the same person? We have to figure out the probability that 2 prints, gotten at random, give a match with one of the b functions f. Prints can have a match at random if both of them have minutiae in same k positions. So what is the probability that this can happen?


  • the probability that both of them have minutiae in k positions at random is pk x pk = p2k (one for x and one for y)
  • the chance that they don't have minutiae in these k positions is (1 - p2k)
  • the chance that both prints don't match for any of the b functions is (1 - p2k)b
  • and finally the chance that at least one of the b functions gives a match is 1 - (1 - p2k)b, which is 0.063 with our values (not bad!)

So what we see is that if the 2 prints are from the same person, LSH procedure, maps them onto the same region with a very high probability but if they are NOT from the same person then the chance of a wrong match is only about 6%. We can reduce this probability by increasing b but so long as it is not 0% this method is still not PERFECT! and so is the world :P.

vendredi 22 juin 2012

Migrating from the Codeigniter database abstraction to the Zend database abstraction.

This post is meant to give a quick review and function reference to developers migrating from the codeigniter database abstraction to the Zend database abstraction. This is just a quick review for frequently used sql functions. If you want a detailed understanding of the Zend DB abstraction class you should not be here but here: http://framework.zend.com/manual/en/zend.db.html Zend_Db and its related classes provide a simple SQL database interface. Zend_Db_Adapter is the basic class you use to connect your PHP application to an RDBMS. I will use the pdo_mysql php extension so you will have to enable the pdo_mysql php extension in order to use the Zend_db_Adapter_Pdo_Mysql class from the Zend_db. Other RDBMS brands are however also supported by this framework. Most of the functions listed here too are just the equivalence of just the CI DB active record class

Connecting to a database using the Zend_db adapter

To create an instance of an adapter, use
<?php
$db = new Zend_Db_Adapter_Pdo_Mysql(array(
    'host'     => '127.0.0.1',
    'username' => 'webuser',
    'password' => 'xxxxxxxx',
    'dbname'   => 'test'
));
?>

you can also use the Zend_Db::factory() method in order to create an instance of an adapter. This method will load the adapter class on demand using the Zend Loader class (use this only if you are using the framework).

Managing Connections


<?php
try {
    $db = Zend_Db::factory('Pdo_Mysql', $parameters);
    $db->getConnection();
} catch (Zend_Db_Adapter_Exception $e) {
    // perhaps a failed login credential, or perhaps the RDBMS is not running
} catch (Zend_Exception $e) {
    // perhaps factory() failed to load the specified Adapter class
}
?>
Getting Rows

<?php
$sql = 'SELECT * FROM bugs WHERE bug_id = ?';
$result = $db->fetchAll($sql, 2);
// second argument is an array (or val)substituting placeholders in the sql statement
?>

You can change the way to fetch data by using the setFetchMode() method. Example
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchAll('SELECT * FROM bugs WHERE bug_id = ?', 2);
// $result is an array of objects | Zend_Db::FETCH_ASSOC, Zend_Db::FETCH_NUM, Zend_Db::FETCH_BOTH, Zend_Db::FETCH_COLUMN, Zend_Db::FETCH_OBJ
echo $result[0]->bug_description;
?>
$db->fetchCol() returns only the first column in the query and is returned as values. fetchRow() can be used to get the first row of a result set
<?php
$db->setFetchMode(Zend_Db::FETCH_OBJ);
$result = $db->fetchRow('SELECT * FROM bugs WHERE bug_id = 2');
// note that $result is a single object, not an array of objects
echo $result->bug_description;
?>
fetchOne() returns an intersection of first row and first column
<?php
$result = $db->fetchOne('SELECT bug_status FROM bugs WHERE bug_id = 2');
// this is a single string value
echo $result;
?>
inserting data

<?php
// $data is an associative array of key=>value pairs with keys being the column names and value being the data to be inserted under each column associative
$db->insert('bugs', $data);
$id = $db->lastInsertId();

?>
updating data

<?php
$db->update('bugs', $data, 'user_id = 5'[$where]);

$where['reported_by = ?'] = 'goofy';
$where['bug_status = ?']  = 'OPEN';
-------------- OR -----------------
$where[] = "reported_by = 'goofy'";
$where[] = "bug_status = 'OPEN'";

?>

deleting data

<?php
$n = $db->delete('bugs', 'bug_id = 3');
?>

The quote() method excapes string and returns a deliminited sql string
<?php $db->quote("O'Reilly"); //returns 'O\'Reilly'?>

more example
<?php
$value = '1234';
$sql = 'SELECT * FROM atable WHERE intColumn = '
     . $db->quote($value, 'INTEGER');
  
$sql = $db->quoteInto("SELECT * FROM bugs WHERE bug_id = ?", '1234', 'INTEGER');
?>
Transactions

Straight to an example:
<?php
// Start a transaction explicitly.
$db->beginTransaction();
 
try {
    // Attempt to execute one or more queries:
    $db->query(...);
    $db->query(...);
    $db->query(...);
 
    // If all succeed, commit the transaction and all changes
    // are committed at once.
    $db->commit();
 
} catch (Exception $e) {
    // If any of the queries failed and threw an exception,
    // we want to roll back the whole transaction, reversing
    // changes made in the transaction, even those that succeeded.
    // Thus all changes are committed together, or none are.
    $db->rollBack();
    echo $e->getMessage();
}
?>
Methods in the Zend_Db_Statement class

use $db->query to get all results at once or you can create a statement using the statement class and execute it
<?php
$sql = 'SELECT * FROM bugs WHERE reported_by = ? AND bug_status = ?';
$stmt = new Zend_Db_Statement_Mysqli($db, $sql);
$stmt->execute(array('goofy', 'FIXED'));
?>
user the fetch method to get results in
<?php
while ($row = $stmt->fetch()) {
    echo $row['bug_description'];
}
//OR $result->fetchObject();
?>
SELECT statement

The select statement object can be used to build good select queries. To create a select statemnt you can do one of the following
<?php
$db = Zend_db::Factory(...options...);
$select = new Zend_Db_Select($db);
  OR
$db->select();
?>
Since it always returns an instance of itself, you can bind its methods in a fluent interface
<?php
$select = $db->select()
    ->from( ...specify table and columns... )
    ->where( ...specify search criteria... )
    ->order( ...specify sorting criteria... );
?>
Example using 'from'
In the second argument of the from() method, you can specify the columns to select from the respective table. If you specify no columns, the default is "*", the SQL wildcard for "all columns". You can list the columns in a simple array of strings, or as an associative mapping of column alias to column name. If you only have one column to query, and you don't need to specify a column alias, you can list it as a plain string instead of an array.
<?php
// Build this query:
//   SELECT p."product_id", (p.cost * 1.08) AS cost_plus_tax
//   FROM "products" AS p
 
$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id',
                          'cost_plus_tax' => '(p.cost * 1.08)')
                   );
       
// Build this query with an alias for one column:
//   SELECT p."product_id" AS prodno, p."product_name"
//   FROM "products" AS p
 
$select = $db->select()
             ->from(array('p' => 'products'),
                    array('prodno' => 'product_id', 'product_name'));
?>
Example using 'join'
<?php
// Build this query:
//   SELECT p."product_id", p."product_name", l.*
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
 
$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id');
     
?>

The several sql join types can be specified as follows
INNER JOIN with the join(table, join, [columns]) or joinInner(table, join, [columns]) //most common join type LEFT JOIN with the joinLeft(table, condition, [columns])
RIGHT JOIN with the joinRight(table, condition, [columns])
FULL JOIN with the joinFull(table, condition, [columns])
CROSS JOIN with the joinCross(table, [columns])
NATURAL JOIN with the joinNatural(table, [columns])
Examples using 'where'
<?php
// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price > 100.00)
//     AND (price < 500.00)
 
$minimumPrice = 100;
$maximumPrice = 500;
 
$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('price > ?', $minimumPrice)
             ->where('price < ?', $maximumPrice);
?>

There is also orwhere which is similar to or_where of the codeigniter active record class.
<?php
// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (product_id IN (1, 2, 3))
 
$productIds = array(1, 2, 3);
 
$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('product_id IN (?)', $productIds);
    ->orwhere('.....');
?>
you can also add
 
<?php
$select->group() // for group_by() //GROUP BY CLAUSE
$select->having() // for having() //HAVING CLAUSE
$select->order(array('id DESC', 'name ASC') // for order_by('field', ORDER) //ORDER BY CLAUSE
$selcet->limit(20, 10);   //The first argument to this method is the desired count of rows. The second argument is the number of rows to skip                     
?>
Adding the DISTINCT keywords to queries

Example:
<?php
// Build this query:
//   SELECT DISTINCT p."product_name"
//   FROM "products" AS p
 
$select = $db->select()
             ->distinct()
             ->from(array('p' => 'products'), 'product_name');

?>
Building a union query

<?php $sql1 = $db->select(); $sql2 = "SELECT ..."; $select = $db->select() ->union(array($sql1, $sql2)) ->order("id"); ?> You can convert your select query to a string by using <?php $sql = $select->__toString(); echo $sql; ?> Executing Select Queries

<?php
Example:
$select = $db->select()
             ->from('products');
 
$stmt = $db->query($select);
$result = $stmt->fetchAll();

//OR  

$stmt = $db->query($select);
$result = $stmt->fetchAll();
?>


This is just a quick review for frequently used sql functions. If you want a detailed understanding of the Zend DB abstraction class you should visit http://framework.zend.com/manual/en/zend.db.html

vendredi 9 décembre 2011

Google Maps API2: How to find latitude and longitude (geocode) for any well formatted address.

Recently I was working on a project to develop a city guide that helps tourists live locally for any specified city in the world. One interesting feature was to propose to the user sites closer to his home based address or current location. The idea is to form a circle with a user specified radius, with center his current location or home based address. Getting the center of this circle involves getting the latitude and longitude (geocode) of the current location. The following javascript function uses the GClientGeocoder() of Google Maps API to find out accurate geographical coordinates (latitude and longitude) for any place on Earth:

1:  function getLatLng(address) {  
2:       //alert('getting the center of the circle');  
3:       if (GBrowserIsCompatible()) {  
4:             var geocoder = new GClientGeocoder();  
5:               
6:             geocoder.getLatLng(address, function(latlng) {  
7:              if (!latlng) {  
8:                  alert('"' + address + '" not found');  
9:                  return false;  
10:              } else {  
11:                  return {'Lat':latlng.lat().toFixed(6), 'Lng':latlng.lng().toFixed(6)};               
13:              }  
14:             });  
15:       }  
16:   }  

This works only for compartible browsers. You can check browser compatibility using GBrowserIsCompatible() and of course you need to include the API using
1:  <script src="http://maps.google.com/maps?file=api&v=2&key=abcdef" type="text/javascript"></script>