php基礎之提取的speedPH框架的資料操作類備用

木大大 發表於 2021-05-30

用框架的話肯定照著文件各種爽,但是脫離了框架的加持,發現操作個資料庫都挺麻煩的,網上看到的各種資料庫封裝類,都不咋地。

於是想起以前看到的一個php微型框架 SpeedPHP,對於剛入門的朋友來說,學習一下這個框架再好不過,原理超級簡單。

提取了SpeedPHP的Model類,小改一了一下,以備用。此Model封裝了基本的增刪改查操作,複雜SQL給封裝了引數繫結的方法,滿足了所有的業務場景要求了。

感謝SpeedPHP作者。

有興趣的可以自己去官網看看 www.speedphp.com
GitHub github.com/SpeedPHP/speed

$db_config = array(
    'MYSQL_HOST' => 'localhost',
    'MYSQL_PORT' => '3306',
    'MYSQL_USER' => 'root',
    'MYSQL_DB' => 'ad_com',
    'MYSQL_PASS' => 'root',
    'MYSQL_CHARSET' => 'utf8',
);

class Model
{
    public $page;
    public $table_name;

    private $sql = array();

    /**
     * Model constructor.
     * @param null $table_name
     */
    public function __construct($table_name = null)
    {
        global $db_config;

        $this->db_config = $db_config;

        if ($table_name) $this->table_name = $table_name;
    }

    /**
     * 設定資料表
     * @param string $table 資料表名
     * @return $this
     */
    public function setTable($table)
    {
        $this->table_name = $table;
        return $this;
    }

    /**
     * 按條件查詢所有資料
     * @param array $conditions 查詢條件
     * @param string $sort 排序欄位
     * @param string $fields  查詢欄位
     * @param null $limit  獲取條數 可以是陣列也可以是字串 陣列的話則是分頁
     * @return array  返回查詢資料
     */
    public function findAll($conditions = array(), $sort = null, $fields = '*', $limit = null)
    {
        $sort = !empty($sort) ? ' ORDER BY ' . $sort : '';
        $conditions = $this->_where($conditions);

        $sql = ' FROM ' . $this->table_name . $conditions["_where"];
        if (is_array($limit)) {
            $total = $this->query('SELECT COUNT(*) as M_COUNTER ' . $sql, $conditions["_bindParams"]);
            if (!isset($total[0]['M_COUNTER']) || $total[0]['M_COUNTER'] == 0) return array();

            $limit = $limit + array(1, 10, 10); // 陣列+陣列,左邊陣列會覆蓋右邊相同鍵名的值
            $limit = $this->pager($limit[0], $limit[1], $limit[2], $total[0]['M_COUNTER']);
            $limit = empty($limit) ? '' : ' LIMIT ' . $limit['offset'] . ',' . $limit['limit'];
        } else {
            $limit = !empty($limit) ? ' LIMIT ' . $limit : '';
        }
        return $this->query('SELECT ' . $fields . $sql . $sort . $limit, $conditions["_bindParams"]);
    }

    /**
     * 按條件資料查詢單條資料
     * @param array $conditions  查詢條件
     * @param null $sort
     * @param string $fields
     * @return bool|mixed
     */
    public function find($conditions = array(), $sort = null, $fields = '*')
    {
        $res = $this->findAll($conditions, $sort, $fields, 1);

        return !empty($res) ? array_pop($res) : false;
    }

    /**
     * 按條件資料更新
     * @param array $conditions  更新條件
     * @param  array $row 更新值
     * @return mixed  返回查詢的資料
     */
    public function update($conditions, $row)
    {
        $values = array();
        foreach ($row as $k => $v) {
            $values[":M_UPDATE_" . $k] = $v;
            $setstr[] = "`{$k}` = " . ":M_UPDATE_" . $k;
        }
        $conditions = $this->_where($conditions);
        return $this->execute("UPDATE " . $this->table_name . " SET " . implode(', ', $setstr) . $conditions["_where"], $conditions["_bindParams"] + $values);
    }

    /**
     * 按條件資料自增
     * @param $conditions
     * @param $field
     * @param int $optval
     * @return mixed
     */
    public function incr($conditions, $field, $optval = 1)
    {
        $conditions = $this->_where($conditions);
        return $this->execute("UPDATE " . $this->table_name . " SET `{$field}` = `{$field}` + :M_INCR_VAL " . $conditions["_where"], $conditions["_bindParams"] + array(":M_INCR_VAL" => $optval));
    }

    /**
     * 按條件資料自減
     * @param $conditions
     * @param $field
     * @param int $optval
     * @return mixed
     */
    public function decr($conditions, $field, $optval = 1)
    {
        return $this->incr($conditions, $field, -$optval);
    }

