1

We have this multiple database and we need to connect all of them together. We are using relationship table to talk back and forth. Multiple database will help us to backup the project or version separately.

Registration database: User Log In
Project database: Number of projects under the Logged in user.
Version database: Number of versions under the selected Project

THE ISSUE
Because we are using 0 and 1, only one user can able to connect to the database at a time. What we need is like the one in the flowchart below. Multiple user can able to connect to the database and working on a different project > different versions.

Thanks!

Sidenote: We are open to try different approach

enter image description here 

<?php
//database error message
 $connect_error='We could not able to connect. Please try later'; 

// Registration table
 $main = mysql_connect('localhost','root','',true) or die($connect_error);
 mysql_select_db('registration-table', $main) ;

// Obtaining session id
if(isset($_SESSION['id'])===true){  
    $session  = $_SESSION['id'];

    // Filtering projects based on 0 and 1
    $sql  = mysql_query("SELECT * FROM project1_table where user_id = '$session' and database_active = '0'",$main);
    $row  = mysql_fetch_array($sql);

    // Each Project has Versions
    $project_id = $row['id'];
    $sqli = mysql_query("SELECT * FROM version_table where project_id = '$project_id' and database_active ='0'",$main);
    $emp = mysql_fetch_array($sqli);

    // Fetching the database name
    $db_name = $emp['database_name'];
    $sub   = mysql_connect('localhost','root','',true) or die($connect_error);
    mysql_select_db("$db_name", $sub) ;
    }
?>
Dexter
  • 7,911
  • 4
  • 41
  • 40
  • since your databases are on same host, you can prefix table with a db? eg select * from database1.table1 ... – SIDU Aug 12 '16 at 05:46
  • We tried this but it did not work. – Dexter Aug 16 '16 at 04:03
  • For multiple DB query take a look at https://stackoverflow.com/questions/274892/how-do-you-connect-to-multiple-mysql-databases-on-a-single-webpage/45688265#45688265 – Nagibaba Aug 15 '17 at 07:45

2 Answers2

7

First of all I would suggest you to use mysqli or PDO other then mysql , Because after PHP 5.5 version mysql function deprecated and therefore mysql function will not be available in future

Multiple DB connection in Mysql

$dbh1 = mysql_connect($hostname, $username, $password); 
$dbh2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);

mysql_query('select * from tablename', $dbh1);
mysql_query('select * from tablename', $dbh2);

Multiple connection using Mysqli

$link1 = new mysqli($hostname, $username, $password,$database1);
$link2 = new mysqli($hostname, $username, $password,$database2);

mysqli_query($link1,"SELECT * FROM table");
mysqli_query($link2,"SELECT * FROM table");

Multiple DB connection in PDO

$conn1 = new PDO("mysql:host=$hostname;dbname=database1", $username, $password);
$conn2 = new PDO("mysql:host=$hostname;dbname=database1", $username, $password);

$conn1->query("SELECT * FROM table");
$conn1->query("SELECT * FROM table");
Aman Maurya
  • 1,305
  • 12
  • 26
  • Hi sorry for the late reply, we caught up in another work. We tried your answer already. We do not want to switch database (from one to two). What we want is to keep looking at the first one and holding the second one. – Dexter Aug 16 '16 at 04:00
1
$conn1 = mysqli_connect( $db_host, $db_user, $db_pass, $db_name );

$conn2 = mysqli_connect( $db_host2, $db_user2, $db_pass2, $db_name2 );

Let's say you have a database named chat1 and chat2, and a table named tbl_chat1 and tbl_chat2.

$sql1 = "SELECT `id`, `msgs` FROM `chat1` . `tbl_chat1`";
$sql2 = "SELECT `id`, `msgs` FROM `chat2` . `tbl_chat2`";

$result1 = mysqli_query($conn1, $sql1);
$result2 = mysqli_query($conn2, $sql2);

However you can achieve the backup of your database without having to connect to multiple databases OK.

GitHubRobot
  • 27
  • 4