4
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
-1
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ could you elaborate why people would say to avoid using singelton? I Don't know much about what singleton are, but I learned from a quick google search that it was a way of programming technique(?) - design pattern of some sort. I do not wish to put the details directly in the class, but rather pass them into the class.. \$\endgroup\$ – ThomasK Jan 25 '14 at 10:28
  • \$\begingroup\$ @ThomasK singletons are useful when used correctly, but some people argue that they hinder automated testing, as they are not easy to override. \$\endgroup\$ – d11wtq Jan 25 '14 at 11:17
  • 4
    \$\begingroup\$ Can whoever down-voted this answer please explain why? \$\endgroup\$ – d11wtq Jan 25 '14 at 11:18
  • \$\begingroup\$ Do you have any good tutorials that explains how to create and use singleton? something that is understandable for a person who's not familiar with it.. I think your answer makes sense though, so I'm about to try it out.. But just out of curiosity: I have a main class named KD. Is there a way to extend this DBH class to that, and access instance() like this: KD::instance()? \$\endgroup\$ – ThomasK Jan 25 '14 at 11:26
  • 1
    \$\begingroup\$ Also. I don't think I should have the dbh instance inside my KD-class because I'm not always using database. I have a variable in a settings file, unique for each project, where I can activate the use of database if it is required. And only then should database related stuff, like the database.detl.php-file, KD_db and KD_sql etc. be included.. \$\endgroup\$ – ThomasK Jan 25 '14 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.