(bool) cast.com - Using multiple databases within Joomla Framework

(bool) cast.com

Home

Joomla

Development

Using multiple databases within Joomla Framework

(9 votes, average 4.89 out of 5)

Written by Alex Balyuk

Friday, 27 August 2010 19:06

Joomla is a fantastic framework! To develop Joomla components one need to know the framework, need to know how to work the database how to work with Factory class in order to keep single instance of an object. Developing custom components might get complex and require developer to use 2 or more databases.·One might think that JFactory class is able to hold only 1 instance of a database object, at least I though so. JFactory can handle multiple database as the same time, you can have as many database connections as you want without having to reconnect to the one you want.

Joomla has an easy to use Factory class called JFactory, purpose of JFactory class is to keep single instance of an object. JFactory class is loaded·with the framework, which it done before anything else can be done. Once database is loaded via

JFactory::getDBO()

the pointer to the connection is stored in 2 different places

JFactory has a static reference variable for Joomla's native database object
JDatabase has a static array with references to all database instances. JFactory::getDBO() calls·JDatabase::getInstance(with default database params)

So in order to create different database connection all you have to do is to call JDatabase::getInstance(with different configs).

For example:

 $options = array();
 $options['driver'] = 'mysql';        // Database driver name
 $options['host'] = 'localhost';      // Database host name
 $options['user'] = 'username';       // User for database authentication
 $options['password'] = 'password';   // Password for database authentication
 $options['database'] = 'database';   // Database name
 $options['prefix'] = '';             // Database prefix
 //
 $db = & JDatabase::getInstance($options);

JDatabase::getInstance receives an array with database configurations. It uses that configuration array to generate a key for the array with database instances. So it is very important to keep the configurations the same, even switching the order of elements will create new database connection.

Rule of thumb in any programming language/script is to minimize repetition of code. The best way to create a helper class that will work like your factory. This way you will not need to specify options for database instance.

<?php

// No direct access
defined('_JEXEC') or die('Restricted access');

/**
 * MyComponentHelper is a component specific factory class that connects to
 * different databases
 *
 * @author Oleksandr Balyuk
 */

class MyComponentHelper {

    /**
     * Returns reference to firstst database connection
     * if connection does not exist will create it
     * 
     * @staticvar JDatabase $dbo1
     * @return JDatabase
     */
    function &getDBO1() {
        static $dbo1 = null;

        if (!$dbo1) {
            $option = array();
            $option['driver'] = 'mysql';        // Database driver name
            $option['host'] = 'localhost';      // Database host name
            $option['user'] = 'username';       // User for database authentication
            $option['password'] = 'password';   // Password for database authentication
            $option['database'] = 'database';   // Database name
            $option['prefix'] = '';             // Database prefix
            //
            $dbo1 = & JDatabase::getInstance($option);
        }

        return $dbo1;
    }

    /**
     * Gets reference to second database connection
     * if connection does not exist will create it
     *
     * @staticvar JDatabase $dbo2
     * @return JDatabase
     */
    function &getDBO2() {
        static $dbo2 = null;

        if (!$dbo2) {
            $option = array();
            $option['driver'] = 'mysql';        // Database driver name
            $option['host'] = 'localhost';      // Database host name
            $option['user'] = 'username2';      // User for database authentication
            $option['password'] = 'password2';  // Password for database authentication
            $option['database'] = 'database2';  // Database name
            $option['prefix'] = '';             // Database prefix
            //
            $dbo2 = & JDatabase::getInstance($option);
        }

        return $dbo2;
    }

}
?>

No just include your helper model/class where you need to connect to different database and call it like so

// Get first database connection
$db1 = & MyComponentHelper::getDBO1();
// Get second database connection
$db2 = & MyComponentHelper::getDBO2();
// Get Joomla default database connection
$db = & JFactory::getDBO();

You can download MyComponentHelper class from here. There are copies of the class, one for php 4 and the other for php 5.

MyComponentHelper Version:1.0

GNU/GPL v2 This e-mail address is being protected from spambots. You need JavaScript enabled to view it Joomla 1.5 2.64 KB

Last Updated on Tuesday, 07 September 2010 14:25

Comments

0 #1 shiva 2011-01-04 05:21

Hi,
I want to connect to external database and export a table and add to my table in internal database.
would you please tell me where can I add these code("my computerhelper") and in which file can I call my computerhelper and call query for inserting tale?
thanks

Quote

0 #2 Alex Balyuk 2011-01-04 05:35

Quoting shiva:

