PDO, OOP, and DB Connection - Class opinion on the code quality

[codeispoetry]

<?php
class Database {
	private $host   = DB_HOST;
	private $user   = DB_USER;
	private $pass   = DB_PASS;
	private $dbname = DB_NAME;
	
	private $dbh;
	private $error;
	private $stmt;
	
	public function __construct() {
		// Set DSN
		$dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
		// Set options
		$options = array (
				PDO::ATTR_PERSISTENT => true,
				PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION 
		);
		// Create a new PDO instanace
		try {
			$this->dbh = new PDO ($dsn, $this->user, $this->pass, $options);
		}		// Catch any errors
		catch ( PDOException $e ) {
			$this->error = $e->getMessage();
		}
	}
	
	
	public function query($query) {
		$this->stmt = $this->dbh->prepare($query);
	}
	
	
	public function bind($param, $value, $type = null) {
		if (is_null ( $type )) {
			switch (true) {
				case is_int ( $value ) :
					$type = PDO::PARAM_INT;
					break;
				case is_bool ( $value ) :
					$type = PDO::PARAM_BOOL;
					break;
				case is_null ( $value ) :
					$type = PDO::PARAM_NULL;
					break;
				default :
					$type = PDO::PARAM_STR;
			}
		}
		$this->stmt->bindValue ( $param, $value, $type );
	}
	
	
	public function execute(){
		return $this->stmt->execute();
	}
	
	
	public function resultset(){
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_OBJ);
	}
	
	
	public function single(){
		$this->execute();
		return $this->stmt->fetch(PDO::FETCH_OBJ);
	}
	
	
	public function rowCount(){
		return $this->stmt->rowCount();
	}
	
	
	public function lastInsertId(){
		return $this->dbh->lastInsertId();
	}
	
	
	public function beginTransaction(){
		return $this->dbh->beginTransaction();
	}
	
	
	public function endTransaction(){
		return $this->dbh->commit();
	}
	
	
	public function cancelTransaction(){
		return $this->dbh->rollBack();
	}
}

I was doing some video training. I got this. Is this a good PDO oop Class or too naive and needs immense improvement?

[m_hutley]

@codeispoetry

	private $host   = DB_HOST;
	private $user   = DB_USER;
	private $pass   = DB_PASS;
	private $dbname = DB_NAME;

Why do we need to define private variables for holding the value of a predefined constant?

[benanamen]

@codeispoetry

needs immense improvement?

This is not a good class. I am not going to get into everything that is not right. Take a look at the new clean-pdo repo I put up for a starting point.

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

[SamA74]

Haven’t we been here before?
I seem to recall you posting a similar class to this a while ago. If not, one quite similar.
This looks as useless for all the same reasons.
Pretty much every method, just duplicates what PDO already does in its native methods, only with very limited functionality.
You may as well just use vanilla PDO than restrict yourself with this.

[SamA74]

@SamA74

I seem to recall you posting a similar class to this a while ago. If not, one quite similar.

This is the one I was thinking of:-

PDO and OOP PHP

​I think any application has two very important features: Saving/Adding Data to Database and then fetching retrieving it back.​ Rest everything such as front end: HTML and DOM is another detail, including structuring application by using correct and optimized coding practices such as MVC model establishing files and folder + Object Oriented PHP. Back to the discussion: I was trying to understand PDO I stumbled upon this online free video course → = Section 5 Dealing with the PDO and the Databa …

It is quite similar, either a cut down of this one, or this one is an expansion of that one.
Either way, the comments in that topic will still stand.

[Pepster64]

The real power of PHP OOP comes not from PDO connection as that is already in Object-Oriented Programming style if you think about it. Rather it comes from the ability to do more than one thing with a class. I’m in the process of redoing my website(s) using the Active Record Design Pattern where I will be able to use the DatabaseObject class:

class DatabaseObject extends Database
{
    static protected string $table = "";
    static protected array $columns = [];

    public static function fetch_all(): array
    {
        $query = "SELECT * FROM " . static::$table;
        $stmt = self::pdo()->query($query);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function fetch_by_id($id): array
    {
        $query = "SELECT " . implode(", ", static::$db_columns) . " FROM " . static::$table . " WHERE id=:id LIMIT 1";
        $stmt = self::pdo()->prepare($query);
        $stmt->execute(['id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    protected static function create():bool
    {
        // Still working on it. 
    }

Where I will be able to use it for a cms, login and many other applications for my website. I highly recommend getting used to working with OOP first especially inheritance as once you get that down a light bulb should turn on by making silly classes like animals then narrowing it down to cats, dogs or fish as an example (There are many other examples you could do like vehicles). Once you get that down pat then look at Active Record Design pattern or many other types of patterns that are out there that will suit your need.

[rpkamp]

@Pepster64

Once you get that down pat then look at Active Record Design pattern or many other types of patterns that are out there that will suit your need.

Please don’t. Active Record is nice for Rapid Application Development where you want a prototype fast, but should not be used for anything serious. Main reason being it’s going against Single Responsibility Principle in a big way.

If you want a wrapper around PDO (which is always a good thing), I would recommend Doctrine DBAL.

Stuff like this has been written over and over, and Doctrine DBAL is very mature and stable. No need to re-invent the wheel. Use your time on solving actual business problems, instead of technical ones that have already been solved.

[ahundiak]

@Pepster64

especially inheritance as once you get that down a light bulb should turn on by making silly classes like animals then narrowing it down to cats, dogs or fish

Not to pile on but inheritance is one of the least useful aspects of OOP and should be used with caution. All of those silly tutorials using animals tend to come crashing down as soon as you realize that birds, bats and insects can all fly.