How to make php code connect to two different database with two different host?

Question

Details:

I am creating a registration page for a game, and the game has two server with different ip's/host and different database, the other one is hosted on Euro and the other is hosted on USA, and what I am planning on my registration page are when the user successfully fill up the form and click "Register" button, the info the user put will be send to the two different database, I search a topic related to my problem, and follow the instructions to fixed it, but it didn't do any good thing. So if you will ask on my code here it is.

Database.php

        <?php

    class Database{ 
       private static $instance = null;
       private $stmt = null;
       private $host = '';
       private $dbname = '';
       private $username = '';
       private $password = '';

        public function __construct(){
            $dns = "mysql:dbname=".$this->dbname.";host:".$this->host;
            $username= $this->username;
            $password= $this->password;
            $db = null;

            $this->db = new PDO($dns,$username,$password);
        }

        public static function getInstance(){
            if(self::$instance == null){
                try{    
                    self::$instance = new Database();
                }catch(PDOException $e){
                    echo $e->getCode();
                }
            }

            return self::$instance;
        }

        public function query($stmt){
            $this->stmt = $this->db->prepare($stmt);
        }

       //get sql statement
       public function getStmt(){
          return $this->stmt;
       }

        public function bind($index, $value, $type = null){

            if (is_null($type)) {
                switch (true) {
                    case is_int($value):
                        $type = PDO::PARAM_INT;
                        break;
                    case is_bool($value):
                        $type = PDO::PARAM_BOOL;
                        break;
                    case is_null($value):
                        $type = PDO::PARAM_NULL;
                        break;
                    default:
                        $type = PDO::PARAM_STR;
               }
            }

            $this->stmt->bindValue($index, $value, $type);
        }

        public function execute(){
            return $this->stmt->execute();
        }

        public function resultset(){
            $this->execute();
            return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        public function single()
        {
            $this->execute();
            return $this->stmt->fetch(PDO::FETCH_ASSOC);
        }

        public function __destruct(){
            $this->db = null;
        }
    }

I'm sorry if i didn't add the database info, just for security sake :), so I think all I need to do is to add something on the Database.php , but I really don't know what.

now here is the validation process code of my registration page

RegistrationProcess.php

<?php
   include_once 'Input.php';
   include_once 'Database.php';
   include_once 'Helper.php';

   //variables
   $db = Database::getInstance();
   $username = '';
   $password = '';
   $email = '';
   $country = '';
   $city = '';
   $question = '';
   $answer = '';
   $ip = Helper::get_ip_address();
   $valid = true;
   $msg = '';

   if (Input::hasPost()) {
      if (Input::post('username') === null) { 
         $msg = 'Please put your username.';
         $valid = false;
         $username = Input::post('username');
      } else if (Input::post('password') === null || Input::post('con_password') === null ) {
         $msg = 'Input password properly!';
         $valid = false;
      } else if (Input::post('password') !== Input::post('con_password')) {
         $msg = 'Password and confirm password must be the same.';
         $valid = false;
      } else   if (Input::post('country') === 'select') {
         $msg = 'Select a country.';
         $valid = false;
      } else if (Input::post('email') === null) {
         $msg = 'Put your email.';
         $valid = false;
      } else if (!filter_var(Input::post('email'), FILTER_VALIDATE_EMAIL)) {
         $msg = 'Invalid email.';
         $valid = false;
      } else if (Input::post('question') === 'select') {
         $msg = 'Select a question.';
         $valid = false;
      } else   if (Input::post('answer') === null) {
         $msg = 'Put your answer.';
         $valid = false;
      }

      if ($valid) {
         $db = Database::getInstance();
         $username = Input::post('username');
         $password = Input::post('con_password');
         $email = Input::post('email');
         $country = Input::post('country');
         $question = Input::post('question');
         $answer = Input::post('answer');

         //banned account
         $sql = "SELECT * FROM accounts WHERE IP = :ip AND State = 1";
         $db->query($sql);
         $db->bind(':ip', $ip);
         $banned = $db->single();

         // > 1 account
         $sql = "SELECT * FROM accounts WHERE IP = :ip";
         $db->query($sql);
         $db->bind(':ip', $ip);
         $anotherAccount = $db->resultset();

         //already exist name
         $sql = "SELECT * FROM accounts WHERE Username = :username";
         $db->query($sql);
         $db->bind(':username', $username);
         $existAccount = $db->single();

         if ($existAccount && (count($existAccount) > 0)) {
            $msg = 'The Username is already exist.';
            $valid = false;
         } else if ($banned && (count($banned) > 0)) {
            $msg = 'Sorry you cannot register because your banned!';
            $valid = false;
         } else if ($anotherAccount && (count($anotherAccount) > 5)) {
            $msg= 'Sorry you cannot register because you made to many accounts.';
            $valid = false;
         } else {
            $sql = "INSERT INTO accounts (`Username`, `Password`, `IP`, `Email`, `Question`, `answer`, `Country`)
                  VALUES (:username, :password, :ip, :email, :question, :answer, :country)";

            $db->query($sql);

            $db->bind(':username', $username);
            $db->bind(':password', $password);
            $db->bind(':ip', $ip);
            $db->bind(':email', $email);
            $db->bind(':question', $question);
            $db->bind(':answer', $answer);
            $db->bind(':country', $country);

            if ($db->execute()) {
               $msg = "The user has been succesfully added into the database! You can now log into the community and in-game!";
               $msg .= " Thank you for registering for West Gaming!<br>";
               $msg .= "<span class='management'>- West Gaming Staff</span>";
               $valid = true;
               unset($_POST);
            } else {
               $msg = 'There is a problem in our system <br>';
               $msg .= 'Please register again with the same data.';
               $valid = false;
            }
         }
      }
   }

