- 實作自己的 DB 模組,加入自己實作的 MVC 框架!
- 接續上一篇文章進行 DB 模組的實作!
實作 DB 模組 !
- 撰寫 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()); } } }
- 撰寫 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); } }
- 撰寫 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()); } }
- 在 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;
- 使用 http://hellomvc/login 來查看一下網頁!