Integrating Pivot Table with PHP/MySQL

FlexMonster Pivot Table Component can be easily interated with your PHP site

We will guide you through simple PHP integration sample.

To integrate Pivot Table into your page you should accomplish the following steps:

1. Copy content of Pivot folder (Pivot component and supported files) to root of your site or sandbox folder.
2. Insert Pivot component into your .php file – paste code below:
   

   <script type="text/javascript" src="swfobject.js"></script>
   <div id="flashContent">No Flash player or Javascript disabeled message</div>
   <script type="text/javascript">
     var so = new SWFObject("PivotTable.swf", "flash", "1000", "720", "9", "#FFFFFF");
       so.addParam("allowScriptAccess", "sameDomain");
       so.addParam("menu", "false");
       so.addParam("allowFullScreen", "true");
       so.addVariable("filename", "your_data_script.php");
       so.addVariable("styleSheetName", "original.css");
       so.addVariable("configuratorEnabled", "true");
       so.addVariable("chartsEnabled", "true");
       so.write("flashContent");
       flash.focus();
   </script>

3. Set path to your data file. To do this change value of filename FlashVar.

   so.addVariable("filename", "your_data_script.php");

4. Now we are ready to write data source script.

Wrinting data source script

The best data format for Pivot Table Component is CSV. It most compact and can be easily convert to desired data structure. So now we will write simple CSV generator.

Your script should retreive data from database and output CSV file.

• Firstly we have to get list of columns:
  
  • connect to database
  • retreive set of columns
  • split resulted dataset
  • write names of columns to csv

  function getColumns() {
    mysql_connect(localhost,$username,$password);
    mysql_select_db($database) or die( "Unable to select database");
    $csv_output = "";
    $sql = "SHOW COLUMNS FROM ".$table;
    $result = mysql_query($sql);
    while ($row = mysql_fetch_assoc($result)) {
      $csv_output .= $row["Field"].",";
    }
    $csv_output = substr($csv_output, 0, -1);
    $csv_output .= "\r\n";
  }
  

• Retreve data from database:
  
  • select data from database
  • parse result of query
  • convert dataset to csv rows
  • write to csv

  function getDataRows() {
    $query="SELECT * FROM ".$table;
    $result = mysql_query($query);
    while($row = mysql_fetch_row($result)){
      for ($i = 0; $i < count($row); $i++) {
        $csv_output .= $row[$i].",";
      }
      $csv_output = substr($csv_output, 0, -1);
      $csv_output .= "\r\n";
    }
  }
  

• Send csv response

  header('Content-type: text/plain');
  print $csv_output;

You can download full source code of this sample.