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.