This is my first attempt at creating a DAO.
I would like to get some feedback regarding the following aspects if possible:
- Code readability
- Efficiency
- Usability
I also would appreciate any other suggestions for improvement as well.
UserDAO
class UserDAO
{
private $pdo;
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}
public function fetchById($id)
{
$sql = 'SELECT id,';
$sql .= ' firstname,';
$sql .= ' lastname,';
$sql .= ' email,';
$sql .= ' password';
$sql .= ' FROM users';
$sql .= ' WHERE id = :id';
$sql .= ' LIMIT 1';
$sth = $this->pdo->prepare($sql);
$sth->bindValue(':id', $id, PDO::PARAM_INT);
$sth->execute();
return $sth->fetch() ?: null;
}
public function fetchByEmail($email)
{
$sql = 'SELECT id,';
$sql .= ' firstname,';
$sql .= ' lastname,';
$sql .= ' email,';
$sql .= ' password';
$sql .= ' FROM users';
$sql .= ' WHERE email = :email';
$sql .= ' LIMIT 1';
$sth = $this->pdo->prepare($sql);
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->execute();
return $sth->fetch() ?: null;
}
public function insert($firstname, $lastname, $email, $password)
{
$sql = 'INSERT INTO users (firstname, lastname, email, password) ';
$sql .= 'VALUES(:firstname, :lastname, :email, :password)';
$sth = $this->pdo->prepare($sql);
$sth->bindValue(':firstname', $firstname, PDO::PARAM_STR);
$sth->bindValue(':lastname', $lastname, PDO::PARAM_STR);
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->bindValue(':password', $password, PDO::PARAM_STR);
return $sth->execute();
}
public function update($id, $firstname, $lastname, $email, $password)
{
$sql = 'UPDATE users';
$sql .= ' SET firstname = :firstname,';
$sql .= ' lastname = :lastname,';
$sql .= ' email = :email,';
$sql .= ' password = :password';
$sql .= ' WHERE id = :id';
$sth = $this->pdo->prepare($sql);
$sth->bindValue(':id', $id, PDO::PARAM_INT);
$sth->bindValue(':firstname', $firstname, PDO::PARAM_STR);
$sth->bindValue(':lastname', $lastname, PDO::PARAM_STR);
$sth->bindValue(':email', $email, PDO::PARAM_STR);
$sth->bindValue(':password', $password, PDO::PARAM_STR);
$sth->execute();
return $sth->rowCount();
}
public function delete($id)
{
$sql = 'DELETE FROM users ';
$sql .= ' WHERE id = :id';
$sth = $this->pdo->prepare($sql);
$sth->bindValue(':id', $id, PDO::PARAM_INT);
$sth->execute();
return $sth->rowCount();
}
}
Database
class Database extends PDO
{
private $options = [];
private $dsn;
private $username;
private $password;
private $connected;
public function __construct($dsn, $username, $password, Array $options = [])
{
$this->dsn = $dsn;
$this->username = $username;
$this->password = $password;
$this->options = $options;
}
public function prepare($sql, Array $driverOptions = [])
{
$this->connect();
return parent::prepare($sql, $driverOptions);
}
public function execute(Array $inputParameters = [])
{
$this->connect();
return parent::execute($inputParameters);
}
private function connect()
{
if (!$this->connected) {
parent::__construct($this->dsn, $this->username, $this->password, $this->options);
$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
$this->connected = true;
}
}
}