Jump to content

Archived

This topic is now archived and is closed to further replies.

TrickyInt

Database class - necessary?

Recommended Posts

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!

Share this post


Link to post
Share on other sites

Learning is always worth the time.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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;
  }
}

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.