Connecting several MySQL databases with 1 shared user

Question

I am trying to integrate a wiki, forum, chat and user database all on 1 site. To connect to the database we use:

<?php
$host="localhost.sitename.com";
$user="user_name";
$password="password";
$database="site_database";
$connection = mysql_connect($host,$user,$password)
or Die ("Could not connect to Server.");
$db=mysql_select_db($database,$connection)
or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("site_database"); 
?>

I need it to also connect to the 3 other databases and am wondering if I need to list each one with a separate $database and again mysql_select_db line or all in one with a , in between?

$database="site_database";
$database="chat_database";
$database="wiki_database";
$database="forum_database";

and

mysql_select_db ("site_database");
mysql_select_db ("chat_database");
mysql_select_db ("wiki_database");
mysql_select_db ("forum_database");

OR

$database="site_database","chat_database","wiki_database","forum_database";

and

mysql_select_db ("site_database","chat_database","wiki_database","forum_database");

???

score 2 · Answer 1 · answered Dec 31 '13 at 18:09

You can only have one database selected per connection. If you want to have 4 databases open and selected at the same time, even if your using the same login credentials you will need to have 4 open connections. Otherwise you will need to select the appropriate database before each MySQL query if the last MySQL query on that connection had a different database selected.

score 0 · Answer 2 · answered Dec 31 '13 at 18:08

First: You should use mysqli Second: Try like this:

$Con1 = new mysqli ("host","user","password","database");
$Con2 = new mysqli ("host","user","password","database");
$Con3 = new mysqli ("host","user","password","database");

Then :

$PreparedStatement1 = $Con1->prepare(); 
$PreparedStatement1->bindparam('',);
$PreparedStatement1->execute();
$PreparedStatemet1->close(); 
$PreparedStatement2 = $Con2->prepare();
$PreparedStatement2->bindparam('',);
$PreparedStatement2->execute();
$PreparedStatemet2->close();

score 0 · Answer 3 · answered Dec 31 '13 at 18:16

0

What you can do is create a function which connects you to the database

function connect($host, $username, $password, $database) {
     $db = mysql_connect($host, $username, $password);
     mysql_select_db($database, $db);
     return $db;
}

This is theorically what you want to do, but here are some tips:

Avoid using mysql extension (you SHOULD use mysqli or PDO)
Try not to switch between databases with a connection for further database compatibility (although MySQL supports database switching, not every DBMS will behave the same)

Also, if you had to perform a couple of operation you could do:

mysql_select_db('first', $db);
// Perform some queries
mysql_select_db('second', $db);
// Perform some other queries
mysql_select_db('first', $db);

// And then switch back to the first database

answered Dec 31 '13 at 18:16

Kei

771
6
17

MUST is simply not true, not all servers have PDO and/or mysqli extensions available. The extension you use depends 100% on the platform's capabilities. Not everyone has a VPS or Dedicated server available, many are using free hosting and are stuck with what's available. – Ralph Ritoch Dec 31 '13 at 18:18
Mistyped with SHOULD. Thanks. – Kei Dec 31 '13 at 18:19
In my opinion new developers should be using an abstraction layer such as the Zend library to be in the habit of making cross platform applications, but unfortunately many good apps are still platform dependent when they don't need to be. – Ralph Ritoch Dec 31 '13 at 18:27
You can write your own way out of troubles or use someone else's code. In my opinion the first one should be used always, where it can not, use the second one. Zend is indeed powerful but it requires knowledge and the resulting application might become heavy. – Kei Dec 31 '13 at 18:30
I rarely use the entire Zend platform and just use the Zend library's (lib/...) see http://stackoverflow.com/questions/3164766/how-to-use-zend-library-without-installation-of-zend-framework . It does require some knowledge of OOP though. I somewhat agree that making your own is a good solution, but only if your ready to maintain your code. The advantage of Zend is that someone else will maintain it and keep it cross-platform compatible. – Ralph Ritoch Dec 31 '13 at 18:33

Connecting several MySQL databases with 1 shared user

3 Answers3