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 來查看一下網頁！
By 於
標籤： ,