CODEWITHSUNDEEP
phpmysqldatabase
Dexter
Dexter

Reputation: 9314

Connect Multiple Database with PHP and MySQL

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) ;
    }
?>

Upvotes: 1

Views: 11404

Answers (2)

Okechukwu Malcolm
Okechukwu Malcolm

Reputation: 37

$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.

Upvotes: 1

Aman Maurya
Aman Maurya

Reputation: 1325

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");

Upvotes: 7

Related Questions