Failover Database Connection with PHP + mySQL

Posted on May 19, 2007

in Code

by Stephen Belue (SJBDude7)

Rated 3.05 (Ratings: 3) (Add your rating)

Want more?

More articles in Code
More articles by SJBDude7

Stephen Belue

Member info | Full bio

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.
?>

That will connect to the mySQL database with the currently defined variables. So, obviously, if $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.
}
?>

I was born in Philly. I started to code. I read some books. Yeah.

4 comments on this article. Log in to add your comment | Rate this article:

Thanks, but..

Submitted by danielpunt on September 13, 2007 - 17:37.

.. I really don't get the $_GET['type'] thing, but apart from that it can be done with far less code :

<?php
// Connect with MySQL database
// by danielpunt;

// Login for both users
$db1['host'] = 'localhost';
$db1['user'] = 'user';
$db1['pass'] = 'pass';
$db1['dbName'] = 'database';

$db2['host'] = 'localhost';
$db2['user'] = 'user2';
$db2['pass'] = 'user2';
$db2['dbName'] = 'database';

$conn = @mysql_connect($db1['host'], $db1['user'], $db1['pass']);
if($conn != false) {
    $db = @mysql_select_db($db1['dbName']);
} else {
    $conn = @mysql_connect($db2['host'], $db2['user'], $db2['pass']);
    $db = @mysql_select_db($db2['dbName']);
} 

if($conn == false OR $db == false) {
    die("Couldn't connect. <br> #" . mysql_errno() . ' : ' . mysql_error()); 
} else {
    echo 'Whoo! Connected!';
}
?>

Switch instead of If

Submitted by Original Sin on September 24, 2007 - 13:32.

I often find that using switch() instead of if() produces cleaner and shorter code. It becomes particularly beneficial if you for instance were to add another 3 dbs to connect to.

See below for a code example using switch(). You can read more about it at php.net/switch.

<?php
$db1['host'] = 'localhost';
$db1['user'] = 'user';
$db1['pass'] = 'pass';
$db1['dbName'] = 'database';

$db2['host'] = 'localhost';
$db2['user'] = 'user2';
$db2['pass'] = 'user2';
$db2['dbName'] = 'database';

switch (true) {
    case @mysql_select_db($db1['dbName'],mysql_connect($db1['host'],$db1['user'],$db1['pass'])): break;
    case @mysql_select_db($db2['dbName'],mysql_connect($db2['host'],$db2['user'],$db2['pass'])): break;
    default: echo 'Unable to connect to the database. God save us all!'; exit();
}
?>

IF for the shortest code

Submitted by atherix on September 29, 2007 - 05:26.

A one-liner IF statement is the most efficent. Also, why echo() and exit() when you can just die()?

<?php
if (!mysql_select_db($db1['dbName'],mysql_connect($db1['host'],$db1['user'],$db1['pass'])) && !mysql_select_db($db2['dbName'],mysql_connect($db2['host'],$db2['user'],$db2['pass']))) die("Unable to connect");
?>

Syncing?

Submitted by jtnt on October 1, 2007 - 20:26.

I'd love to know your method for keeping these two databases in sync, since given the above code, this would have to happen in real time so that at any point db1 was an exact duplicate of db2.

Start of page header

Other Fine Evolt.org Sites

Navigation Starts

Article Categories

Highest rated articles

Submit

Recent comments

Main Page Content