Mysql.class.php
class M{
private static $_conn; //建立连接句柄
private $_table; //表名
private $_prefix; //表前缀
//构造方法
public function __construct($table="",$db_config=""){
if(empty($db_config)){
$db_config=require("db_config.php");
}
$this->_conn($db_config);//数据库的实例
$this->_table=$db_config['table_prefix'].strtolower($table);
}
//数据库连接
private function _conn($db_config){
//如果连接存在，就不要重复实例化数据库连接。
if(!(is_object(self::$_conn) && self::$_conn instanceof Mysqli)){
self::$_conn=new mysqli($db_config['host'],$db_config['username'],$db_config['password']);
if(self::$_conn->connect_errno){
die("数据库连接失败".self::$_conn->error);
}
}
//选择数据库
self::$_conn->select_db($db_config['db_name']);
self::$_conn->query("SET NAMES ".$db_config['charset']);
}
//开启事务
public function start_transaction(){
self::$_conn->autocommit(false);
}
//提交事务
public function commit(){
self::$_conn->commit();
}
//回滚事务
public function rollback(){
self::$_conn->rollback();
}
//检测运行是否有问题
public function has_errno(){
return self::$_conn->errno;
}
public function end_transaction(){
self::$_conn->autocommit(true);
}
//更新
public function update($_data,$where){
//查找两个数组的交集
$_data=$this->filterData(self::getField(),$_data);
$values="";
foreach($_data as $key=>$val){
$values.="{$key}='{$val}',";
}
$values=mb_substr($values,0,-1,"utf-8");
$_where="";
if(is_array($where)){
foreach($where as $k=>$v){
$_where.=" {$k}='$v' and";
}
$_where=mb_substr($_where,0,-3,"utf-8");
}else{
$_where=$where;
}
$sql="UPDATE {$this->_table} SET {$values} WHERE {$_where}";
$res=self::$_conn->query($sql);
if($res){
return self::$_conn->affected_rows;
}else{
return false;
}
}
//查询
public function select($where="",$field="*",$limit="0,10"){
if(empty($where)){
$sql="SELECT {$field} FROM {$this->_table} LIMIT {$limit}";
}else{
$sql="SELECT {$field} FROM {$this->_table} WHERE {$where} LIMIT {$limit}";
}
$_data=array();
$res=self::$_conn->query($sql);
if($res){
//var_dump($res->fetch_object());
while($row=$res->fetch_object()){
$_data[]=$row;
}
}
return $_data;
}
public function insert($_data){
//判断是不是数组 是不是键值对
if(is_array($_data)&& sizeof(array_keys($_data))>0){
if(!isset($_data['posttime'])){
$_data['posttime']=time();
}
//查找两个数组的交集
$_data=$this->filterData(self::getField(),$_data);
$fields=array();
$values="";
foreach($_data as $key=>$val){
$fields[]=$key;
$values.="'{$val}',";
}
$values=mb_substr($values,0,-1,"utf-8");
$sql="INSERT INTO {$this->_table}(".implode(",",$fields).")VALUES({$values})";
$res=self::$_conn->query($sql);
if($res){
return self::$_conn->insert_id;
}else{
return false;
}
}else{
die("插入数据失败，请重试");
}
}
//获取表的结构
public function getField(){
$sql="SELECT * FROM {$this->_table} LIMIT 1";
$res=self::$_conn->query($sql);
$fields=array();
$_data=$res->fetch_fields();
foreach($_data as $val){
$fields[$val->name]=$val->name;
}
/**while(){
$fields[]=$row->name;
}**/
return $fields;
}
//获取表详细信息
public function getTableInfo(){
$sql="SHOW TABLE STATUS LIKE '{$this->_table}'";
$res=self::$_conn->query($sql);
return $res->fetch_object();
}
public function showtables(){
$sql="show tables";
$res=self::$_conn->query($sql);
$_data=array();
$res=self::$_conn->query($sql);
if($res){
while($row=$res->fetch_object()){
$_data[]=$row;
}
}
return $_data;
}
//过滤_data
private function filterData($_dataField,$_data){
foreach($_data as $key=>$val){
if(!array_key_exists($key,$_dataField)){
unset($_data[$key]);
}
}
return $_data;
}
}
//定义一个大M的方法，实例mysql类
function M($table="",$db_config=""){
return new M($table,$db_config);
}CREATE TABLE IF NOT EXISTS `web_student` ( `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '//ID', `username` varchar(30) NOT NULL COMMENT '//姓名', `age` int(2) DEFAULT '0' COMMENT '//年龄', `posttime` int(10) DEFAULT '0' COMMENT '//提交时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ; -- -- 转存表中的数据 `web_student` -- INSERT INTO `web_student` (`id`, `username`, `age`, `posttime`) VALUES (1, 'd3aAB6', 65, 1478144500), (2, 'lTaQ5h', 55, 1478144505), (3, 'uBFpCW', 40, 1478144509), (4, 'KeELzx', 42, 1478144534), (5, '1uHRl5', 39, 1478158277);
事务操作：
header("Content-type:text/html;charset=utf-8");
include("./Mysql.class.php");
// 开启事务
M()->start_transaction();
M("student")->update(array("age"=>55),array("id"=>2));
M("student")->update(array("age"=>65),array("id"=>1));
if(M()->has_errno()){
M()->rollback();
}else{
M()->commit();
}写入数据：
writesql.php
header("Content-type:text/html;charset=utf-8");
include("./Mysql.class.php");
$_str="1234567890abcdefghklmgopqrstuvwxyzABCDEFGHKLMGOPQRSTUVWXYZ";
$_data=array("username"=>substr(str_shuffle($_str),rand(1,10),6),"age"=>rand(15,45),"uu"=>'asd');
$res=M("student")->insert($_data);
var_dump($res);读取数据：
readsql.php
header("Content-type:text/html;charset=utf-8");
include("./Mysql.class.php");
$res=M("student")->select();
echo "<pre>";
print_r($res);
echo "</pre>";
//$tables=M()->showtables();
//print_r($tables);
$res=M("student")->getTableInfo();
//$_data=array("username"=>'jiangliuxia',"age"=>22);
//$res=M("student")->insert($_data);
//var_dump($res);