A nice and simple PHP tutorial today where I will show you how to connect to multiple MySQL databases at once using PHP. I’m sure when you first considered the option of multiple connections you thought it wasn’t possible, difficult to do or there is a long winded way of doing it (such as disconnecting and re-connecting each time you want to make a query). This is untrue. All that you need to do is define a new connection and save it as a seperate resource. So, a simple one-database connection would look like this:
<?php
// connect to the database server
$conn = mysql_connect("HOST", "USERNAME", "PASSWORD");
// select the database to connect to
mysql_select_db("DATABASE", $conn);
// query the database
$query = mysql_query("SELECT * FROM users", $conn);
// close the database connection
mysql_close($conn);
?>
Now if you were to have a second connection, instead of disconnecting from $conn and re-connecting as a seperate connection, you can keep the first one alive and create a second. Just like follows:
<?php
// connect to the database server
$conn = mysql_connect("HOST", "USERNAME", "PASSWORD");
// select the database to connect to
mysql_select_db("DATABASE", $conn);
// connect to the second database server
$conn2 = mysql_connect("HOST", "USERNAME", "PASSWORD");
// select the database to connect to
mysql_select_db("DATABASE", $conn2);
// query the first database
$query = mysql_query("SELECT * FROM users", $conn);
// query the second database
$query = mysql_query("SELECT * FROM articles", $conn2);
// close the database connections
mysql_close($conn);
mysql_close($conn2);
?>
As you can see I have two open connections and I am interacting with both databases. At the end I simply close both connections. It is as simple as that. Any questions please don’t hesitate to ask in the comments section below. Alternatively ping me a message or a tweet on Twitter – @rossytzoltan.
If you enjoyed this article, please consider sharing it!
This is great but is there a way to have the first connection without the $conn ? I want to add the second connection to an already written script that has lots of querys already but I still need lots of querys from the second database. I was thinking of some kind of if statement to see if there is a $conn2 defined if there is then use database 2 in my generic class, if not then carry on and use the default db connection.
Thanks in advance.
Yes, you could do this. I assume $conn already exists from your other queries but perhaps as a different variable name.
You could then say:
if ( isset ( $conn2 ) && is_resource ( $conn2 ) ) {
$db_resource = $conn2;
} else {
$db_resource = $conn;
}
Your connections would then rely upon $db_resource but this could be one of the two connections, depending on whether or not $conn2 is set. Is that what you meant? So you have 2 active connections but only one is being accessed at a time and you are just switching between them.
Ross