2021年6月21日 星期一

使用 PHP 實作 MVC 框架(三)

設定目標:
  • 實作自己的 DB 模組,加入自己實作的 MVC 框架!
  • 接續上一篇文章進行 DB 模組的實作!

實作 DB 模組 !
  1. 撰寫 DB 連線核心模組,放置於 config 目錄下,例:dbconnect.php
    <?php
    //DB 連線用模組
    namespace config;
    
    use PDO;
    use PDOexception;
    
    class DBconnect {
        private static $pdo = null;
        
        public static function pdo(){
            if (self::$pdo !== null){
                return self::$pdo;
            }
            try {
                $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8', DB_HOST, DB_NAME);
                $option = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC);
    
                return self::$pdo = new PDO($dsn, DB_USER, DB_PASSWORD, $option);
            } catch (PDOException $e) {
                exit($e->getMessage());
            }
        }
    }
    
  2. 撰寫 DB 基本模組,放置於 app/DBs 目錄下,例:DB.php
    <?php
    //建立一個通用的 DB 模組
    namespace App\DBs;
    
    use config\DBconnect;
    use PDOStatement;
    
    class DB extends DBconnect {
    
        protected $table;
        protected $primary = 'id';
        private $filter = '';
        private $param = array();
    
        public function __construct($table){
            $this->table = $table;
            //$this->filter = $filter;
        }
    
        // 條件查詢 where ! 
        // 輸入格式 where(['id = :id'], [':id' => $id]])
        public function where($where = array(), $param = array()){
            if (isset($where)) {
                $this->fileter .= ' WHERE ';
                $this->filter .= \implode(' ', $where);
                $this->param = $param;
            }
    
            return $this;
        }
    
        // 排序方式,由使用者自行輸入
        // 例: order(['id DESC', 'name ASC',...])
        public function order($order = array()){
            if (isset($order)){
                $this->filter .= ' ORDER BY ';
                $this->filter .= \implode(',', $order);
            }
    
            return $this;
        }
        //新增資料
        public function add($data){
            $sql = \sprintf("insert into `%s` %s",$this->table, $this->formatInsert($data));
            $sth = DBconnect::pdo()->prepare($sql);
            $sth = $this->formatParam($sth, $data);
            $sth = $this->formatParam($sth, $this->param);
            $th->execute();
    
            return $sth->rowCount();
        }
        //修改資料
        public function update($data){
            $sql = \sprintf("update `%s` set %s %s", $this->table, $this->formatUpdate($data), $this->filter);
            $sth = DBconnect::pdo()->prepare($sql);
            $sth = $this->formatParam($sth, $data);
            $sth = $this->formatParam($sth, $this->param);
            $th->execute();
    
            return $sth->rowCount();
        }
    
        //一次取回所有資料
        public function fetchAll(){
            $sql = \sprintf("select * from `%s` %s", $this->table, $this->filter);
            $sth = DBconnect::pdo()->prepare($sql);
            //var_dump(($sth));
            $sth = $this->formatParam($sth, $this->param);
            $sth->execute();
    
            return $sth->fetchAll();
        }
    
        //一次只取回一筆資料
        public function fetch(){
            $sql = \sprintf("select * from `%s` %s", $this->table, $this->filter);
            $sth = DBconnect::pdo()->prepare($sql);
            $sth = $this->formatParam($sth, $this->param);
            $sth->execute();
    
            return $sth->fetch();
        }
    
        //刪除資料,以 id 欄位為主要刪除方式,較為方便
        public function delete($id){
            $sql = \sprintf("delete from `%s` where `%s` = :%s", $this->table, $this->primary, $this->primary);
            $sth = DBconnect::pdo()->prepare($sql);
            $sth = $this->formatParam($sth, [$this->primary => $id]);
            $sth->execute();
    
            return $sth->rowCount();
        }
        //格式化資料
        private function formatParam(PDOStatement $sth, $params = array()){
            foreach ($params as $param => &$value) {
                $param = is_int($param) ? $param + 1 : ':' . ltrim($param, ':');
                $sth->bindParam($param, $value);
            }
    
            return $sth;
        }
    
        //轉換成INSERT SQL 語法
        private function formatInsert($data){
            $fields = array();
            $names = array();
            foreach ($data as $key => $value) {
                $fields[] = \sprintf("`%s`", $key);
                $names[] = \sprintf(":%s", $key);
            }
    
            $field = implode(',' ,$fields);
            $name = implode(',', $names);
    
            return \sprintf("(%s) values (%s)", $field, $name);
        }
    
        //轉換成UPDATE SQL 語法
        private function formatUpdate($data){
            $fields = array();
            foreach ($data as $key => $value) {
                $fields[] = \sprintf("`%s` = :%s", $key, $key);
            }
            return implode(',', $fields);
        }
    }
    
  3. 撰寫 LoginController 控制器模組,放置於 app\Controllers 目錄下,使用 DB 模組,進行資料庫存取,例:LoginController.php
    <?php
    namespace App\Controllers;
    
    use kernel\Controller;
    use App\Models\indexModel;
    use App\Views\indexView;
    use App\DBs\DB;
    
    class LoginController extends Controller {
       
        protected $paras;
    
        public function __construct($parameter){
           parent::__construct($parameter);
        }
    
        public function getUri(){
            $this->paras = parent::getUri();
            return $this->paras;
        }
        
        public function run(){
            $db = new DB("students");
            var_dump($db->fetchAll());
        }
    }
    
  4. 在 MySQL 資料庫內,請增一個 contact 資料庫,並在資料庫內,新增一個 student 表格:
    DROP TABLE IF EXISTS `students`;
    CREATE TABLE IF NOT EXISTS `students` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `studentid` varchar(100) NOT NULL,
      `name` varchar(100) DEFAULT NULL,
      `email` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `studentID` (`studentid`)
    ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    
    INSERT INTO `students` (`id`, `studentid`, `name`, `email`) VALUES
    (1, 'ABC123', 'hellokitty', 'kitty@hello.com');
    COMMIT;
    
  5. 使用 http://hellomvc/login 來查看一下網頁!