Integrating Pivot Table with PHP/MySQL

FlexMonster Pivot Table Component can be easily integrated with your PHP site.

We will guide you through simple PHP integration sample. Here you can find a guide describing a simple PHP integration example.

To be the Pivot Table Component integrated into your web page you should follow these steps:

1. You have to copy all Pivot component folder content with all supported files in there. Paste them into the root of your site or to a sandbox folder.
2. Next insert the Pivot component into .php file and past this code below:
   

   <div id="pivotContainer">The component will appear within this DIV.</div>
   <script type="text/javascript" src="flexmonster/flexmonster.js"></script>
   <script type="text/javascript">
       flexmonster.embedPivotComponent("flexmonster/", "pivotContainer","100%", "500");
   </script>

3. To select a valid path to your data file you need to add an attribute to a last embedPivotComponent function parameter:

   <div id="pivotContainer">The component will appear within this DIV.</div>
   <script type="text/javascript" src="flexmonster/flexmonster.js"></script>
   <script type="text/javascript">
       var params = {filename : "your_data_script.php"};
       flexmonster.embedPivotComponent("flexmonster/", "pivotContainer","100%", "500", params);
   </script>

How to Write a Data Source Script

CSV format is used for Pivot Table Component because it is the most compact one and can be easily convert to an appropriate data structure.

Now we can write a simple CSV generator.

An output of the CSV file and database data should be retrieved by your script.

• To do this you have to:
  
  • connect to needed database
  • retrieve (take out) set of columns
  • separate resulted dataset
  • name all received columns

  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";
  }
  

• To retrieve data from database you need to:
  
  • select data from database
  • parse result of a query
  • convert dataset to a CSV rows
  • write it 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";
    }
  }
  

• the last thing to do is to send a CSV response

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