用框架的话肯定照着文档各种爽，但是脱离了框架的加持，发现操作个数据库都挺麻烦的，网上看到的各种数据库封装类，都不咋地。
于是想起以前看到的一个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"); // 没有问题，那么事务提交。 | |
| } |
数据库的更多操作说明可以查看文档，链接如下：