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