1. Create the data source

Let's imagine, for example, a company website that displays the following data to partners who log in into the site:
dateproductamountcountry
1/12/2007chair200US
1/12/2007desk220CA
1/12/2007chair150CA
1/12/2007desk80US
3/15/2007chair180CA
3/15/2007desk230US
3/15/2007chair210CA
3/15/2007desk95US
1/28/2009chair120US

The web master wants to present the information in more attractive way, and allow the partners to display this information in their websites.

The first step is to convert the data into a Google visualization compliant data source. For this, I used GV Streamer PHP API, to generate a PHP page that will create a response from the data. This can be accomplished in one of two ways:

First way - use the basic API for creating columns and rows and generating the response:
<?php
    
    
//the response should be utf8 encoded
    
header('Content-Type: text/html; charset=utf-8');
    
    
//Include the basic API
    
include_once("gvServerAPI.php");
    
    
    
//------------------------------------------
    
    //-- Add here business logic, if needed
    //-- For example users authentication and access control 
    
    //------------------------------------------
    
    //Initialize the gvStreamer objecy
    
$gvJsonObj = new gvStreamer();
    
$gvJsonObj->init();
    
    
//Add columns
    
$gvJsonObj->addColumn("0","year","string");
    
$gvJsonObj->addColumn("1","month","string");
    
$gvJsonObj->addColumn("2","product","string");
    
$gvJsonObj->addColumn("3","amount","number""#0.0########");
    
$gvJsonObj->addColumn("4","country","string");
    
    
//Add row 1
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("1");
    
$gvJsonObj->addStringCellToRow("chair");
    
$gvJsonObj->addNumberCellToRow(200"400.0");
    
$gvJsonObj->addStringCellToRow("US");
    
    
//Add row 2
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("1");
    
$gvJsonObj->addStringCellToRow("desk");
    
$gvJsonObj->addNumberCellToRow(220"220.0");
    
$gvJsonObj->addStringCellToRow("CA");
    
    
//Add row 3
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("1");
    
$gvJsonObj->addStringCellToRow("chair");
    
$gvJsonObj->addNumberCellToRow(150"150.0");
    
$gvJsonObj->addStringCellToRow("CA");
    
    
//Add row 4
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("1");
    
$gvJsonObj->addStringCellToRow("desk");
    
$gvJsonObj->addNumberCellToRow(80"80.0");
    
$gvJsonObj->addStringCellToRow("US");
    
    
//Add row 5
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("3");
    
$gvJsonObj->addStringCellToRow("chair");
    
$gvJsonObj->addNumberCellToRow(180"180.0");
    
$gvJsonObj->addStringCellToRow("CA");
    
    
//Add row 6
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("3");
    
$gvJsonObj->addStringCellToRow("desk");
    
$gvJsonObj->addNumberCellToRow(230"230.0");
    
$gvJsonObj->addStringCellToRow("US");
    
    
//Add row 7
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("3");
    
$gvJsonObj->addStringCellToRow("chair");
    
$gvJsonObj->addNumberCellToRow(210"210.0");
    
$gvJsonObj->addStringCellToRow("CA");
    
    
//Add row 8
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2007");
    
$gvJsonObj->addStringCellToRow("3");
    
$gvJsonObj->addStringCellToRow("desk");
    
$gvJsonObj->addNumberCellToRow(95"95.0");
    
$gvJsonObj->addStringCellToRow("US");
    
    
//Add row 9
    
$gvJsonObj->addNewRow();
    
$gvJsonObj->addStringCellToRow("2009");
    
$gvJsonObj->addStringCellToRow("1");
    
$gvJsonObj->addStringCellToRow("chair");
    
$gvJsonObj->addNumberCellToRow(120"120.0");
    
$gvJsonObj->addStringCellToRow("US");
    
    echo 
$gvJsonObj;
    
    
?>
Note: For simplicity sake, the data in the example above was hardcoded.

Second way - Use the extended API for generating the response automatically from the MySql query result, and enjoying the protocol's advanced features:
<?php
    
    
//The response should be utf8 encoded
    
header('Content-Type: text/html; charset=utf-8');
    
    
//Include the extended API
    
include_once("gvServerAPIEx.php");
    
    
//------------------------------------------
    
    //-- Add here business logic, if needed
    //-- For example users authentication and access control 
    
    //------------------------------------------
    
    // 2 parameters of the protocol are supported: tqx and responseHandler. 
    // You should pass them as-is to the gvStreamerEx object
    
$tqx $_GET['tqx'];
    
$resHandler $_GET['responseHandler'];
    
    
// Read the data from MySql
    
$host  $_SERVER['HTTP_HOST'];
    
$con mysql_connect($host,"user","password");
    
mysql_select_db("DB_Name"$con);
    
