Main Page Content
Failover Database Connection with PHP + mySQL
Rated 2.83 (Ratings: 4) (Add your rating)
Log in to add a comment
(7 comments so far)
Stephen Belue
User since: February 18, 2007
Last login: March 03, 2007
Articles written: 1
Imagine, you're connecting to a mySQL database with PHP, but hell, you want a bit more robustness. So why would you want just one database? You'd use a failover right? So if the first database is down or just doesn't connect, it can connect to the next one. Here's how to do that (I'm assuming you're already replicating between databases so that they're roughly in sync).
Here's how you do it. Firstly, you'll have to define some things...
<?php
$host=""; //Database host.
$user=""; //Database username.
$pass=""; //Database password.
$dbase=""; //Database.
$host2=""; //Database host two... This is optional: in case the other database cannot connect.
$user2=""; //Database username two... This is optional: in case the other database cannot connect.
$pass2=""; //Database password two... This is optional: in case the other database cannot connect.
$dbase2=""; //Database two... This is optional: in case the other database cannot connect.
?>
View the comments, they are simple yet... informative. Now, the one thing that can make all of this come true:
<?php
$connect=mysql_connect($host,$user,$pass); //Connect to the database.
?>
$connect does that, what would $connect2 do?
<?php
$connect2=mysql_connect($host2,$user2,$pass2); //Connect to the database.
?>
It connects to the database! But, oh, it's the second one. Strange, isn't it? Now, you can check if the database connected or not by checking if its true, or false.
<?php
if (!$connect) {
mysql_close($connect); //Ends the current connection to the database.
echo "Cannot connect to the first database!"; //Display the text inside the quotes.
}
else {
echo "Connected to the first database!"; //Display the text inside the quotes.
}
?>
Of course, how does it connect to the second database? Simple!
<?php
if (!$connect) {
mysql_close($connect); //Ends the current connection to the database.
echo "Cannot connect to the first database!<br />"; //Display text inside the quotes.
if (!$connect2) {
mysql_close($connect2); //Ends the current connection to the second database.
echo "Cannot connect to second database!"; //Display text inside the quotes.
}
else {
echo "Connected to the second database!"; //Display text inside the quotes.
}
}
else {
echo "Connected to the first database!<br />Did not connect to the second database!"; //Display text in quotes
}
?>
Now, that's all this really does. It has some mysql_error() stuff in it too, and checks if the url is connection.php?type=1 before executing the code. I basically combined all of that into one script, all you have to do is edit the variables, and do:
<?php
include "connection.php";
?>
Here's the full script:
<?php
/////////////////////////////////////////////////////////
//Name --- Connection.php //
/////////////////////////////////////////////////////////
//Description --- Connects to a mySQL database easily //
/////////////////////////////////////////////////////////
//Author --- [SJB]Dude7 //
/////////////////////////////////////////////////////////
$host=""; //Database host.
$user=""; //Database username.
$pass=""; //Database password.
$dbase=""; //Database.
$host2=""; //Database host two... This is optional: in case the other database cannot connect.
$user2=""; //Database username two... This is optional: in case the other database cannot connect.
$pass2=""; //Database password two... This is optional: in case the other database cannot connect.
$dbase2=""; //Database two... This is optional: in case the other database cannot connect.
$connect=mysql_connect($host,$user,$pass); //Connect to the database.
if ($_GET["type"]=="1") {
if (!$connect) {
mysql_close($connect); //End the connection to possibly prevent SQL injection or just for safety.
$connect2=mysql_connect($host2,$user2,$pass2); //Connect to the host.
if (!$connect2) {
die("<b>ERROR:</b> " . mysql_error()); //Receive an error if it cannot connect.
mysql_close($connect2); //End the connection to possibly prevent SQL injection or just for safety.
}
else {
$dbconnect2=mysql_select_db($dbase2, $connect2); //Select the database.
if (!$dbconnect2) {
die("<b>ERROR:</b> " . mysql_error()); //Receive an error if it cannot connect.
mysql_close($connect2); //End the connection to possibly prevent SQL injection or just for safety.
}
}
}
else {
$dbconnect=mysql_select_db($dbase, $connect); //Select the database.
if (!$dbconnect) {
die("<b>ERROR:</b> " . mysql_error()); //Receive an error if it cannot connect.
mysql_close($connect); //End the connection to possibly prevent SQL injection or just for safety.
}
}
}
else {
echo "<b>ERROR:</b> Invalid argument '" . $_GET["type"] . "'."; //If the connect.php?type doesn't equal one, it will display this error.
}
?>
