CODEWITHSUNDEEP
phpmysql
Penwell
Penwell

Reputation: 63

Connect to two databases on the same host at the same time

How can i connect to two databases at the same time if both databases are on the same host and i have full privileges on both databases. So i have DB-1 and DB-2. And in this case i would like to have the following script working with the two databases. Im currently using require("db.php"); to connect to one database but i would like to connect to both databases.

require("DB-1.php");
$tbl_name="System_Info";
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql = "SELECT COUNT(a.student_id) 
    FROM DB-1.TableA a
    INNER JOIN DB-2.TableB b ON a.student_id = b.student_id 
    WHERE a.account_status = 'AVTIVE' 
    AND a.semesteer = '6' 
    AND b.assesor_status = 'PENDING'";
$q = mysql_query($sql) or die("Query failed: ".mysql_error());
while ($row = mysql_fetch_array($q))
{
    echo '' . $row[0];
}

and this is what its using on DB-1 to connect db-1.php

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password=""; // Mysql password 
$db_name="DB-1"; // Database name

Upvotes: 0

Views: 1773

Answers (3)

Pankaj Garg
Pankaj Garg

Reputation: 1322

I will also recommend you to use mysqli or PDO for connecting with mysql as mysql extension is deprecated in PHP5.5.

Additionally If you dont want to move and want to run your current code, then here is the solution :

1). First remove mysql_select_db("$db_name")or die("cannot select DB"); 2). and Use table names with their respective database names e.g. DBName.TableName

It should work!

Upvotes: 0

Michael Butler
Michael Butler

Reputation: 6309

With MySQLi you can store the different DB connections in different variables.

<?php
$mysqlOne = new mysqli("localhost", "user", "password", "database_1");
echo $mysqlOne->host_info . "\n";

$mysqlTwo= new mysqli("127.0.0.1", "user", "password", "database_2", 3306);
$result = $mysqlTwo->query("SELECT Name FROM City LIMIT 10");

Remember, you could also utilize one database connection and just change the database you're using in the SQL, such as SELECT * FROM database3.city

See: http://www.php.net/manual/en/mysqli.quickstart.connections.php

Upvotes: 3

raPHPid
raPHPid

Reputation: 567

Use mysqli (or PDO) extension for this. Mysql extension (without i) is bit outdated, and its usage is disencouraged. With mysqli you can easily make and handle several (different) connections to DB.

See: http://www.php.net/manual/en/mysqli.quickstart.connections.php

Upvotes: 0

Related Questions