<?php
class DB_mysqli {
protected $mysqli; //mysqli实例对象
public $sql; //sql语句
protected $rs; //结果集
protected $query_num = 0; //执行次数
protected $fetch_mode = MYSQLI_ASSOC; //获取模式
protected $cache; //缓存类对象
protected $reload = false; //是否重新载入
protected $cache_mark = true; //缓存标记
//构造函数:主要用来返回一个mysqli对象
public function __construct($dbhost, $dbuser, $dbpass, $dbname, $dbport) {
$this->mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport);
if(mysqli_connect_errno()) {
$this->mysqli = false;
echo '<h2>'.mysqli_connect_error().'</h2>';
die();
} else {
$this->mysqli->set_charset("utf8");
}
}
//缓存类对象:文件缓存、memcache键值对缓存
public function cache_obj($cache) {
$this->cache = $cache;
}
//析构函数:主要用来释放结果集和关闭数据库连接
public function __destruct() {
$this->free();
$this->close();
}
//释放结果集所占资源
protected function free() {
@$this->rs->free();
}
//关闭数据库连接
protected function close() {
$this->mysqli->close();
}
//获取结果集
protected function fetch() {
return $this->rs->fetch_array($this->fetch_mode);
}
//获取查询的sql语句
protected function get_query_sql($sql, $limit = null) {
if (@preg_match("/[0-9]+(,[ ]?[0-9]+)?/is", $limit) && !preg_match("/ LIMIT [0-9]+(,[ ]?[0-9]+)?$/is", $sql)) {
$sql .= " LIMIT " . $limit;
}
return $sql;
}
//从缓存中获取数据
protected function get_cache($sql,$method) {
$cache_file = md5($sql.$method);
$res = $this->cache->get($cache_file);
if(!$res) { //如果缓存文件过期或不存在的话,返回false;如果缓存文件存在且未过期的话,则返回缓存数据
$res = $this->$method($sql); //先从缓存中取数据,如果缓存中没数据,则从数据库中取数据
if($res && $this->cache_mark && !$this->reload) {
$this->cache->set($cache_file, $res);//如果缓存文件过期或不存在的话,将重新将从数据库中查询的数据放入缓存文件
}
}
return $res;
}
//获取查询次数
public function query_num() {
return $this->query_num;
}
//执行sql语句查询
public function query($sql, $limit = null) {
$sql = $this->get_query_sql($sql, $limit);
$this->sql[] = $sql;
$this->rs = $this->mysqli->query($sql);
if (!$this->rs) {
echo "<p>error: ".$this->mysqli->error."</p>";
echo "<p>sql: ".$sql."</p>";
die();
} else {
$this->query_num++;
return $this->rs;
}
}
//返回单条记录的单个字段值
public function get_one($sql) {
$this->query($sql, 1);
$this->fetch_mode = MYSQLI_NUM;
$row = $this->fetch();
$this->free();
return $row[0];
}
//缓存单个字段
public function cache_one($sql, $reload = false) {
$this->reload = $reload;
$sql = $this->get_query_sql($sql, 1);
return $this->get_cache($sql, 'get_one');
}
//获取单条记录
public function get_row($sql, $fetch_mode = MYSQLI_ASSOC) {
$this->query($sql, 1);
$this->fetch_mode = $fetch_mode;
$row = $this->fetch();
$this->free();
return $row;
}
//缓存行
public function cache_row($sql, $reload = false) {
$this->reload = $reload;
$sql = $this->get_query_sql($sql, 1);
return $this->get_cache($sql, 'get_row');
}
//返回所有的结果集
public function get_all($sql, $limit = null, $fetch_mode = MYSQLI_ASSOC) {
$this->query($sql, $limit);
$all_rows = array();
$this->fetch_mode = $fetch_mode;
while($rows = $this->fetch()) {
$all_rows[] = $rows;
}
$this->free();
return $all_rows;
}
//缓存all
public function cache_all($sql, $reload = false, $limit = null) {
$this->reload = $reload;
$sql = $this->get_query_sql($sql, $limit);
return $this->get_cache($sql, 'get_all');
}
//返回前一次mysql操作所影响的记录行数
public function affected_rows() {
return $this->mysqli->affected_rows;
}
/**
* 获取插入语句
*
* @param string $tbl_name 表名
* @param array $info 数据
*/
public function get_insert_db_sql($tbl_name,$info)
{
//首先判断是否为数组,再判断数组是否为空
if(is_array($info)&&!empty($info))
{
$i = 0;
foreach($info as $key=>$val)
{
$fields[$i] = $key; //将所有的键名放到一个$fields[]数组中
$values[$i] = $val; //将所有的值放到一个$values[]数组中
$i++;
}
$s_fields = "(".implode(",",$fields).")";
$s_values = "('".implode("','",$values)."')";
$sql = "INSERT INTO
$tbl_name
$s_fields
VALUES
$s_values";
Return $sql;
}
else
{
Return false;
}
}
/**
* 获取替换语句:replace into是insert into的增强版
* 区别:replace into跟insert功能类似,不同点在于:replace into 首先尝试插入数据到表中,如果发现表中
已经有此行数据(根据主键或唯一索引判断),则先删除此行数据,然后插入新的数据,否则直接插入新数据
* @param string $tbl_name 表名
* @param array $info 数据
*/
public function get_replace_db_sql($tbl_name,$info)
{
if(is_array($info)&&!empty($info))
{
$i = 0;
foreach($info as $key=>$val)
{
$fields[$i] = $key;
$values[$i] = $val;
$i++;
}
$s_fields = "(".implode(",",$fields).")";
$s_values = "('".implode("','",$values)."')";
$sql = "REPLACE INTO
$tbl_name
$s_fields
VALUES
$s_values";
Return $sql;
}
else
{
Return false;
}
}
/**
* 获取更新SQL语句
*
* @param string $tbl_name 表名
* @param array $info 数据
* @param array $condition 条件
*/
public function get_update_db_sql($tbl_name,$info,$condition)
{
$i = 0;
$data = '';
if(is_array($info)&&!empty($info))
{
foreach( $info as $key=>$val )
{
if(isset($val))
{
$val = $val;
if($i==0&&$val!==null)
{
$data = $key."='".$val."'"; //第一次:如,update 表名 set username='admin'
}
else
{
$data .= ",".$key." = '".$val."'";//非第一次:如, ,password='123'
}
$i++;
}
}
$sql = "UPDATE ".$tbl_name." SET ".$data." WHERE ".$condition;
return $sql;
}
else
{
Return false;
}
}
/**
* 取得数据库最后一个插入ID
*
* @return int
*/
public function last_id() {
return mysqli_insert_id($this->mysqli);
}
public function real_get($sql, $fetch_mode = MYSQLI_ASSOC) {
$this->query($sql);
$this->fetch_mode = $fetch_mode;
$row = $this->fetch();
$this->free();
return $row;
}
}
TEST
// DB_PDO CRUD
$pdo = DB_PDO::getInstance();
echo '<br>--PDO getRow--<br>';
$sql = 'select * from test where id > 2';
var_dump( $pdo->getRow($sql));
echo $pdo->getRow($sql)['name'];
echo $pdo->getRow($sql, PDO::FETCH_NUM)[1];
echo '<br>--PDO getAll--<br>';
$sql = 'select * from test';
echo $pdo->getAll($sql)[1]['name'];
var_dump($pdo->getAll($sql));
echo $pdo->getAll($sql)[1]['name'];
echo '<br>--PDO insert--<br>';
echo $pdo->insert('test', array('name'=>'aa11', 'age'=>'25'));
echo '<br>--PDO delete--<br>';
echo $pdo->delete('test', "name = 'aa'");
echo '<br>--PDO update--<br>';
echo $pdo->update('test', array("name"=>'一哥111', "age"=>'24'), "id=481");
// 事务
try {
$pdo->beginTransaction();
$pdo->insert("test", array("name"=>"王六", "age"=>26));
$pdo->insert("test", array("name"=>"王七", "age"=>27));
$pdo->commit();
echo '提交ok';
}catch(PDOException $e){
$pdo->rollBack();
echo "Failed! " . $e->getMessage();
}
// 预编译
echo "<br>--fetch--<br>";
$sql = "select * from test where id >:id and age =:age";
$param = array(":id"=>2, ":age"=>26);
$test = $pdo->fetch($sql, $param);
var_dump($test);
echo "<br>--fetchAll--<br>";
$sql = "select * from test where id >:id";
$param = array(":id"=>2);
var_dump($pdo->fetchAll($sql, $param));
echo "<br>--exec--<br>";
$sql = "insert into test(name, age) values(:name, :age)";
$param = array(":name"=>"小二", ":age"=>28);
$sql = "update test set name=:name where id=:id";
$param = array(":name"=>"李四光2", ":id"=>"440");
$sql = "delete from test where id > :id";
$param = array(":id"=>"2");
echo $pdo->exec($sql,$param);
// DB_MySQLi CRUD
$mysqli = DB_MySQLi::getInstance();
echo '<br>--getLastInsID--<br>';
echo $mysqli->getLastInsID();
echo '<br>--MySQLi getRow--<br>';
$sql = 'select * from test where id = 2';
var_dump( $mysqli->getRow($sql));
echo $mysqli->getRow($sql)['name'];
echo '<br>--MySQLi getAll--<br>';
$sql = 'select * from test where 1 = 1';
var_dump( $mysqli->getAll($sql));
echo $mysqli->getAll($sql)[0]['name'];
echo '<br>--MySQLi insert--<br>';
echo $mysqli->insert('test', array('name'=>'bb', 'age'=>'25'));
echo "<br>--MySQLi delete--<br>";
echo $mysqli->delete("test", "name = 'aa'");
echo "<br>--MySQLi update--<br>";
echo $mysqli->update('test', array('name'=>"小五111"), 'id=370');
// 事务
echo "<br>--insert--<br>";
$mysqli->beginTransaction();
$res1 = $mysqli->insert('test', array('name'=>'rose', 'age'=>'2222'));
$res2 = $mysqli->insert('test', array('name'=>'bbbb', 'age'=>'1111'));
if(!$res1 || !$res2) {
echo "回滚。。。";
$mysqli->rollBack();
}else{
echo '提交。。。';
$mysqli->commit();
}
// 预编译
echo "<br>--fetch--<br>";
$sql = "select * from test where 1 = ?";
$param = array('i', '1');
var_dump($mysqli->fetch($sql, $param));
echo "<br>--fetchALL--<br>";
$sql = "select * from test where 1 = ?";
$param = array('i', '1');
var_dump($mysqli->fetchALL($sql, $param));
// 其它操作
function inject_check($sql_str) { //防止注入
$check = eregi('select|insert|update|delete|\'|\/\*|\*|\.\.\/|\.\/|union|into|load_file|outfile', $sql_str);
if ($check) {
echo "输入非法注入内容!";
exit ();
} else {
return $sql_str;
}
}
echo inject_check("delete");