    /**
     * 按刪除條件資料
     * @param $conditions
     * @return mixed
     */
    public function delete($conditions)
    {
        $conditions = $this->_where($conditions);
        return $this->execute("DELETE FROM " . $this->table_name . $conditions["_where"], $conditions["_bindParams"]);
    }

    /**
     * 往表中插入資料
     * @param $row
     * @return mixed
     */
    public function create($row)
    {
        $values = array();
        foreach ($row as $k => $v) {
            $keys[] = "`{$k}`";
            $values[":" . $k] = $v;
            $marks[] = ":" . $k;
        }
        $this->execute("INSERT INTO " . $this->table_name . " (" . implode(', ', $keys) . ") VALUES (" . implode(', ', $marks) . ")", $values);
        return $this->dbInstance()->lastInsertId();
    }

    /**
     * 獲取按條件查詢資料的資料量
     * @param  array $conditions 預設值空陣列則獲取所有資料總條數
     * @return int
     */
    public function findCount($conditions=array())
    {
        $conditions = $this->_where($conditions);
        $count = $this->query("SELECT COUNT(*) AS M_COUNTER FROM " . $this->table_name . $conditions["_where"], $conditions["_bindParams"]);
        return isset($count[0]['M_COUNTER']) && $count[0]['M_COUNTER'] ? $count[0]['M_COUNTER'] : 0;
    }

    /**
     * 輸出剛執行的SQL語句
     * @return array
     */
    public function dumpSql()
    {
        return $this->sql;
    }

    /**
     * 根據給定引數生成分頁所需的資料
     * @param int $page  當前頁碼數
     * @param int $pageSize 每頁資料條數 預設一頁10條
     * @param int $scope  顯示的頁碼個數 預設顯示10個頁碼
     * @param int $total 資料總條數  根據 findCount 方法獲取
     * @return array|null
     */
    public function pager($page, $pageSize = 10, $scope = 10, $total)
    {
        $this->page = null;
        if ($total > $pageSize) {
            $total_page = ceil($total / $pageSize);
            $page = min(intval(max($page, 1)), $total_page);
            $this->page = array(
                'total_count' => $total,
                'page_size' => $pageSize,
                'total_page' => $total_page,
                'first_page' => 1,
                'prev_page' => ((1 == $page) ? 1 : ($page - 1)),
                'next_page' => (($page == $total_page) ? $total_page : ($page + 1)),
                'last_page' => $total_page,
                'current_page' => $page,
                'all_pages' => array(),
                'offset' => ($page - 1) * $pageSize,
                'limit' => $pageSize,
            );
            $scope = (int)$scope;
            if ($total_page <= $scope) {
                $this->page['all_pages'] = range(1, $total_page);
            } elseif ($page <= $scope / 2) {
                $this->page['all_pages'] = range(1, $scope);
            } elseif ($page <= $total_page - $scope / 2) {
                $right = $page + (int)($scope / 2);
                $this->page['all_pages'] = range($right - $scope + 1, $right);
            } else {
                $this->page['all_pages'] = range($total_page - $scope + 1, $total_page);
            }
        }
        return $this->page;
    }

    /**
     * 執行SQL語句
     * @param string $sql  SQL語句
     * @param array $params
     * @return mixed
     */
    public function query($sql, $params = array())
    {
        $str = stripos(trim($sql),"select ");

        if($str===false){
            $status = false; // 不是查詢操作,則是返回影響行數
        }else{
            $status = true; // 查詢操作則獲取資料
        }

        return $this->execute($sql, $params,$status);
    }

    /**
     * 執行SQL語句,內部呼叫
     * @param $sql
     * @param array $params
     * @param bool $readonly
     * @return mixed
     */
    public function execute($sql, $params = array(),$readonly=false)
    {
        $this->sql[] = $sql;

        $sth = $this->dbInstance()->prepare($sql);


        if (is_array($params) && !empty($params)) {
            foreach ($params as $k => &$v) {
                if (is_int($v)) {
                    $data_type = PDO::PARAM_INT;
                } elseif (is_bool($v)) {
                    $data_type = PDO::PARAM_BOOL;
                } elseif (is_null($v)) {
                    $data_type = PDO::PARAM_NULL;
                } else {
                    $data_type = PDO::PARAM_STR;
                }
                $sth->bindParam($k, $v, $data_type);
            }
        }

        // 如果有執行結果 判斷是返回執行sql的條數,還是返回查詢的資料
        if($sth->execute())return $readonly ? $sth->fetchAll(PDO::FETCH_ASSOC) : $sth->rowCount();
        $err = $sth->errorInfo();
        die('Database SQL: "' . $sql . '", ErrorInfo: '.$err[2]);
    }

