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 in new code. They are no longer maintained and are officially deprecated. Learn about prepared statements instead, and use pdo or mysqli. — hjpotter92
– hjpotter92, Commented Jun 29, 2014 at 7:47
You are missing ; from string declaration statements. Also, you can simply use $database1 etc. — hjpotter92
– hjpotter92, Commented Jun 29, 2014 at 7:47
simply use $database1 means ? how can that be used directly in mysql query ? — user3787291
– user3787291, Commented Jun 29, 2014 at 7:55

Community · Accepted Answer · 2017-05-23 12:29:26Z

3

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

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Jun 29, 2014 at 7:51

Shivam Verma

8,0233 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

arielnmz Over a year ago

Great answer! But please don't encourage the use of the mysql extensions. Provide examples with either the mysqli or PDO extensions instead.

Shivam Verma Over a year ago

Hey! Yep, I wouldn't encourage this. The SO link I posted actually includes both the solutions.

arielnmz Over a year ago

You may want to reflect that on your answer, since I doubt the OP would actually read the whole referenced answer.

Shivam Verma Over a year ago

Added PDO Example. Edit 3.

arielnmz Over a year ago

That's a good point there, I agree with you. By the way, +1 for the quick, right to the point answer.

|

Jignesh Rawal · Accepted Answer · 2014-06-29 08:05:40Z

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, 2014 at 8:05

Jignesh Rawal

5497 silver badges21 bronze badges

3 Comments

arielnmz Over a year ago

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.

Jignesh Rawal Over a year ago

You may call it a suggestion.But it should be practiced very often.

arielnmz Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related