Best way to connect to mysql with php objects using mysqli object

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

Mysql is very quick in making connections, so I tend to open and close my connections where they are needed.

So, if I need to make 3 database queries in order to get the results that I need to return, then I do that, but at the end I will close that connection.

So, my controller makes a connection, makes the calls to the DAO that is needed, and then closes it.

So, if this Thing class is just going to use a connection that may already have been made, then I would just make the connection and then close it in this class.

The problem with passing it around outside of a controller is that it is easy to get lost as to the state of the connection.

Answer 2

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) {
    }
}

Answer 3

I have a factory class that makes the connections and caches them by hint for the next call. It would support creating multiple connections to the same database for the purpose of keeping connection credentials separate.

class dbtool 
{
    private static $instance   = false;
    private static $connections= false;

    private function __construct() { 
        if( ! self::$instance ) {
            self::$instance = $this;
            self::$connections = array();
        }
    }
    public function getInstance() { 
        if( ! self::$instance ) 
            self::$instance = new dbtool();
        return self::$instance;
    }
    public static function getConnection( $hint )
    {
        if( ! self::$instance ) return false;
        if( ! array_key_exists( $hint, self::$connections ))
            self::$connections[ $hint ] = self::$connectByHint( $hint );
        return self::$connections[ $hint ];
    }
    // a list of database creds by hint, etc...
    private static function connectByHint( $hint ) {}
}

Closing connections happens when the script exits. If you're running a batch process, like a daemon, you might want to wrap the connections themselves in a homegrown connector class that does a mysqli_ping() to assert the connection is still alive, and if not, reconnect.

I also discourage keeping database passwords as member variables, as they can be exposed using a print_r() or var_export(). Can you guess what I would suggest for passwords?