Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Customising the CMS

Connection to multiple databases


Reply

5 Posts   2924 Views

Avatar
linkoovi

10 August 2010 at 3:31am Community Member, 12 Posts

Hi,

I have to connect to a "external" db, (with different params:username/host/pass ), that holds some data, it has nothing to do with SS. I managed to connect and exe a query
but I cant reconnect to the SS database back.

How do i do this in SS 2.4?

Avatar
patjnr

6 October 2010 at 10:58pm Community Member, 101 Posts

Hi

iam trying to do the same thing. how do you connect to a secondary db?

thx

PAt J

Avatar
swaiba

6 October 2010 at 11:18pm (Last edited: 7 October 2010 12:04am), Forum Moderator, 1796 Posts

how about the standard php way...

//note use of true in mysql_connect, this ensure that the connection ($C) is a new
//resource and not stomping all over the previous mysql_connect return value
$C = mysql_connect( $MYSQL_SERVER, $MYSQL_USER, $MYSQL_PASS ,true);
$cursor = mysql_query( $QUERY, $C );
while ($r = mysql_fetch_array($cursor))
{
   //do stuff here, e.g.
   $dataitem=$r['colname'];
}

Avatar
banal

7 October 2010 at 12:48am Community Member, 901 Posts

I did this recently and this is how:
In your _config.php you create two db connection settings, something along these lines:

global $databaseConfig;

$databaseConfig = array(
   'type' => 'MySQLDatabase',
   'server' => 'localhost',
   'username' => 'usernameA',
   'password' => 'passwordA',
   'database' => 'databaseA'
);

global $databaseConfig2;

$databaseConfig2 = array(
   'type' => 'MySQLDatabase',
   'server' => 'localhost',
   'username' => 'usernameB',
   'password' => 'passwordB',
   'database' => 'databaseB'
);

Then in a function where you need both DBs, you do something like this:

public function doStuff() {
   global $databaseConfig, $databaseConfig2;
   
   // connect to DB 2
   DB::connect($databaseConfig2);
   
   // create a query.. this will go to "databaseB"
   $query = new SQLQuery("*", "MyTable");

   $result = $query->execute();

   // do something with the result...
   
   // when done, switch back to the regular DB Config
   DB::connect($databaseConfig);
}

Avatar
patjnr

7 October 2010 at 10:30pm Community Member, 101 Posts

The solution that worked for me is from Banal as my situation involved connecting to 3 databases.
I created all my connection setting in _config.php. As said above, whenever i wanted to switch, I used

DB::connect($databaseConfig_WP);


and the most important part was to revert back to your default SS connection soon after.

thx All

PJ