Snippet: quickly switch between databases

In one of my recent projects I had to get some data from the external database, in this case it was a Sugar CRM database. So I had to write a little function, which I will share with you now.

First of all you need to define all external databases you will be using. Provide information such as database host, name etc. (array keys are the same as in /config/config.php). If the external database uses the same values as Contao’s one, you can leave them empty – they will be filled with the default values (localconfig.php).

Unfortunately, the charset needs to be set manually, because the setDatabase() function does not handle $GLOBALS['TL_CONFIG']['dbCharset'] (it is only used in connect() function).

Take a look at the example below. If you would like to connect the CRM database which is located on the other sql server, you must define it as shown. However, if the forum database is on the same server as Contao is, you just need to set values that differ from defaults. In this case host, user and port stay the same for both databases.

/**
 * External databases details
 * @var array
 */
protected $arrDatabases = array
(
    'crm' => array
    (
        'dbHost'     => 'sql.website.com',
        'dbUser'     => 'sugar_crm_user',
        'dbPass'     => '$3cr3tp4$$w0rd',
        'dbDatabase' => 'sugar_crm',
        'dbPort'     => '3306'
    ),
    'forum' => array
    (
        'dbPass'     => 'password',
        'dbDatabase' => 'phpbb'
    )
);

/**
 * Switch to external database
 * @param string
 */
public function changeDatabase($strDatabase = 'contao')
{
    if ($strDatabase == 'contao')
    {
        include(TL_ROOT . '/system/config/localconfig.php');
    }
    else
    {
        $GLOBALS['TL_CONFIG'] = array_merge($GLOBALS['TL_CONFIG'], array_filter($this->arrDatabases[$strDatabase]));
    }

    $this->Database->setDatabase($GLOBALS['TL_CONFIG']['dbDatabase']);
    $this->Database->execute("SET NAMES 'utf8'");
}

Please note that when you finish retrieving data from the external database, you should always switch back to Contao database!

Example usage:

$arrCategories = array();

// Change database to CRM
$this->changeDatabase('crm');

$objCategories = $this->Database->execute("SELECT document_category AS title FROM documents");

while ($objCategories->next())
{
    $arrCategories[] = $objCategories->title;
}

$arrCategories = array_unique($arrCategories);

// Change database back to Contao
$this->changeDatabase('contao');

$this->Template->title = 'My documents';
$this->Template->categories = $arrCategories;

About Author



Comments