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);