Organization of work with DataBase

Question

I have some expierence in PHP, but have no one in application architecture
Now I want to orginize my own "bicycle". It's something not useful, maybe mini-framework or mini-application, I want get some exp here.

I need now to write classes for work with database and classese for entities (one of them isUser)
I have following code for database (some cheks and method are omitted to minify this question):

namespace DataBase;
class DataBase {
    /**
     *
     * @var \PDO $pdo
     */
    public $pdo;
    public function __construct($host, $dbname, $username, $password=''){

        $this->pdo = new \PDO('mysql:host='.$host.';dbname='.$dbname, $username, $password,
            array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

    }
    /**
     *
     * @param string $statement
     * @return Statement
     */
    public function prepare($statement){
        return new Statement($this->pdo->prepare($statement));
    }
}


namespace DataBase;

class Statement {
    private $stmt;

    public function __construct(\PDOStatement $stmt) {
        $this->stmt = $stmt;
    }

    public function query() {
    try {
        $this->stmt->execute();
        return $this; //for chaining
    }

    public function bind($key, $value) {
        $this->stmt->bindValue($key, $value, $this->typeof($value));
        return $this; //for chaining
    }
        //some methods for fetching data(works with fetch,fetchAll, fetchColumn and different PDO::FETCH_ methods

    public function fetchUpdate($obj) {
        $this->stmt->setFetchMode(\PDO::FETCH_INTO, $obj);
        $this->stmt->fetch();
    }

    public function fetchRow() {
        return $this->stmt->fetch(\PDO::FETCH_OBJ);
    }
    public function fetchRowClass($class) {
        return $this->stmt->fetchObject($class);
    }
}

And Some dummy for User class

<?php

/**
 * Description of User
 *
 * @author riad
 */
class User {
    private $id;

    private $name = null;

    private $status = null;

    private $hasInfo = false;

    private static $cache=array();
    public function __construct() {

    }
    public function getId() {
        return $this->id;
    }
    public function getName() {
        if(!$this->hasInfo)
            $this->getInfo ();
        return $this->name;
    }
    public function isAuthorized(){
        return $this->status!="noauth";
    }
    public static function createById($id) {
        // I want this method to cerate user from id, then get info only I will use it after that
        if(array_key_exists($id,self::$cache)){
            return self::$cache[$id];
        }
        $user = new User;
        $user->id = $id;
        return $user;
    }
    private function getInfo(){
        try{
            \FrontController::getInstance()->getDB()->
                prepare('SELECT * FROM `users` WHERE `id`=:id')->
                bind('id', $this->id)->query()->fetchUpdate($this);
            $this->hasInfo = true;
        }
        catch(\DataBase\NotFoundException $dbe){
            $this->status = "deleted";
        }
    }
    public static function createFromRequest($request){
        $user = new User;
        try{
            //try get data from cookie
            \FrontController::getInstance()->getDB()->
                prepare('SELECT * FROM `users` WHERE `session` = :session AND `id`= :id')->
                bind('id', $id)->bind('session',$session)->query()->
                fetchUpdate($user);
        }
        catch(... $e){
            $user->status = "noauth";
            $user->id = 0;
            // make it unregged
        }
        return $user;
    }
}

?>

---

I have some problems with it.

• I don't want set properties from database, that are not listed in props of class list(is not so important, of course). I know that I can use

  public function __call($name,$value){
  //do nothing;
  }

• I want to mkae this props private, but want also use $stmt->fetchUpdate($obj) I know I can use

  public function __call($name,$value){
  $this->$name=$value;
  }

  , but it's as declare props public and it is on the road with first point I can also use

  public function __call($name,$value){
  if($name=='id'){
      $this->id=$value;
  }
  else if($name=='status'){
      $this->status=$value;
  }
  }

  But it's not comfortable to write it for every entity class and not save as from publicity of this methods
• I want to set $this->hasInfo to true when I get this class from database. I know I can change my Database class to always set some variable to true when by default it's false. But it seems to be not elegant.
• I want to update cache when I set id (It maybe used as previos point)
• Is it possible to avoid fetchRowClass write direct to props and use setter as with fetchUpdate? Or maybe allow fetchUpdate direct access?

I know I write a lot of code self but I want your opinion:

• What should I improve?
• What are other/the best possible solution for problems from previos list?

Hope, It's not so hard to read and understand.
Glad to see any suggestions
With regards Alex

Answer 1

Few tips: [based on my own experience and frameworks i already used]

Basically what you should/want/might do is to create a SuperClass for all the clases in you model. This class will contain the reference to the Database Instance, and it will have all the common methods for your model, i.e. getById($id), getAll(),getPaginated(), etc.

The other goal of this SuperClass is to map the results from the database into Instances of your Model's Classes. So in the end, your user class would have only properties, accessors and methods that are specific to the class, like special queries or something like that.

Here's an example of what this could look like:

Class Model{
    protected function getById($_id){
        $_table = get_class($this);
        $anonymous = $this->_getObjectById($_table,$_id); //this method will execute a query (unsing PDO) and return a StdClass object with the results
        $this->mapTable($anonymous,$_table); //this method will take the StdClass instance and will transform it into a $_table Instance
    }

    private function mapTable($stdInstance,$model_name){
        foreach($stdInstance as $key => $value){
            try{
                if(property_exists($this,$key)){
                    $this->$key = $value; //you could declare the model's properties     as protected... or you could create accessors and call them here
                }
            } catch(Exception $ex) {
                /* something went wrong o_O */
            }
     }

Class User extends Model{
     protected $id;
     protected $name;
     .....
}

Class Controller{
    public function index(){
         $user = new User();
         $user->getById($_GET['id']);
         print_r($user);
         //now you can pass the $user object to the View to display it
    }
}

in a few words... the Model class is a very small ORM. You could try to create your own ORM, (like i did) but you'll face a lot of problems when trying to map the relations between objects: Nx1,1xN,NxN,1x1, inheritance, "deeper relations" and the n+1 problem. You'll also need to, somehow, define the model structure so your ORM could understand it, maybe using YAML/XML files or reading the structure directly from the table structure of your databe, or having a naming convention in your properties...

its a really interesting field :)

Hope this helps and Good Luck