Jump to content

Database class - necessary?


TrickyInt
Go to solution Solved by ignace,

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!

Edited by TrickyInt
Link to comment
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)

Link to comment
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.

Edited by requinix
Link to comment
Share on other sites

  • Solution

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 by ignace
Link to comment
Share on other sites

  • 5 weeks later...

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.