Multiple Databases in an Environment

Let's say you have a main database and a separate statistics database that you want to run a report on. You also have a production and a dev environment, each with their own set of databases.

Configuration

You can set up your config/config.php file as follows:

<?php
return array(
//...
    'environments'=>array(
        'production'=>array(
            'mysql'=>array(
                'host'=>'http://example.com',
                'user'=>'root'
                'database'=>'mysite'
            ),
            'statistics'=>array(
                'host'=>'http://example.com',
                'user'=>'root',
                'database'=>'statistics'
            ),
        ),
        'dev'=>array(
            //...
        ),
    ),
//...
);
?>

By default, MySql reports will use the "mysql" database within an environment. If you want to use another database, like "statistics", you need to specify that with a DATABASE header in the report.

-- Main Site Report
-- The DATABASE header below is optional
-- DATABASE: mysql

SELECT * FROM mytable;

-- Statistic Report
-- The DATABASE header below is required since
-- it is different from the default
-- DATABASE: statistics

SELECT * from mystats;

Joining Across Multiple Databases

Let's say you want to create a report that joins between the main site and statistics databases.

You need to add another database to your environment that defines host and connection info, but doesn't provide a specific database to connect to. Here I'm using "nodb_mysql", but it could be anything.

<?php
return array(
//...
    'environments'=>array(
        'production'=>array(
            //...
            'nodb_mysql'=>array(
                'host'=>'http://example.com',
                'user'=>'root'
            ),
            //...
        ),
        //...
    ),
//...
);
?>

Here is a sample report:

-- Main Site and Statistics Report
-- DATABASE: nodb_mysql

SELECT 
   * 
FROM
   {{environment.mysql.database}}.mytable 
JOIN
   {{environment.statistics.database}}.mystats USING (id)

The "environment" variable is available to Twig and holds all of the databases in the currently selected environment. Using this instead of hard-coding database names will ensure that your reports are portable.