Getting Flexmonster Pivot Table Connected to Traditional Databases

Volodymyr Yatsevsky posted on December 21st, 2011

FlexMonster Pivot Table Component can connect traditional databases like MS SQL, MySQL, Oracle, PostgreSQL and others very similarly as it can get connected to OLAP servers. The main underlying concept is using a simple script in PHP, C# or practically any platform that would connect database, load the data and pass it to Pivot Table as CSV.

Assuming that the component is already deployed at final location, to connect Flexmonster Pivot to database, you need to prepare a script that should

1. Let Pivot know where it has to take the data.
2. Load the table from database with script.
3. Convert it to CSV and simply return in response.

Let’s have a look at this simple PHP script. It connects to a MySQL database and returns a CSV that can be loaded in OLAP-like manner into Pivot.

// Replace with own credentials
mysql_connect(localhost,$username,$password);
mysql_select_db($database) or die( "Unable to select database");
$csv_output = "";
$table = "SampleTable";

function getCSVColumns() {
  global $table, $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";
}

function getCSVDataRows() {
  global $table, $csv_output;
  $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";
  }
}

getCSVColumns();
getCSVDataRows();

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

If you have an existing solution which Pivot has to be integrated, you can modify its routines to return CSV from existing configured DB sources.

You are welcome to modify the above sample or use it as a guideline to write a script for your particular platform.