# Handling access to database connection both inside and outside of classes

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.

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.

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.

• 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.. – ThomasK Jan 25 '14 at 10:28
• @ThomasK singletons are useful when used correctly, but some people argue that they hinder automated testing, as they are not easy to override. – d11wtq Jan 25 '14 at 11:17
• Can whoever down-voted this answer please explain why? – d11wtq Jan 25 '14 at 11:18
• 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()? – ThomasK Jan 25 '14 at 11:26
• 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.. – ThomasK Jan 25 '14 at 12:31