CODEWITHSUNDEEP
phpmysql
user3787291
user3787291

Reputation: 227

How to use multiple database names as variables in mysql query in php

I need to write 2 mysql queries in php script, where in both cases I want the data to be fetched from two different databases on the same server. But those database names are stored in two different variables.

$link1 = mysql_connect($hostname_database,$username_database,$password_database);      

$database1 = "android1";
$database2= "android2";


$result1 = mysql_query("Select * from database1.tablename");
$result2 = mysql_query("Select * from database2.tablename");

what's the correct way of achieving this ?

Upvotes: 1

Views: 858

Answers (2)

Jignesh Rawal
Jignesh Rawal

Reputation: 549

Instead use a PDO object in doing the same.
It is the lastest thing + mysql_query will be deprecated in the future versions of php

 <?php

    /*Function Definition*/

     function getDbConnection($dbName,$qry)
      {
        $db = new PDO('mysql:host=localhost;dbname='.$dbName,'username','password');
        $stmt = $db->query($qry);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
      }

   /*Call the Function*/

    $resultSet = getDbConnection(database1,$YourQuery);

 ?>

Upvotes: 0

Shivam Verma
Shivam Verma

Reputation: 8023

This is how you'll connect to two databases. You need to send true as the fourth parameter in the second connection, otherwise first connection will be used. 

$db1 = mysql_connect($hostname, $username, $password); 
$db2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('database1', $db1);
mysql_select_db('database2', $db2);

Then to query the first database : 

mysql_query('select * from tablename', $db1);

Query the second database : 

mysql_query('select * from tablename', $db2);

Edit 1 : I had used this from a SO answer but can't seem to find that answer.

Edit 2 : Found it : How do you connect to multiple MySQL databases on a single webpage?

Edit 3 : Preferred Way :

If you use PHP5 (And you should, given that PHP4 has been deprecated), you should use PDO, since this is slowly becoming the new standard. One (very) important benefit of PDO, is that it supports bound parameters, which makes for much more secure code.

You would connect through PDO, like this:

try {
  $db = new PDO('mysql:dbname=databasename;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

(Of course replace databasename, username and password above)

You can then query the database like this:

$result = $db->query("select * from tablename");
foreach ($result as $row) {
  echo $row['foo'] . "\n";
}

Or, if you have variables:

$stmt = $db->prepare("select * from tablename where id = :id");
$stmt->execute(array(':id' => 42));
$row = $stmt->fetch();

If you need multiple connections open at once, you can simply create multiple instances of PDO:

try {
  $db1 = new PDO('mysql:dbname=databas1;host=127.0.0.1', 'username', 'password');
  $db2 = new PDO('mysql:dbname=databas2;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
  echo 'Connection failed: ' . $ex->getMessage();
}

Upvotes: 3

Related Questions