    /**
     * 例項化PDO物件
     * @param $db_config
     * @param $db_config_key
     * @param bool $force_replace
     * @return mixed
     */
    private function dbInstance()
    {
        try {
            if (!class_exists("PDO") || !in_array("mysql", PDO::getAvailableDrivers(), true)) {
                die('Database Err: PDO or PDO_MYSQL doesn\'t exist!');
            }
            return new PDO('mysql:dbname=' . $this->db_config['MYSQL_DB'] . ';host=' . $this->db_config['MYSQL_HOST'] . ';port=' . $this->db_config['MYSQL_PORT'], $this->db_config['MYSQL_USER'], $this->db_config['MYSQL_PASS'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'' . $this->db_config['MYSQL_CHARSET'] . '\''));
        } catch (PDOException $e) {
            die('Database Err: ' . $e->getMessage());
        }

    }

    /**
     * 組裝 查詢條件
     * @param $conditions
     * @return array
     */
    private function _where($conditions)
    {
        $result = array("_where" => " ", "_bindParams" => array());
        if (is_array($conditions) && !empty($conditions)) {
            $fieldss = array();
            $sql = null;
            $join = array();
            if (isset($conditions[0]) && $sql = $conditions[0]) unset($conditions[0]);
            foreach ($conditions as $key => $condition) {
                if (substr($key, 0, 1) != ":") {
                    unset($conditions[$key]);
                    $conditions[":" . $key] = $condition;
                }
                $join[] = "`{$key}` = :{$key}";
            }
            if (!$sql) $sql = join(" AND ", $join);

            $result["_where"] = " WHERE " . $sql;
            $result["_bindParams"] = $conditions;
        }
        return $result;
    }
}

條件查詢引數 $condition

$condition,陣列形式,查詢紀錄的條件。有兩種方式:

直接鍵對值的等於關係的AND條件,如array(“cid”=>12, “score”=>100),那麼指代的查詢是“ WHERE cid = 12 AND score = 100 ”。

另一種是可以表示比等於和AND更為複雜的條件陣列。該陣列的[0]下標項,是查詢的字串條件,但是輸入引數必須是繫結形式的;陣列的其他鍵對值項,都是繫結的引數欄位對應值。

這種型別比較難理解,比如說我們需要模糊查詢文章title帶有“php”的文章,條件是: “WHERE title like ‘%php%’ ”。那麼$condition可以設定成

      $keyword = "php";
      findAll(
          array("title like :word", 
              ":word" => '%'.$keyword.'%'
          )
      );

PHP5.4 起可以使用短陣列定義語法,用 [] 替代 array()。所以5.4之後可以使用更簡潔優雅的方式來寫$condition。

findAll(["title like :word",":word"=>"%".$keyword."%"]); // after 5.4

$condition條件可以解決包括大於小於等於,or條件,like查詢等條件的構造。這裡多舉兩個例子:

假設我們要刪除IP為218.26.35.*網段的紀錄:

        DELETE * FROM records WHERE ip like "218.26.35.%";

        等同於

          $condition = array('ip like :ip', 
              ":ip" => "218.26.35.%"
          );

          $obj->delete($condition);

        OR邏輯複雜條件查詢:

        SELECT * FROM students WHERE score > 90 AND ( classname = 'class1' OR classname = 'class2' );

        等同於

          $condition = array("score > :score AND ( classname = :c1 OR classname = :c2 )", 
              ":score" => 90,
              ":c1"    => "class1",
              ":c2"    => "class2",
          );

          $obj->findAll($condition);

事務支援

支援SQL就能支援資料庫事務,當然資料庫型別需要是innoDB。

$g = new Model("lib_guestbook");
// 開啟事務
$g->execute("START TRANSACTION"); // 或者是$g->execute("BEGIN");
// 這裡是很多的插入或修改操作等,一般來說查詢不需要用事務的。
$result1 = $g->create(xxx);
$result2 = $g->update(xxx);
...
// 這裡判斷操作是否成功,然後回滾或提交事務
    if( false == $result1 || false == $result2 || ... ){ // create、update之類的返回false即是操作失敗,也有可能是欄位錯誤
    $g->execute("ROLLBACK");  // 出現問題,事務回滾
}else{
    $g->execute("COMMIT");  // 沒有問題,那麼事務提交。
}

資料庫的更多操作說明可以檢視文件,連結如下:

資料操作-SQL支援及關聯實現.md

資料操作-分頁.md

資料操作-建立資料模型類.md

資料操作-資料操作教程.md

本作品採用《CC 協議》,轉載必須註明作者和本文連結