PHP - How to get mysqli_connect() to return a RESOURCE so it can be used with define()

Question

I have noticed something while testing different MySQL connection methods in PHP.

I usually use odbc_connect() when making a connection to a database in PHP. I save the resouce variable that odbc_connect() returns into a global constant (using the define() function) that I can conveniently access throughout my application. Like so:

Using odbc_connect() and saving the return value into a constant works fine:

<?php
define("_conn", odbc_connect("Driver={MySQL ODBC 5.3 Unicode Driver};Database=MyDB;", "user", "pass"));
?>

Saving the return value of mysql_connect() (the deprecated one) into a constant also works fine:

<?php
define("_conn", mysql_connect("localhost", "user", "pass", "MyDB"));
?>

However, trying to save the return value of mysqli_connect() into a constant does NOT work:

<?php
define("_conn", mysqli_connect("localhost", "user", "pass", "MyDB"));
?>
Warning: Constants may only evaluate to scalar values in C:\...\script.php on line 164

This is unfortunate, as it would be nice to use mysqli_connect() to establish a connection and save the handle into a constant, where odbc_connect() isn't available. I did research and found that the only two database connection functions that I can use with MySQL that return a RESOUCE (and can be used with the define() function) are odbc_connect() and mysql_connect() (the deprecated one). See this link: http://php.net/manual/en/resource.php

Is there a way to get mysqli_connect() to return a RESOUCE, so that I can use its return value in a constant (using the define() function)?

PDO does not return a RESOUCE either.

Answer 1

i would recommend to use the singleton pattern for this case Here a example:

<?php
 class PDOConnection {

/**
 * singleton instance
 * 
 * @var PDOConnection 
 */
protected static $_instance = null;

/**
 * Returns singleton instance of PDOConnection
 * 
 * @return PDOConnection 
 */
public static function instance() {

    if ( !isset( self::$_instance ) ) {

        self::$_instance = new PDOConnection();

    }

    return self::$_instance;
}

/**
 * Hide constructor, protected so only subclasses and self can use
 */
protected function __construct() {}

function __destruct(){}

/**
 * Return a PDO connection using the dsn and credentials provided
 * 
 * @param string $dsn The DSN to the database
 * @param string $username Database username
 * @param string $password Database password
 * @return PDO connection to the database
 * @throws PDOException
 * @throws Exception
 */
public static function getConnection() {


    $dsn = 'mysql:dbname=_____;host=_____';
    $username = '_____';
    $password = '_____';

    $conn = null;
    try {

        $conn = new \PDO($dsn, $username, $password);

        //Set common attributes
        $conn->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        return $conn;

    } catch (PDOException $e) {

        //TODO: flag to disable errors?


    }
    catch(Exception $e) {

        //TODO: flag to disable errors?


    }
}

/** PHP seems to need these stubbed to ensure true singleton **/
public function __clone()
{
    return false;
}
public function __wakeup()
{
    return false;
}
}

?>

Then you can use it from anywhere:

$dbh =  PDOConnection::getConnection();

Answer 2

Well, as you see, PHP doesn't allow to store non-scalar values in constants.

But you still can realize Singleton pattern or Registry for your needs.

Answer 3

I would certainly not recommend singleton pattern in this case, and actually, in general case either, as it makes it hard to test and extend in many cases and cannot be used to handle more than one connections at once, which may be needed at some point.

The singleton pattern only makes it easy to "globalise" the connection among your app, and certainly became famous because, at the time, there where many poorly written php app that would begin every script with a new connections resulting into many duplicated connections opened. But in fact, this would even better be addressed using global directly on the object where needed than with a singleton. Of course, it would be best to implement some kind of dependency injection or registry pattern to do so, but this is how bad I think is the singleton pattern in general, and even more when dealing with db in particular.

And you don't even need to implement your own db handler or even to use any vendors one, just like it is done in Doktor OSwaldo example, you get everything you need right from PDO :

$db = new PDO('mysql:host=localhost;dbname=myDb', 'user', 'pass');

You can wrap this into a function/method to manipulate the connection before you return it or even implement a minimalistic connection store :

function getConnection($which= 'default') {
    static $connections = [
        // connectionName => connectionOptions
        'default' => [
            'dsn'  => 'mysql:host=localhost;dbname=myDb',
            'user' => 'user',
            'pass' => 'pass',
            // 'option' => value,
            // ...
        ],
        // ...
    ], $dbStore = [];

    if (isset($dbStore[$which])) {
        return $dbStore[$which];
    }

    if (!isset($connections[$which])) {
        throw new \Exception("DB setup not supported");
    }

    $dbStore[$which] = new \PDO($connections[$which]['dsn'], $connections[$which]['user'], $connections[$which]['pass']);

    // eventually set some options that could be stored in $connections[$which]
    // $dbStore[$which]->setAttribute( ...

    return $dbStore[$which];
}

This would provide with the same ease of use and guarantees against duplicated connections as a singleton would, but would still allow you to use more than one connection at once:

// get default connection
$defaultDb = getConnection();

// get another connection
$anotherDb = getConnection('another');

Of course, you'd better store connections options in some kind of configuration (for example fetching it the first time getConnection is called). And this example is still not ideally testable (only its simplicity makes it easy to mock), but it can suffice almost as it is in many cases.