CODEWITHSUNDEEP
phpmysqlclassserverconnection
Rauno
Rauno

Reputation: 636

Connecting 2 servers in php with database class

I do know that there are answers for how to connect 2 servers to a database but my situation is a little different, so don't just redirect me to another answer. My connections are not simple 1-liners and I'm looking for the best way to proceed (giving up on the class and doing something entirely different, a way to connect 2 through that class, etc).

So this is what happened. I am currently an intern abroad and I got assigned a simple task where I had to work with 1 server, got everything finished and that seemed to be the end of story. However once we moved everything to real host, it turned out that I have 2 servers where the tables come from instead of just 1.

So this is how my 1 server connection looks like:

class.database.inc

<?php

require_once(LIB_PATH . "config.php");

class MySQLDatabase {

private $connection;
public $last_query;
private $magic_quotes_active;
private $real_escape_string_exists;

function __construct() {
    $this->open_connection();
    $this->magic_quotes_active = get_magic_quotes_gpc();
    $this->real_escape_string_exists = function_exists("mysql_real_escape_string");
}

public function open_connection() {
    $this->connection = mysqli_connect(SERVER_ADDR, SERVER_USER, SERVER_PWD, SERVER_DB);
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    } else {
        $db_select = $this->connection->query("SELECT DATABASE()");
        if (!$db_select) {
            printf("Database selection failed: %s\n", mysqli_connect_error());
            exit();
        }
    }
}

public function close_connection() {
    if (isset($this->connection)) {
        $this->connection->close();
        unset($this->connection);
    }
}

public function query($sql) {
    $this->last_query = $sql;
    $result = $this->connection->query($sql);
    $this->confirm_query($result);
    return $result;
}

public function escape_value($value) {
    if ($this->real_escape_string_exists) { // PHP v4.3.0 or higher
        // undo any magic quote effects so mysql_real_escape_string can do the work
        if ($this->magic_quotes_active) {
            $value = stripslashes($value);
        }
        $value = $this->connection->real_escape_string($value);
    } else { // before PHP v4.3.0
        // if magic quotes aren't already on then add slashes manually
        if (!$this->magic_quotes_active) {
            $value = addslashes($value);
        }
        // if magic quotes are active, then the slashes already exist
    }
    return $value;
}

// "database-neutral" methods
public function fetch_array($result_set) {
    return $result_set->fetch_array(MYSQLI_ASSOC);
}

public function num_rows($result_set) {
    return $result_set->num_rows;
}

public function insert_id() {
    // get the last id inserted over the current db connection
    return $this->connection->insert_id;
}

public function affected_rows() {
    return $this->connection->affected_rows;
}

private function confirm_query($result) {
    if (!$result) {
        $output = "Database query failed: " . mysqli_connect_error() . "<br /><br />";
        $output .= "Last SQL query: " . $this->last_query;
        die($output);
    }
}

}

$database = new MySQLDatabase();
$db = & $database;

In config.php file I just have this (removed the values, it works as of now anyway):

defined("SERVER_ADDR") ? null : define("SERVER_ADDR", );
//MYSQL username
defined("SERVER_USER") ? null : define("SERVER_USER", );
//MYSQL password
defined("SERVER_PWD") ? null : define("SERVER_PWD", );
//MYSQL database name
defined("SERVER_DB") ? null : define("SERVER_DB", );

So the question is, what is the best way to proceed. Is there a way I could just add another "config2.php" type of file and change a bit in the class to make all these functions work on both classes or should I do something entirely else.

As always, thanks for the answers I really do appreciate them.

Update:

So this is what I tried and it didn't work. When I run the SQL functions needed, it does display the correct table but function confirm query fails every time.

    <?php

require_once(LIB_PATH . "config.php");

class MySQLDatabase {

private $connection;
public $last_query;
private $magic_quotes_active;
private $real_escape_string_exists;

public $server;
public $user;
public $pw;
public $database;

function __construct() {
    $this->open_connection();
    $this->magic_quotes_active = get_magic_quotes_gpc();
    $this->real_escape_string_exists = function_exists("mysql_real_escape_string");
}

public function open_connection() {
    $this->connection = mysqli_connect($this->server, $this->user, $this->pw, $this->database);
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    } else {
        $db_select = $this->connection->query("SELECT DATABASE()");
        if (!$db_select) {
            printf("Database selection failed: %s\n", mysqli_connect_error());
            exit();
        }
    }
}

public function close_connection() {
    if (isset($this->connection)) {
        $this->connection->close();
        unset($this->connection);
    }
}

public function query($sql) {
    $this->last_query = $sql;
    $result = $this->connection->query($sql);
    $this->confirm_query($result);
    return $result;
}

public function escape_value($value) {
    if ($this->real_escape_string_exists) { // PHP v4.3.0 or higher
        // undo any magic quote effects so mysql_real_escape_string can do the work
        if ($this->magic_quotes_active) {
            $value = stripslashes($value);
        }
        $value = $this->connection->real_escape_string($value);
    } else { // before PHP v4.3.0
        // if magic quotes aren't already on then add slashes manually
        if (!$this->magic_quotes_active) {
            $value = addslashes($value);
        }
        // if magic quotes are active, then the slashes already exist
    }
    return $value;
}

// "database-neutral" methods
public function fetch_array($result_set) {
    return $result_set->fetch_array(MYSQLI_ASSOC);
}

public function num_rows($result_set) {
    return $result_set->num_rows;
}

public function insert_id() {
    // get the last id inserted over the current db connection
    return $this->connection->insert_id;
}

public function affected_rows() {
    return $this->connection->affected_rows;
}

private function confirm_query($result) {
    if (!$result) {
        $output = "Database query failed: " . mysqli_connect_error() . "<br /><br />";
        $output .= "Last SQL query: " . $this->last_query;
        die($output);
    }
}

}

$database = new MySQLDatabase();
$database->server = SERVER_ADDR;
$database->user = SERVER_USER;
$database->pw = SERVER_PWD;
$database->database = SERVER_DB;

$db = & $database;

Perhaps some additional info would help so this is where I use 1 function to get data from SQL (they all fail now so I guess 1 example is enough for start).

users.php:

//more code
 $max_users = User::count_all(); //max users   
//more code

class.database_query.inc:

require_once(LIB_PATH . "class.database.inc");

class DatabaseObject {

    protected static $table_name;

//more functions

//the one in use:
    public static function count_all() {
        global $database;
        $sql = "SELECT COUNT(*) FROM " . static::$table_name;
        $result_set = $database->query($sql);
        $row = $database->fetch_array($result_set);
        return array_shift($row);
    }

//more functions

}

And this is the result I get when loading users.php:

" Database query failed:

Last SQL query: SELECT COUNT(*) FROM table_name" (That same SQL query works when I type it directly in phpmyadmin).

And yeah.. it all works as long as I use 1 server only (the first block on top of the page).

Upvotes: 2

Views: 1081

Answers (1)

Pinke Helga
Pinke Helga

Reputation: 6682

Why do you rely on define constant statements? You can write .ini-files and read the configuration with $array = parse_ini_file(filename). Doing so, you can call the constructor with the filename of your specific configuration (or more advanced a section name).

Since you are talking about "the best way to proceed" - if you really need mysql_real_escape_string (dynamic $table_name is one reason, when there's no other way) and handle the case that it might not exist you should (if it does not) deny any access or provide at least a polyfill with regular expressions. mysqli is better the mysql, consider usage of PDO. When ever possible, use parameterized prepared statements to improve security.

The misusage of classes to provide static only methods is an obsolete concept since in newer PHP versions context separation is well handle by namespaces.

Upvotes: 1

Related Questions