Loading data to your table from another database/server

Tabulizer offers you the possibility to display the results of a database query into a table, by creating a data source of type "Database Table/Query" where you have defined the query. This works well for queries that are executed on the same database as the Joomla site, but how about if you wanted to execute a query on a different database or even on a remove server? This is still feasible, but you need to follow a different approach. Tabulizer can also read data from a remote location, and this is exactly what you need to do:
  1. Create a file that will execute the query and display the results as CSV file
  2. Create a data source that will read the contents of this file. The data source will be of type "CSV/Text file" and the URL should point to the location of the file mentioned in the previous step. Of course, don't forget to make sure that the separator and the enclosure match.

Below is the skeleton code for the PHP file responsible to execute the database query and display the results as CSV file.

<?php

$host = 'localhost';
$user = 'username';
$password = 'password';
$db = 'database_name';

$fieldnames = array('field1','field2','field3');
$query = 'SELECT '.implode(',',$fieldnames).' FROM `table_name` WHERE 1';

define('COLUMN_SEPARATOR',',');
define('ENCLOSURE','"');

// DO NOT MODIFY ANYTHING BELOW THIS LINE

function outputCSV($data) {

    $outstream = fopen("php://output", 'w');

    function __outputCSV(&$vals, $key, $filehandler) {
        fputcsv($filehandler, $vals, COLUMN_SEPARATOR, ENCLOSURE);
    }
    array_walk($data, '__outputCSV', $outstream);

    fclose($outstream);
}

$link = mysql_connect($host, $user, $password);
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
}
$result = mysql_query($query);
if (!$result) {
    die('Could not query:' . mysql_error());
}
$values = array();
$num_of_rows=mysql_numrows($result);
for ($i=0;$i<$num_of_rows;$i++) { 
  $values[$i] = array();
  foreach ($fieldnames as $fieldname) {
    $values[$i][] = mysql_result($result, $i, $fieldname);
  } 
}

mysql_close($link);

if (!empty($values)) outputCSV($values);

?>

Example

Let's look at a specific example. We want to have in a Joomla article a table that will display the results of a database query executed on a remote web server (of course, it can be on the same server as well). We take the skeleton code above, we replace the highlighted values with the actual values and we save the file as select.php in the tmp folder under the public_html directory. Let's assume that the resulting URL for this file is:

http://www.othersite.com/tmp/select.php

The next step is to create a data source of type "CSV/Text file" that will read the output of this file.


Finally, we insert the data source into the article and we are ready to go. Every time the database query returns different results, the linked table will be updated as well.


If the query runs on a remote server it's always a good idea to enable data source caching in order to improve performance