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

Question

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 ?

Welcome to Stack Overflow! [Please, don't use `mysql_*` functions](http://stackoverflow.com/q/12859942/1190388) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about prepared statements instead, and use [tag:PDO] or [tag:MySQLi]. — hjpotter92, Jun 29 '14 at 07:47
You are missing `;` from string declaration statements. Also, you can simply use `$database1` etc. — hjpotter92, Jun 29 '14 at 07:47
simply use $database1 means ? how can that be used directly in mysql query ? — user3787291, Jun 29 '14 at 07:55

score 3 · Accepted Answer · edited May 23 '17 at 12:29

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

Great answer! But please don't encourage the use of the `mysql` extensions. Provide examples with either the `mysqli` or `PDO` extensions instead. — arielnmz, Jun 29 '14 at 07:55
Hey! Yep, I wouldn't encourage this. The SO link I posted actually includes both the solutions. — Shivam Verma, Jun 29 '14 at 07:59
You may want to reflect that on your answer, since I doubt the OP would actually read the whole referenced answer. — arielnmz, Jun 29 '14 at 08:02
I was just expecting that you added an *i* after *mysql* on your original post, both mysqli and PDO are *the preferred way*, but I think that still helps the OP. — arielnmz, Jun 29 '14 at 08:13
I think its always better to tell the OP what exactly he wants to know followed by suggestions, hence the original answer mysql remains :) — Shivam Verma, Jun 29 '14 at 08:16
That's a good point there, I agree with you. By the way, +1 for the quick, right to the point answer. — arielnmz, Jun 29 '14 at 08:18

score 0 · Answer 2 · answered Jun 29 '14 at 08:05

0

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

 ?>

answered Jun 29 '14 at 08:05

Jignesh Rawal

521
6
17

I think this answer should be formatted more like a suggestion, e.g. saying *you may want to use PDO* instead of *instead use PDO* since both mysqli and PDO are the way to go, the only difference is structural syntax vs oop and that pdo supports many different databases, plus oop is how most things are being developed nowadays. – arielnmz Jun 29 '14 at 08:25
You may call it a suggestion.But it should be practiced very often. – Jignesh Rawal Jun 29 '14 at 08:52
It should indeed, but pdo and mysqli are somewhat equivalent for mysql databases, so you can't say one is better than the other. By the way I do prefer PDO. – arielnmz Jun 29 '14 at 08:59

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

2 Answers2