Multiple Database Connections

[rjddev]

So recently i've been programming a multiple database connection file which lets you connect to 2 databases (or more, if you add new arrays for the db).

Although its alot of code writing.. Is there a easier way to do this?

The code looks like this:

<?php
error_reporting(E_ALL);
// Database info through array
$config = array(
 "database1"    => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 ),
 "database2"   => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 )
);

// Connect
$connectDB1 = mysql_connect($config['database1']['host'], $config['database1']['user'], $config['database1']['pass']) or die(mysql_error());
$connectDB2 = mysql_connect($config['database2']['host'], $config['database2']['user'], $config['database2']['pass']) or die(mysql_error());

// Select Databases
$selectDB1 = mysql_select_db($config['database1']['data'], $connectDB1) or die(mysql_error());
$selectDB2 = mysql_select_db($config['database2']['data'], $connectDB2) or die(mysql_error());

// Example to run queries
$queryDB1 = mysql_query("SELECT * FROM `yourtable`", $selectDB1) or die(mysql_error());
$queryDB2 = mysql_query("SELECT * FROM `yourtable`", $selectDB2) or die(mysql_error());
?>

 

The reason I have used this into an array, is that I like to have all website config into one single file, such as stylesheets, javascripts like this:

<?php
error_reporting(E_ALL);
// Database info through array
$config = array(
 "database1"    => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 ),
 "database2"   => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 ),
 "stylesheets" => array(
     "website" => 'websitestyle.css'
 ),
 "javascripts" => array(
     "jquery"  => "http://code.jquery.com/jquery.min.js",
     "website" => "website.js"
 )
);

// Connect
$connectDB1 = mysql_connect($config['database1']['host'], $config['database1']['user'], $config['database1']['pass']) or die(mysql_error());
$connectDB2 = mysql_connect($config['database2']['host'], $config['database2']['user'], $config['database2']['pass']) or die(mysql_error());

// Select Databases
$selectDB1 = mysql_select_db($config['database1']['data'], $connectDB1) or die(mysql_error());
$selectDB2 = mysql_select_db($config['database2']['data'], $connectDB2) or die(mysql_error());

// Example to run queries
$queryDB1 = mysql_query("SELECT * FROM yourtable", $selectDB1) or die(mysql_error());
$queryDB2 = mysql_query("SELECT * FROM yourtable", $selectDB2) or die(mysql_error());
?>

 

So you will be able to do this when loading a stylesheet / javascript file

<link rel="stylesheet" href="<?php echo $config['stylesheets']['website']; ?>" type="text/css" />
<script src="<?php echo $config['javascripts']['jquery']; ?>" type="text/javascript"></script>
<script src="<?php echo $config['javascripts']['website']; ?>" type="text/javascript"></script>

 

and so on .. :)

If you need it, feel free to use it as well ;)

Edited April 12, 2013 by rjddev
Added a few more codes

[sniko]

Yep, here's something to connect to multiple databases, quickly (your first issue)

<?php
error_reporting(E_ALL);
// Database info through array
$config = array(
$config = array(
 "database1"    => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 ),
 "database2"   => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 )
);

$db = array();
foreach($config as $database => $values) {
   $connection = mysql_connect($config[$database]['host'], 
                                  $config[$database]['user'], 
                                  $config[$database]['pass']);
   $db[$database] = mysql_select_db($config[$database]['data'], $connection) or die('Connection to '. $config[$database] .' failed. '. mysql_error());
}
?>

 

Now, to address the second issue;

<?php
error_reporting(E_ALL);
// Database info through array
$config = array(
 "database"    => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 ),
 "database"   => array(
     "host"    => "localhost",
     "user"    => "root",
     "pass"    => "usbw",
     "data"    => "database2"
 ),
 "stylesheet" => array(
     "website" => "websitestyle.css",
     "path" => "path/to/file.css",
     "name" => "homestyle"
 ),
 "javascript" => array(
     "jquery"  => "http://code.jquery.com/jquery.min.js",
     "website" => "website.js",
     "path" => "path/to/file.js",
     "name" => "jquery"
 )
);