$sql "SELECT YEAR(date) as year, FORMAT(MONTH(date),0) as month,product,amount,country FROM sales";
    
$result mysql_query($sql);
    
    
// Initialize the gvStreamerEx object
    
$gvJsonObj = new gvStreamerEx();
    
    
// If there will be an error during the inialization
    // gvStreamerEx object will generate an error message
    
if($gvJsonObj->init($tqx$resHandler) == true);
    {
        
//convert the entire query result into the compliant response
        
$gvJsonObj->convertMysqlRes(&$result"%01.1f""m/d/Y""G:i:s");
        
$gvJsonObj->setColumnPattern(3,"#0.0########");
    }
    
    
// Close the connection to DB
    
mysql_close($con);
    
    echo 
$gvJsonObj;
    
    
    
?>
See how the final data source looks like here.
Add just few parameters to the URL to download the data as CSV.

Another example - Generate the same response as above, but this time automatically convert PostgreSQL query:
<?php
    
    
//The response should be utf8 encoded
    
header('Content-Type: text/html; charset=utf-8');
    
    
//Include the extended API
    
include_once("gvServerAPIEx.php");
    
    
//------------------------------------------
    
    //-- Add here business logic, if needed
    //-- For example users authentication and access control 
    
    //------------------------------------------
    
    // 2 parameters of the protocol are supported: tqx and responseHandler. 
    // You should pass them as-is to the gvStreamerEx object
    
$tqx $_GET['tqx'];
    
$resHandler $_GET['responseHandler'];
    
    
// Read the data from PostgreSQL
    
$host "localhost";
    
$user "user";
    
$pass "password";
    
$db "postgres";
  
    
$connection pg_connect("host=$host dbname=$db user=$user password=$pass");
    if (!
$connection)
    {    
        die(
"Could not open connection to database server");
    }
  
    
$sql "SELECT  EXTRACT(YEAR from date) as year, to_char(EXTRACT(MONTH from date),'FM99') as month,product,amount,country FROM sales";
    
$result pg_query($connection$sql);
    
    
// Initialize the gvStreamerEx object
    
$gvJsonObj = new gvStreamerEx();
    
    
// If there will be an error during the inialization
    // gvStreamerEx object will generate an error message
    
if($gvJsonObj->init($tqx$resHandler) == true);
    {
        
//convert the entire query result into the compliant response
        
$gvJsonObj->convertPGRes(&$result"%01.1f""m/d/Y""G:i:s");
        
$gvJsonObj->setColumnPattern(3,"#0.0########");
    }
    
    
// Close the connection to DB
    
pg_close($connection);
    
    echo 
$gvJsonObj;
    
    
    
?>

2. Add visualizations that read the data source

Now that we have the data source available, we can start applying visualizations on it, thus adding value to our website and our partners.
Here I have chosen to embed the Panorama's pivoting gadget in the webpage . Play with it and see how cool it is:


3. Publish your data

By now, anyone can add visualizations to their website and put the data source URL that we created as the visualization's source. I can make the life of my partenrs and visitors easier by offering them the ability to embed the gadget that I am using into their website, simply by copying and pasteing the following code:
<script src="http://www.gmodules.com/ig/ifr?url=http://hosting.gmodules.com/ig/gadgets/file/107522617710775425917/Panorama-Analytics.xml&amp;up__table_query_url=http%3A%2F%2Fwww.gvstreamer.com%2FsalesDataSourceEx.php&amp;up__table_query_refresh_interval=300&amp;up_GudgetName=First&amp;up_GudgetTitle=First&amp;up_FirstTime=First&amp;up_NVServer=pivot.panorama.com&amp;up_AutoRefresh=false&amp;up_IncludeChart=true&amp;up_CreationMode=1&amp;synd=open&amp;w=420&amp;h=350&amp;title=GV+Streamer+demo+-+sales&amp;border=%23ffffff%7C0px%2C1px+solid+%23ff9977%7C0px%2C1px+solid+%23ffddcc%7C0px%2C1px+solid+%23ff9977%7C0px%2C1px+solid+%23ffddcc%7C0px%2C1px+solid+%23ff9977&amp;output=js"></script>

How did I created this? This code is generated automatically by Google according to the gadget you wish to embed in your website. If you go to the Gadgets index you can see all gadgets that can be added to a webpage. In our case, I searched for 'panorama' and got to here . In the 'Data source URL' box I entered the URL of the PHP file I had created before. Clicking 'Get the Code', generates the code above.

4. Support personalization

Another great thing feature is being able to offer your visitors the chance to view your information in their iGoogle dashboard by instructing them to 1. Click this button Add to Google in order to add the gadget to iGoogle and 2. Type the data source URL in the gadget's 'Data source URL' edit box after it has been added.

Another Example

Using the GVStreamer extended API, I created from the same database another Google visualization data source. This time I used the Google geomap visualization in order to view the data: