使用mysqli对象使用php对象连接到mysql的最佳方法

Question

If I have in my php app an object that connects to the database, lets say I am using mysqli as on object for my database transactions.

example:

$dbase = new mysqli('localhost','dbuser','dbpass','dbname');
$oresult = $dbase->query("SELECT `field` FROM `table` WHERE `otherfield` = 12;");
if($oresult->num_rows > 0) {
    $row = $oresult->fetch_row();
    $data = $row[0];
}

but I have another custom object that I want to talk to the dbase.

<?php
class Thing {
    private $sql = '';
    public $results = '';

    public function __construct($sql) {
        $this->sql = $sql;
        $this->get_data();
    }

    private function get_data() {
         // get the stuff from the dbase using $this->sql
         $this->results = 'whatever';
    }
}

$thing = new Thing("SELECT `field` FROM `table` WHERE 1");
// do whatever i want with $thing->results
?>

Where I have the '// get the stuff from the dbase using $this->sql' line i would want to connect to the dbase and get the data.

Is it best to create a new mysqli object (which i see issues with because I would need to get the connection information passed to every object I have) or can I somehow reference the object I already have by using

global $dbase

inside the get_data function.

Whats best practise?

Answer 1

Create a wrapper class for DB connections. The wrapper could be a singleton or it could store the mysqli connection in a static field.

class DB {
    static public $_connection;
    static function connection(...) {
        if (! self::$_connection) {
            self::$_connection = mysqli_connect(...);
        }
        return self::$_connection;
    }
}

This also makes it easy to isolate user credentials, storing them in a single script or a configuration file.

Instead of class DB exposing the connection, you could use the DB class itself. Turn connection() into a constructor, write a prepare() method and a DBStatement class.

class DB {
    static private $_connection;
    function __construct(...) {
        if (! self::$_connection) {
            self::$_connection = mysqli_connect(...);
        }
    }
    // returns an SQLStatement
    function prepare($query) {
    }
}