$db = array();
$css = array();
foreach($config as $type => $values) {
switch($type) { 
   case 'database' : $connection = mysql_connect($config['database']['host'], 
                                   $config['database']['user'], 
                                   $config['database']['pass']);
    			$db['database']['data'] = mysql_select_db($config['database']['data'], $connection) or die('Connection to '. $config['database'] .' failed. '. mysql_error());
    break;
   case 'stylesheet' : $css['stylesheet']['name'] = $config['stylesheet']['path'];
    break;
   case 'javascript' : $js['javascript']['name'] = $config['javascript']['path'];
}
}

//Test it all works, perform a dump
var_dump($db);
var_dump($css);
var_dump($js);
?>

 

Also, I'd use mysqli.

Usage (Not tested)

//Queries
$db['database']['database2'] = mysql_query();

//Loop through the $css and $js arrays
foreach($css as $style) {
  echo '<link rel="stylesheet" src="''. $style['stylesheet']['path'] .'" type="css/text" />';
}

foreach($js as $javascript => $value) {
 echo '<script type="javascript/text" src="'. $javascript['javascript']['path'] .'"></script>';
}

Edited April 12, 2013 by sniko

[Spudinski]

Why on earth would you want to launch multiple connections, it makes no sense?

You can easily select across databases if needed, even though inefficient.

 

SELECT
 COUNT(db1.tbl.id),
 COUNT(db2.tbl.id)
FROM 
 db1.tbl,
 db2.tbl

[sniko]

Why on earth would you want to launch multiple connections, it makes no sense?

You can easily select across databases if needed, even though inefficient.

 

SELECT
 COUNT(db1.tbl.id),
 COUNT(db2.tbl.id)
FROM 
 db1.tbl,
 db2.tbl

Perhaps, one for his game, one for a forum?

[Spudinski]

1 code set, 2 worlds, 2 seperate sets of data that are independent of the other without the need to add IF clauses to code, should the 2 worlds have separate data?

It depends. Decoupling is useful at times, but I really don't see how anything can be in two places at once.

I personally think that this particular question from the OP was not well thought through - probably due to the OP not knowing any better.

[Octarine]

It never fails to amaze me the ability of certain members here to completely fail to understand what is at heart a very basic and extremely powerful concept.

For years, we have been have been using multiple connections - application (game) servers each utilize at least two connections to the database cluster which provides read-write splitting, improving ensuring that for example, SELECT statements are handled by read-only database nodes, freeing that load from the primary database.

With a very simple replication setup of two database servers; A (master), B (read-only slave); an application could utilize dual connections; $connA for INSERT/DELETE/UPDATE etc, $connB for SELECT etc. Proxies can be positioned easily between the application server(s) and the database cluster extending this concept further and reducing the read load considerably, which can be a stumbling point once games reach a certain size. With careful planning, you can split write load as well.

There are other solutions which work on a single connection, but the experience gained from multiple connections is excellent and will hopefully warrant the time spent in developing the ideas.

@guest: I swear I've seen that code somewhere else

[Spudinski]

It never fails to amaze me the ability of certain members here to completely fail to understand what is at heart a very basic and extremely powerful concept.

For years, we have been have been using multiple connections - application (game) servers each utilize at least two connections to the database cluster which provides read-write splitting, improving ensuring that for example, SELECT statements are handled by read-only database nodes, freeing that load from the primary database.

With a very simple replication setup of two database servers; A (master), B (read-only slave); an application could utilize dual connections; $connA for INSERT/DELETE/UPDATE etc, $connB for SELECT etc. Proxies can be positioned easily between the application server(s) and the database cluster extending this concept further and reducing the read load considerably, which can be a stumbling point once games reach a certain size. With careful planning, you can split write load as well.

There are other solutions which work on a single connection, but the experience gained from multiple connections is excellent and will hopefully warrant the time spent in developing the ideas.

@guest: I swear I've seen that code somewhere else

You seem to over-estimate the experience and knowledge of this community in general.

[Spudinski]

But by reading the reply, they should not understand something new, right?

You've misinterpreted me. I'm not saying they shouldn't learn something new, but rather that database replication and clustering is beyond the scope of this post.

But if you'd like, I'd go into further detail... I just don't see the point to.