Database class - necessary?

[TrickyInt]

Hey.

 

Just recently got into developing with PDO and OOP, instead of the old MySQL_*, so I'm pretty new, and have some dumb questions.

 

Right now I'm taking the first steps into making a blog (Nope, not a blogger - just need something to work on), and i came across a tutorial to create a database class. I have spend a little time on making such a class, and though I got it working just fine, I'm not really sure how useful and necessary it really is. Yeah, of course it saves you a couple of lines, but at the cost of getting used to using the class, instead of just raw PDO.

 

Here is the class i just quickly made:

<?php
	class database {
		private $host	=	DB_HOST;
		private $user	=	DB_USER;
		private $pass	=	DB_PASS;
		private $dbname	=	DB_NAME;
		
		public function __construct() {
			// Try connection or die.
			try {
				$this->db 	= 	new PDO('mysql:host='.$this->host.';dbname='.$this->dbname, $this->user,$this->pass);
			} catch(PDOException $e) {
				die($e);
			}
		}
		
		public function getColumn($sql, $params) {
			// Get value of a single row
			$query	=	$this->db->prepare($sql);
			$query->execute($params);
			return $query->fetchColumn();
		}
		
		public function fetch($sql, $params) {
			// Return fetch
			$query	=	$this->db->prepare($sql);
			$query->execute($params);
			return $query->fetch();
		}
		
		public function update($sql, $params) {
			// Update
			$query	=	$this->db->prepare($sql);
			$query->execute($params);
		}
		
		public function getRows($sql, $params) {
			// Return number of rows
			$query	=	$this->db->prepare($sql);
			$query->execute($params);
			return($query->rowCount());
		}
		
		public function insert($sql, $params) {
			// Insert
			$query	=	$this->db->prepare($sql);
			$query->execute($params);
		}
	}
	
?>

I know it can get way more optimized, advanced and usefull, but it's just a quick example.

 

Is it just dumb to spend time on a database class, or is it worth the time? Any advice appriciated.

 

 

Thanks in advande!

Edited May 3, 2014 by TrickyInt

[requinix]

Learning is always worth the time.

[TrickyInt]

Learning is always worth the time.

 

Indeed. But this approach might be efficient. Or it may not. I don't not, thats why I'm asking - I haven't got any experience.

 

(Not talking OOP and PDO in general, just creating a DB class)

[requinix]

Well, there are three issues I can see:

1. No way to get more than one row from a query (besides a column).

2. Can't do more than one thing with a query. Like get a count of the rows before fetching them.

3. Can't reuse a prepared statement with multiple values.

 

[edit] By the way, I'm only considering design. If I were to comment on other things, I'd say something about the PDO configuration (eg, disabling emulated prepares). Or how you could optimize in a few places by not using prepared statements if there aren't any parameters to pass.

Edited May 3, 2014 by requinix

[ignace]

Is it worth having a DB class? Yes. Pdo is such an existing class which brings you the advantage of being able to switch between vendors (mysql, postgre, sqlite, ..).

 

Is it worth writing this yourself? No, unless for learning purposes, it's better to use an existing solution written by people who know their stuff.

 

Most libraries simply extend PDO with their own functionality like Doctrine's DBAL layer.

 

What you probably are looking for is a way to centralise your queries, so instead of having these spread out over your entire application, possibly even duplicating them, you would write a few cohesive classes that will hold your queries allowing you to change these in one place.

 

abstract class AbstractRepository {
  protected $pdo;
  
  public function __construct(PDO $pdo) {
    $this->pdo = $pdo;
  }
}
class UserRepository extends AbstractRepository {
  // all queries executed by this class at the top for quick editing
  private static $QUERY_RECENT_REGISTERED_USERS = 'SELECT .. FROM users WHERE registered_at >= ?';
    
  public function getRecentRegisteredUsers($interval = '-5 hours', $timezone = null) {
    $datetime = $timezone ? new DateTime($interval, $timezone) : new DateTime($interval);
    $stmt = $this->pdo->prepare(self::$QUERY_RECENT_REGISTERED_USERS);
    // execute statement, get users
    return $users;
  }
}

Edited May 4, 2014 by ignace

[TrickyInt]

Thank you, ignace, just what i needed!

[gizmola]

Thank you, ignace, just what i needed!

 

The pattern described by ignace is much more fully fleshed out by Doctrine2, as he mentioned.  Take a look at http://www.doctrine-project.org and specifically, you might try following this:  http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/getting-started.html to add it to your project.