PHP Database Class

[Solar]

Hey, I'm stumped. Through out this post, I will bold my questions.

Is global $pdo; frowned upon? is it safe? is there a better way to access it?

As of right now, I'm using the easy way of creating a database php file and including it in specific php pages to access the $pdo variable but I would like a different approach. I would like to use a database class.

Let's say I have my class/class_database.php -- converted from a non-class.

<?php
	class Database{
		private $pdo;
      
		function __construct(){
			try{
				$pdo = new PDO('mysql:host=HOST;dbname=DBNAME', "USER", "PASSWORD");
				$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			}catch(PDOException $e){
				echo 'Connection failed: '.$e->getMessage();
			} 
		}
	}
?>

I would like to create a separate class called Statements:

class Statements{
	function select($sql,$arr){
		$stmt = $this->pdo->prepare($sql);
		if(empty($arr) == false){
			foreach($arr as $k => $v){
				$stmt->bindValue($k, $v);
			}
		}
		$stmt->execute();
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}
}

The problem I'm having is that I would have to use extends in order to access the private pdo:

class Statements extends Database

Is extending a Database class safe or recommended? If not, how can I use a construct inside of Statements to obtain the Database Class PDO variable? Because every time Statements is called, it will need to use the pdo from the Database Class.

class Statements{
	private $pdo;
		
	function __construct(Database $newPDO){
		$this->pdo = $newPDO;
	}

	//functions below...
}

How exactly do I pass the pdo to the __construct function and make the call? I would easily assume that creating a get function inside of the Database Class to get the private $pdo variable is risky business.

After doing a bunch of research online, it seems like programmers fight over global pdo variables and extends of the Database because it logically doesn't make sense. Unfortunately, I have yet to find a reasonable answer. I appreciate your time!

Edited June 1, 2022 by Solar

[maxxd]

Yes, using globals is frowned upon. The reason for this is the lack of any real accountability or pinpointing modification points - if the variable is global, it can be changed literally anywhere and this can lead to days of hunt and seek debugging. The current basic pattern is to either instantiate a concrete instance of the database object and pass it to the other objects that require a database connection or setting up a dependency injection pattern/system, but that's a whole extra level of difficulty.

Extending a class is, for the most part, perfectly safe and fine. Now, the presiding opinion is to prefer composition over inheritance which means most modern advice you'll see and read is that you should - as stated above - either use dependency injection or pass an object into the constructor. There's more controversy in that most people apparently have an issue with singletons, but if you always need the same database connection you'll probably want a singleton object that you pass into the constructor methods of any class that needs it.

You pass an object to a constructor method in a series of `new` statements. Any time you write, for instance:

$db = new DatabaseObject();

you're calling the __construct() method of the DatabaseObject class. So, hopefully this'll help a bit:

<?php

class DatabaseObject{
	
	private $pdo;

	public function __construct(){
		$this->pdo = new PDO('connection_string', 'username', 'password');
		$this->pdo->setAttribute(PDO::WHATEVER);
	}

	public function getDatabaseObject(){
		return $this->pdo;
	}
}

class Statements{

	private $pdo;

	public function __construct(DatabaseObject $dbo){
		$this->pdo = $pdo;
	}
}

$pdo = new DatabaseObject();
$stmtnt = new Statements($pdo->getDatabaseObject());

A few additional things:

You can't access a private variable from anywhere other than the class that defines that variable, even if you're in a child class - in that case you'll want to use protected. Also, in your Database class constructor, you're missing `$this->` on your first line. $this->pdo is a different thing than just $pdo within a class or object.

Long story short, there's really no one "way" to do things - best practices adapt and change as we all learn and grow as programmers. Best bet is to do the research you're already doing, really take in and understand the information and opinions in that research, and apply that understanding to the problem you're trying to solve - but remember you (or worse, someone else) will always have to revisit the code you're currently writing later on. Don't make it harder on that person as much as you can.

[Solar]

Simply perfect! To know that this is fine to create a new object and then call the get function to retrieve the pdo relieves a lot of stress! Thanks a billion for this quick reply!

[maxxd]

Actually, looking at what I wrote I'm wrong in my type-hinting on the Statements class constructor - sorry about that. You'll want to pass just the DatabaseObject object and then call the getDatabaseObject method inside the Statements class. This will avoid my mistake and allow you to create multiple database connection classes that all use a common interface so you can use PDO, MySQLi, etc. as necessary.

Edited June 1, 2022 by maxxd

[benanamen]

Take a look at my "Clean PDO" Repo. It will get you going in the right direction.

https://github.com/benanamen/clean-pdo

[Strider64]

Here's my version though I believe you need PHP 8.0 to use it? (Though I could be wrong)

https://github.com/Strider64/phototechguru/blob/master/src/Database.php

 

The to use it then simply do the following:

 

    public static function fetch_by_column_name($sql)
    {
        $stmt = Database::pdo()->prepare($sql); // Database::pdo() is the PDO Connection

        $stmt->execute([ static::$searchItem => static::$searchValue ]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }