php基础之提取的speedPH框架的数据操作类备用

用框架的话肯定照着文档各种爽，但是脱离了框架的加持，发现操作个数据库都挺麻烦的，网上看到的各种数据库封装类，都不咋地。

于是想起以前看到的一个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");  // 没有问题，那么事务提交。
}