Handling access to database connection both inside and outside of classes

Question

Is this an OK way to handle access to database connection both inside and outside of classes?

I used to include a PHP file -- database.detl.php -- at the very top of my index.php file containing the following code:

try{
    $dbh_options = array(/*...*/);
    $dbh = new PDO('mysql:host=localhost;dbname='.db_name.';charset=utf8',db_user,db_user_pw,$dbh_options);
} catch(PDOException $e) {
    /*...*/
}

Now every document that got included by index.php could use $dbh to access my database. This made it quite easy to work with.

But despite including that file before any classes got loaded, this $dbh-variable was not available from inside the classes. This was not possible to do:

class KD_sql {
    public static function get_recipes(){
        $qry = $dbh->query('SELECT * FROM recipes');
        $get = $qry->fetchAll();
        return $get;
    }
}

new KD_sql();

$recipes = sql_query::get_recipes();

This resulted in a fatal error: Call to a member function query() on a non-object....

So I guess no variables outside of a class is ever available inside unless you somehow send it there as an additional parameter to the method.

Anyway:

This is how I rewrote the code in order to still be able to have the $dbh-variable available throughout the entire site - just like before, and also have the connection available inside my KD_sql-class - without creating the connection in multiple locations:

First I changed database.detl.php into this:

try{
    $_SITE_DATABASE_details = function(){  //  <  added a function that holds the connection details
        $dbh_options = array(/*...*/);
        $dbh = new PDO('mysql:host=localhost;dbname='.db_name.';charset=utf8',db_user,db_user_pw,$dbh_options);
        return $dbh;  //  <  return connection details
    };
    new KD_db($_SITE_DATABASE_details);  //  the details is sent to a new class
    $dbh = KD_db::con();  //  <  a method, in this new class, returns the connection in order to still have $dbh available
} catch(PDOException $e) {
    /*...*/
}

This might seem a bit wierd, but let me finish.
Here's the new KD_db-class:

class KD_db {
    protected static $con = null;
#   initiate and stores the connection to the $con-variable
    public function __construct(callable $db_details){
        if (self::$con===null){
            self::$con = call_user_func($db_details);
        }
    }
//
#   the method that returns the database connection.
    public static function con(){
        return self::$con;
    }
//
}

This class is basically just a parent class that other classes can extend to in order to have the database connection available as well.

Here is the rewritten KD_sql-class:

class KD_sql extends KD_db {
    public static function get_recipes(){
        $qry = self::$con->query('SELECT * FROM recipes');  //  uses the $con -variable from parent class
        $get = $qry->fetchAll();
        return $get;
    }
}

I can now easily create quick-queries inside my KD_sql-class and just do this:

$recipes = KD_sql::get_recipes();

which quickly returns an array of all the recipes. Or, if that doesn't cut it, I can write a more complex SQL query like this:

$recipes = $dbh->prepare('
    SELECT r.columns, 
        ot.columns 
    FROM recipes r 
        JOIN other_table ot ON r.fk_other_table_pk = ot.id
    WHERE r.some_column = :value
');

But I do have some "bugs" with the returned resultset from the class. If I only use fetch(), instead of fetchAll(), I can't do a while()-loop like this:

$recipes = KD_sql::get_recipes();
while($recipes){
    echo $recipes['id'].' '.$recipes['name'].'<br>';
}

Nothing happens, beside it looks like the loop is never ending or something. That's why I'm using fetchAll() for now. Then uses foreach() because that works.

But besides that. I would really appreciate som feedback on my way of doing this. I'm not that good with classes, oop and such. So if I'm breaking any golden rules or something - please let me know.

Answer 1

Use the global keyword to make globals accessible from inside of methods and functions:

class KD_sql 
{
    public static function get_recipes()
    {
        global $dbh; // *** ADD THIS ***

        $qry = $dbh->query('SELECT * FROM recipes');
        $get = $qry->fetchAll();
        return $get;
    }
}

http://www.php.net/manual/en/language.variables.scope.php

Should you use globals for this? That is another topic completely.

Answer 2

I think you've over-complicated this and locked yourself into using inheritance (which you should avoid until you actually need it). I'm going to suggest you use a singleton, which others will incidentally say to avoid, however database connection access is a great place to use a singleton. Since you're just passing a raw PDO object around, we'll just use a static variable for this.

class DBH {
  public static function instance() {
    static $dbh = null;
    if (!$dbh) {
      $dbh = new PDO('...');
    }
    return $dbh;
  }
}

Now you can just call DBH::instance() from anywhere to get access to the database connection. This also has the benefit that it is a shared object and you don't create a connection for each part of the code that needs to use the database.