Database.php
This creates a connection and executes a query. Although, I think I should make it a static/singleton class.
class Database extends PDO
{
/**
* constant DSN contains database driver, host and database name.
*/
const DSN = 'mysql:host=localhost;dbname=matrimonial';
/**
* @var string $userName username of database.
* @var string $passWord password of database.
*/
private $userName;
private $passWord;
/**
* @var object $dbHandler An object of PDO class contain connection
* @var object $stHandler An object of PDOStatement class
*/
private $dbHandler;
private $stHandler;
/**
* @var boolean $connected Contain true if connection is OK otherwise false
*/
private $connected = FALSE;
/**
* @var string $query contain the sql query to be performed
*/
private $query;
/**
* @var mixed[]|null contain the result set returned from database or nothing/null
*/
private $result;
/**
* @var array contain error code, message etc stored by $e->getMessage(); statement
*/
private $error = array();
/**
* Constructor of class.
*
* It create a PDO (ie connection to database) object and store it to $db_handler
* set attributes to throw exception, create a PDOStatement object $st_handler
* and assign true or false to $connected according to established connection
*
* @param string $username username of database
* @param string $password
*/
function __construct($username = "root", $password = '')
{
$this->userName = $username;
$this->passWord = $password;
$opt = array( PDO::ATTR_PERSISTENT=>TRUE );
try {
$this->dbHandler = new PDO(self::DSN, $this->userName, $this->passWord, $opt);
$this->dbHandler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
$this->error = $e->getMessage();
}
$this->connected = TRUE;
$this->stHandler = new PDOStatement();
}
/**
* Execute the query and return the result.
*
* First of all prepare the given query. Query must have placeholders. Next we execute the query
* using the $value argument which contain elements equal to placeholder in same order. Result is
* returned based on query type if query is 'select' type then return array fetchAll otherwise
* return number of row effected.
*
* @param string $query the query to be executed
* @param array $values array of values, must be associative if named placeholder
* @param mixed $fetchMode data fetch mode, default is FETCH_ASSOC
* @return mixed
*/
public function prepareAndExecute($query, $values = array(), $fetchMode = PDO::FETCH_ASSOC)
{
$this->query = $query;
try {
$this->stHandler = $this->dbHandler->prepare($this->query);
$this->stHandler->execute($values);
if (preg_match("/^select/i", $this->query)) {
$this->result = $this->stHandler->fetchAll($fetchMode);
} else {
$this->result = $this->stHandler->rowCount();
}
} catch(PDOException $e) {
$this->error = $e->errorInfo;
return false;
}
return $this->result;
}
/**
* id of last row inserted
* @param string $name
* @return string last inserted id if any, otherwise return false(for select statement)
*/
public function lastInsertId($name = null)
{
return $this->dbHandler->lastInsertId();
}
/**
* @return array return the error if registered during operation
*/
public function getError()
{
return $this->error;
}
/**
* A quick and dirty function to delete rows
* @param string $query delete query to perform
* @return mixed return numbers of row deleted, 0 if no row deleted
*/
public function exec($query)
{
return $this->dbHandler->exec($query);
}
}
MatEducation.php
class MatEducation
{
/**
* @var int $id id of the user/family member
* @var int $eduId
* @var string $degree
* @var string $institute information of user
* @var int $type mapper type - User/Family member
*/
private $id;
private $eduId;
private $degree;
private $institute;
/**
* getter and setter methods for properties
* **************removed to make code shorter
*/
/**
* Convert array into object
* @param array $arr
* @return MatEducation object
*/
public function arrayToObject($arr){
$obj = new MatEducation();
$obj->id = $this->getId();
$obj->eduId = $arr['edu_id'];
$obj->degree = $arr['edu_degree'];
$obj->institute = $arr['edu_institute'];
return $obj;
}
}
CommonMapper.php
I think I should make some abstract methods like add, delete, and insert. But since the arguments for them are objects of different classes for different implementations, I couldn't find a way.
abstract class CommonMapper
{
/**
* @var array contain error code, message etc
*/
private $error = array();
/**
* @var Database class object. Contain PDO connection.
*/
private $dbObj;
/**
* constructor initialize $dbObj with connection.
*/
public function __construct($dbObj)
{
$this->dbObj = $dbObj;
}
/**
* runQuery function
* This function is for select, delete query.
* @param string $query
* @param array $values
* @return mixed For select - return in array containing rows
* For delete - return number of affected rows
* return false in case of empty result
*/
protected function runQuery($query, $values)
{
$result = $this->dbObj->prepareAndExecute($query, $values);
if (!$result && !empty($this->dbObj->getError())) {
$this->error = $this->dbObj->getError();
}
return $result;
}
/**
* runNonQuery function
* This function is for insert query.
* @param string $query
* @param array $values
* @return mixed For select - return last insert id
* return false in case of empty result
*/
protected function runNonQuery($query, $values)
{
$result = $this->dbObj->prepareAndExecute($query, $values);
if (!$result && !empty($this->dbObj->getError())) {
$this->error = $this->dbObj->getError();
}
return $this->dbObj->lastInsertId();
}
/**
* @return array return the error if registered during operation
*/
public function getError()
{
return $this->error;
}
}
UserEducationMapper.php
There are more classes with almost the same functionality, like UserJobMapper
, MemberEducationMapper
, MemberJobMapper
etc.
class UserEducationMapper extends CommonMapper
{
/**
* @param Database $dbObj Object of Database class to perform CRUD operation
*/
public function __construct(Database $dbObj)
{
parent::__construct($dbObj);
}
/**
* addEducation function
* First add column in mat_education then add a column in mat_user_education which
* is relation between mat_education and mat_user
* @param MatEducation $education object of MatEducation class
* @return boolean true if education inserted else false
*/
public function addEducation(MatEducation $education)
{
$set = "";
$values = array();
if ($education->getDegree()) {
$degree = $education->getDegree();
$set .= ":degree, ";
$values['degree'] = $degree;
}
if ($education->getInstitute()) {
$institute = $education->getInstitute();
$set .= ":institute, ";
$values['institute'] = $institute;
}
$set=rtrim($set,", ");
$query = "insert into mat_education (edu_degree, edu_institute) values($set)";
// last insert id. we need it to add row in Mat_user_education
$eid = parent::runNonQuery($query, $values);
$values = array("uid"=>$education->getId(), "eid"=>$eid);
$query = "insert into mat_user_education (user_id, edu_id) values (:uid, :eid)";
return parent::runNonQuery($query, $values);
}
/**
* @param MatEducation $education object of MatEducationMapper class having userId already set
* @return boolean true if information is retrieved else false
*/
public function getEducation(MatEducation $education)
{
$columns = "mat_education.edu_id, mat_education.edu_degree, mat_education.edu_institute";
$id = $education->getId();
$values = array("id"=>$id);
$query = "SELECT $columns FROM mat_user_education
inner join mat_user on mat_user.user_id = mat_user_education.user_id
left join mat_education on mat_education.edu_id = mat_user_education.edu_id
WHERE mat_user_education.user_id = :id";
// return array of rows.
$result = parent::runQuery($query,$values);
// return array of MatEducation Objects.
return $this->loadEducation($education, $result);
}
/**
* @param MatEducation $education object containing userId only.
* we user it to access arrayToObject method since i don't want
* want to create new object using 'new' keyword
* @param array $result array of row from mat_education table
* @return array array of MatEducation objects
*/
private function loadEducation(MatEducation $education, $result)
{
$objArray = array();
for ($i=0; $i<count($result); $i++) {
$objArray[$i] = $education->arrayToObject($result[$i]);
}
return $objArray;
}
/**
* removeEducation
* Function works in two steps first delete the mat_education row and then delete
* mat_user_education row.
* @param MatEducation $education object containing id to be deleted
* @return boolean true if education removed successfully
*/
public function removeEducation(MatEducation $education)
{
$eid = $education->getEduId();
$uid = $education->getId();
$queryRelation = "DELETE FROM mat_user_education WHERE user_id = :uid AND edu_id = :eid";
$valuesRelation = array("uid"=>$uid, "eid"=>$eid);
$queryEducation = "DELETE FROM mat_education WHERE edu_id = :eid";
$valueEducation = array("eid"=>$eid);
parent::runQuery($queryRelation, $valuesRelation);
parent::runQuery($queryEducation, $valueEducation);
return true;
}
}
profile.php
//----------- delete education details-----------------
if (isset($_GET['eidUser']) && !empty($_GET['eidUser'])) {
$eduId = $_GET['eidUser'];
$userId = $_SESSION['user_id'];
$education = new MatEducation();
$education->setEduId($eduId);
$education->setId($userId);
$dbObj = new Database();
$obj = new UserEducationMapper($dbObj);
if ($obj->removeEducation($education)) {
if (!empty($obj->getError())) {
var_dump($obj->getError());
}
}
}
//----------- insert education details-----------------
if (isset($_GET['degree']) || isset($_GET['institute'])) {
$education = new MatEducation();
if (!empty($_SESSION['user_id'])) {
$userId = $_SESSION['user_id'];
$education->setId($userId);
}
if (!empty($_GET['degree'])) {
$degree = $_GET['degree'];
$education->setDegree($degree);
}
if (!empty($_GET['institute'])) {
$institute = $_GET['institute'];
$education->setInstitute($institute);
}
$dbObj = new Database();
$obj = new UserEducationMapper($dbObj);
if (!$obj->addEducation($education)) {
if (!empty($obj->getError())) {
var_dump($obj->getError());
}
}
}
//----------- retrieve education details for user----------
$education = new MatEducation();
if (!empty($_SESSION['user_id'])) {
$userId = $_SESSION['user_id'];
$education->setId($userId);
}
$dbObj = new Database();
$obj = new UserEducationMapper($dbObj);
if (!$userEducationArray = $obj->getEducation($education)) {
if (!empty($obj->getError())) {
var_dump($obj->getError());
}
}
//--------------------------------------------
if (!empty($userEducationArray)){
foreach ($userEducationArray as $education) {
echo "Degree :: " . $education->getDegree() .
" || Institute :: " . $education->getInstitute() .
"<a href='./request/user_education.php?eidUser=" . $education->getEduId() .
"'>Delete</a><br/>";
}
}
I studied some of blogs and code reviews and decided to follow proper oop techniques and concepts. I tried to follow PSR standards and some SOLID concepts (as far as I could understand them). I tried to follow Data Mapper pattern so here MatEducation.php works as ORM for mat_education.
I have read about dependency injection and what I could understand was a class should not create an object of another class in it but if it try to access the services of other class the object should be given to it (correct me). I am more interested in design pattern and conceptual understanding of problem solving (error handling is not done properly in this code but that's ok for now).
I appreciate any suggestion, criticism, links to some good blogs or anything further reading.