Hi,
I want to connect to external database and export a table and add to my table in internal database.
would you please tell me where can I add these code("my computerhelper") and in which file can I call my computerhelper and call query for inserting tale?
thanks

Thank you for your interest! You don't have to add import/export logic to the Helper class. The purpose of "ComponentHelper " is ensure only on connection to db (singleton).

You need to do the following (pseudocode):
1. Connect to external database
2. Run export query to create a temp file with tbl data - "SELECT * INTO OUTFILE '/tmp/export_tbl.sql' FROM table_name";
3. Connect to internal database
4. Run import query - "LOAD DATA INFILE '/tmp/export_tbl.sql' INTO TABLE table_name"

Quote

0 #3 Alex Balyuk 2011-01-04 05:39

However, for import/export I would use Command Line. You can use it from PHP with shel_exec or just run it from terminal.

www.boolcast.com/lamp/mysql/mysql-cli-ba[censored]uprestore

Quote

0 #4 shiva 2011-01-16 13:01

Hi
I have two databases in a same server.I want to connect to database2 and read the table of news and then insert it to another table in database1 in order to update the news of my site by reading from database2 every day.How can I do it? would you please help me?

Quote

0 #5 Alex Balyuk 2011-01-16 21:16

Quoting shiva:

Hi
I have two databases in a same server.I want to connect to database2 and read the table of news and then insert it to another table in database1 in order to update the news of my site by reading from database2 every day.How can I do it? would you please help me?

Here is psedocode:
1. Connect to db1
2. Load data from table in db1 into array $data.
E.g.
$db1->setQuery("SELECT * FROM news");
$data = $db1->loadObjectList( );

3. Connect to db2
4. Scan through array $data with foreach loop and create insert statement for each $row.
E.g.
$sql = "INSERT INTO news (field1, field2) VALUE ($row->field1, $row->field2)";
$db2->setQuery($sql);
$db2->query();

5. It's all there is to it.

This will help you: docs.joomla.org/How_to_use_the_database_classes_in_your_script

docs.joomla.org/How_to_use_the_database_classes_in_your_script

Quote

0 #6 Joomla Newbie 2011-03-04 21:41

Hello. I am a new joomla user and i am trying to make a contact directory. I already have a mysql database with the information i need for the directory. How do i display the data from the database in my website with front end edit privileges?

Quote

0 #7 Alex Balyuk 2011-03-06 15:55

Quoting Joomla Newbie:

Hello. I am a new joomla user and i am trying to make a contact directory. I already have a mysql database with the information i need for the directory. How do i display the data from the database in my website with front end edit privileges?

You will have to create views that will allow guests or users (depending on your application) to edit the info and save. Similar to how it is on the ba[censored]end .

Take a look at this tutorial docs.joomla.org/Developing_a_Model-View-Controller_Component_-_Part_1

docs.joomla.org/Developing_a_Model-View-Controller_Component_-_Part_1

, it is very informative.

Quote

+1 #8 Scott Oyer 2011-04-05 19:57

Need to connect to different databases based on login directory. Can this be easily done? We have custom logon pages for each "group" of users.
Thanks.

Quote

0 #9 Alex Balyuk 2011-04-06 04:08

Quoting Scott Oyer:

Need to connect to different databases based on login directory. Can this be easily done? We have custom logon pages for each "group" of users.
Thanks.

what do you mean login directory?

I understood it as simple conditional type problem.
Quoting Sample Code:

function getDBO($group)
{
if ($group == 'guest') return getGuestDBO();
else if ($group == 'registered') return getRegisteredDB O();
else if ($group == 'administrator') return getAdministrato rDBO();
else return getDefaultDBO() ;
}

Quote

0 #10 lukas 2011-09-18 21:34

HI
I'm quite new in Joomla. And I will be very grateful If you could tell me where in joomla framework insert MyComponentHelp erCode file to be accessible from any joomla component. Should I add files somewhere in library directory??

Quote

0 #11 Alex Balyuk 2011-09-27 02:51

Quoting lukas:

HI
I'm quite new in Joomla. And I will be very grateful If you could tell me where in joomla framework insert MyComponentHelp erCode file to be accessible from any joomla component. Should I add files somewhere in library directory??

sorry for late reply... Simply place files into "helper" directory in your component. Take a look at "\administrator\ components\com_ content\helper" as an example.
You will have to rename the file/class appropriately.

Quote

0 #12 Evandromar Machado 2011-09-28 00:58

Great post, but I have an intriguing question.
I have a db1.person.city_id db2.city.name and I need to do a search for the name of the city db2.city.name, how do I bring the table record as a city name?

Thanks all

Quote

Refresh comments list