and that is.. hope you can help me and if you do, Thanks in advance.

You could just make a instance of your database class for each of your database hosts. Then do two sql queries on your inserts using each of the database vars. I would look into having one of your databases sync with other instead. — Jacob
– Jacob, Commented Apr 30, 2014 at 5:53
Thank you for your suggestion, I will try to.. but not now, since it will be needed to release today. ^^ — Itsmezack
– Itsmezack, Commented Apr 30, 2014 at 5:53
Disregard my previous comment. I was switching between posts and was not paying attention. You are using PDO form in this code. My sincere apologies. — Brandon White
– Brandon White, Commented Apr 30, 2014 at 6:04

Didar_Uranov · Accepted Answer · 2014-04-30 05:52:29Z

1

Just create 2 Database class instances.

Rid of, public static function getInstance(){} Dont do like this:

$db = Database::getInstance();

Instead:

$db1 = new Database();
$db2 = new Database();

Edit Database constructor to something like

public function __construct($dns, $username, $password){
    $this->db = new PDO($dns,$username,$password);
}

answered Apr 30, 2014 at 5:52

Didar_Uranov

1,24013 silver badges26 bronze badges

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

8 Comments

Itsmezack Over a year ago

by the mean of "Create 2 Database" is do it like this class Database1{ private static $instance = null; private $stmt = null; private $host = ''; private $dbname = ''; private $username = ''; private $password = ''; class Database2{ private static $instance = null; private $stmt = null; private $host = ''; private $dbname = ''; private $username = ''; private $password = '';

Didar_Uranov Over a year ago

Rid of = remove. Exactly, use $db1 = new Database(...)

Didar_Uranov Over a year ago

No! The Database class should be only 1. Edit Database constructor like I suggested, and pass different connection information, i.e : $db1 = new Database('host:91.213.xx.1', ...); $db2 = new Database('host:91.213.xx.2', ...)

Itsmezack Over a year ago

Oh okay, I should just leave the database class, and follow your instruction and follow the changes on the public function constructor, then on the $db1 = new Database('host,'dbname','user','pass') and $db2 = new Database('host', 'dbname', 'user', 'pass') then that's ok, no need to change anything. (Sorry, I am not that knowledgeable on php.)

Itsmezack Over a year ago

There ya go.. I have rid of public static function getInstance(){} and did the changes in the publick function, then I received this error Fatal error: Call to undefined method Database::getInstance() in D:\..\xampp\htdocs\.._inc\RegistrationProcess.php on line 7

|

Brandon White · Accepted Answer · 2014-04-30 05:58:57Z

Based on your code it appears as if you could just create two different objects of class Database. Upon construction, just pass the different connection information. See Objects and Classes.

You would need to change your construction code to the following, however:

public function __construct($host, password, $user, $db){
    // Set Object Properties accordingly
    $this->host = $host;
    $this->username = $user;
    $this->password = $password;
    $this->dbname = $db;


    $dns = "mysql:dbname=".$this->dbname.";host:".$this->host;
    $username= $this->username;
    $password= $this->password;
    $db = null;

    $this->db = new PDO($dns,$username,$password);
}

Construction

When creating your object, you would now need to pass this information:

$db1 = new Database("hostname", "password", "username", "database_name");
$db2 = new Database("hostname2", "password2", "username2", "database_name2");

Referencing

When Referencing each different database you would call according to the information you need to seek.

// for the primary database
$db1->GetStmt();
// or for the secondary database
$db2->GetStmt();

Collectives™ on Stack Overflow

How to make php code connect to two different database with two different host?

2 Answers 2

8 Comments

Construction

Referencing

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Construction

Referencing

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related