MySQL & PHP: auto connect to DB or to properly way to pass host/db to MySQL methods

does anyone know of a known method in PHP to auto connect to MySQL db/table in case an app is using multiple databases on multip开发者_如何转开发le hosts?

Question 1: are there scripts around that allow to auto connect to necessary host/DB based on query?

Question 2: if above is not possible, is there a known approach to properly passing host/DB info to make sure app is properly connected before executing the query?

---

You should write a wrapper class. This class will have a function, query. From here, you have some options:

1. Hard-code the login info into the function.
2. Set server variables in the Apache config.
3. Pass the login info to your query function every time.

If you use option 1 or 2, you will probably want to split the components of your query into multiple parameters, so your query function would look more like query($operation, $columns, $tableName, $whereClause, $orderBy). There are other parameters you could possibly add as well, but this is just an example of how you could do it.

---

Another possibility with your wrapper class (which is the method I prefer) would be to create instances of the class, each with its own MySQL connection. Just create one instance of the class for each connection you will use, connect to it at the beginning of the script, and just use the correct instance for your query. For example:

<?php
require_once("MYSQL.php");
$db1 = new MYSQL("database1", "username1", "password1", "hostName1");
$db2 = new MYSQL("database2", "username2", "password2", "hostName2");

$index = $db1->query("SELECT * FROM tableOnDB1") or die($db1->error());
while ($result = $db1->fetch($index))
    echo $result["column1"];

$index = $db2->query("SELECT * FROM tableOnDB2") or die($db2->error());
list($value) = $db2->fetch($index, "ROW");
echo $value;
?>

Here is MYSQL.php:

<?php
    class MYSQL_fetch extends MYSQL
    {
        protected static function fetch($index, $flag = "", $mysql = "")
        {
            if (empty($mysql))
                $mysql = $flag;

            if (!$mysql->ivalid($index))
                return false;

            if ($flag == self::ROW)
                $return = mysql_fetch_row($mysql->results[$index][1]);
            else if ($flag == self::ARR)
                $return = mysql_fetch_array($mysql->results[$index][1]);
            else if ($flag == self::OBJ)
                $return = mysql_fetch_object($mysql->results[$index][1]);
            else
                $return = mysql_fetch_assoc($mysql->results[$index][1]);

            if ($return === false)
                mysql_free_result($mysql->results[$index][1]);

            return $return;
        }

        protected static function querystr($index, $mysql)
        {
            return $mysql->results[$index][0];
        }
    }

    class MYSQL
    {
        protected $connection;
        protected $db;
        protected $type;
        protected $results;
        protected $errormsg;
        protected $valid_instance;

        const E_INDEF_METHOD    = "Undefined method: ";
        const E_INVALID         = "Invalid instance of MYSQL";
        const E_BOUNDS          = "Invalid MYSQL index identifier: ";
        const E_FAIL_PROG_MOVE  = "Cannot move progression, result is either invalid or out of bounds";
        const E_FAIL_PROG_SET   = "Cannot set progression, result is either invalid or out of  bounds";

        const ASSOC             = "ASSOC";
        const ROW               = "ROW";
        const ARR               = "ARRAY";
        const OBJ               = "OBJECT";

        function __construct($db, $server = "", $username = "", $password = "")
        {
            if (get_class($this) != "MYSQL")
            {
                $this->valid_instance = false;
                $this->errormsg = self::E_INVALID;
                return;
            }

            $this->valid_instance = true;
            $this->errormsg = false;
            $this->db = $db;
            $this->results = array();
            $this->type = "MYSQL_fetch";

            $this->connection = @mysql_connect($server, $username, $password, true);
            if ($this->connection)
            {
                if (mysql_select_db($db, $this->connection))
                    register_shutdown_function(array(&$this, "close"));
                else
                    $this->errormsg = mysql_error();
            }
            else
                $this->errormsg = mysql_error();
        }

        function __destruct()
        {
            $this->close();
        }

        private function valid()
        {
            return ($this->valid_instance && isset($this->connection));
        }

        protected function ivalid($index)
        {
            if (!$this->valid())
                return false;

            if (is_numeric($index) && $index < count($this->results))
                return true;
            else
                die("error: ". count($this->results));

            $this->errormsg = self::E_BOUNDS . $index;
            return false;
        }

        public function query()
        {
            if (!$this->valid())
                return false;

            $args = func_get_args();
            $sql = trim(array_shift($args));

            if (func_num_args() > 1)
            {
                foreach ($args as $value)
                {
                    $pos = strpos($sql, "?");
                    if ($pos !== false)
                    {
                        if (is_array($value))
                        {
                            $new = "(";
                            foreach ($value as $val)
                                $new .= "'". mysql_real_escape_string($val) ."', ";
                            $sql = substr_replace($sql, trim($new, ", ") .")", $pos, 1);
                        }
                        else
                            $sql = substr_replace($sql, "'". mysql_real_escape_string($value) ."'", $pos, 1);
                    }
                }
            }

            if (!$resource = @mysql_query($sql, $this->connection))
            {
                $backtrace = debug_backtrace();
                if ($this->errormsg == "")
                    $this->errormsg = "<b>SQL Error at Line ". $backtrace[0]["line"] ." in ". $backtrace[0]["file"] .":</b><br />". mysql_error($this->connection) ."<pre>$sql</pre>";
                return false;
            }

            $this->results[] = array($sql, $resource);
            return count($this->results);
        }

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

        public function __call($method, $args)
        {
            if (!$this->valid())
                return false;

            if (!method_exists($this->type, $method))
            {
                $this->errormsg = self::E_INDEF_METHOD . $method;
                return false;
            }

            --$args[0];
            $args[] = &$this;
            return call_user_func_array($this->type ."::". $method, $args);
        }

        public function error()
        {
            if (is_array($this->errormsg))
                return htmlentities($this->errormsg["message"]);

            return $this->errormsg;
        }

        public function clearError()
        {
            $this->errormsg = "";
        }

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

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

        public function escape($value)
        {
            return mysql_real_escape_string($value);
        }
    }
?>

It also sanitizes input for you if you use it properly. Another sample:

$mysql->query("SELECT * FROM table WHERE value = ?", $value) or die($mysql->error());
$mysql->query("SELECT * FROM table WHERE value IN ?", array($value1, $value2, $value3)) or die($mysql->error());
$mysql->query("INSERT INTO table (column1, column2) VALUES ?", array($value1, $value2)) or die($mysql->error());