php for mysql 操作类
浏览数：56 /
时间：2015年06月12日
1 <?php 2 defined(‘TOKEN‘) or die(‘Hacker intrusion‘); 3 class dbm { 4 private static $_instance = false; //连接实例 5 private $linkid = ‘‘; //当前的连接资源 6 private $dbname = ‘‘; //数据库名 7 private $tbpre = ‘‘; //表前缀 8 public $sql = array(); //所有执行过的sql 9 public $error = ‘‘; //当前执行的sql的错误信息 10 /** 架构函数 11 */ 12 private function __construct() { 13 $this->linkid = @mysql_connect(HOST,UNAME,UPASS) or H::show_msg (‘{"code":"1","msg":"Do not connect database"}‘); 14 $this->dbname = DBNAME; 15 $this->tbpre = TBPRE; 16 $this->select_db(DBNAME); 17 $this->set_charset(CHARSET); 18 } 19 /** 销毁类 估计没什么球用 建议手动销毁 20 */ 21 public function __destory() { 22 //$this->close(); 23 } 24 /** 禁止clone对象 25 */ 26 private function __clone() {} 27 /** 获取数据库单例 28 */ 29 public static function get_db() { 30 if(!self::$_instance) self::$_instance = new self(); 31 return self::$_instance; 32 } 33 /** 设置数据库字符集 34 * @param $charset 字符集 默认 utf8 35 */ 36 public function set_charset($charset=‘utf8‘) { 37 mysql_query("set names " . $charset, $this->linkid); 38 } 39 /** 选择数据库 40 * @param $dbname 数据库名 41 */ 42 public function select_db($dbname) { 43 mysql_select_db($dbname, $this->linkid) or H::show_msg (‘{"code":"1","msg":"Do not open database"}‘); 44 } 45 /** 单表插入 46 * @param $field 存储需要插入的键值对的数组 47 */ 48 public function insert($tbname,$field=array()) { 49 $str_k = ‘‘; 50 $str_v = ‘‘; 51 foreach($field as $k=>$v) { 52 $str_k .= $str_k == ‘‘ ? ‘`‘.trim($k).‘`‘ : ‘,`‘.trim($k).‘`‘; 53 $str_v .= $str_v == ‘‘ ? ‘\‘‘.$this->filter($v).‘\‘‘ : ‘,‘.‘\‘‘.$this->filter($v).‘\‘‘; 54 } 55 $sql = ‘insert into ‘.$this->tbpre.$tbname.‘ (‘; 56 $sql .= $str_k.‘) values (‘.$str_v.‘)‘; 57 return $this->query($sql,‘insert‘); 58 } 59 /** 单表删除 不传递where 则删除整张表 60 */ 61 public function deletes($tbname,$where=‘‘) { //注意传递 limit 1 避免扫描整张表 62 $sql = ‘delete from ‘.$this->tbpre.$tbname.($where==‘‘ ? ‘‘ : " where {$where}"); 63 return $this->query($sql,‘delete‘); 64 } 65 /** 单表更新 支持直接传递更新的字符串 "info_title=‘呵2呵‘,fcolor=‘#666‘" 66 */ 67 public function update($tbname,$field,$where=‘‘) { //注意传递 limit 1 避免扫描整张表 68 $sql = ‘update ‘.$this->tbpre.$tbname.‘ set ‘; 69 if(is_array($field)) { 70 $str_k_v = array(); 71 foreach($field as $k=>$v) { 72 $str_k_v[] = ‘`‘.trim($k).‘`=‘.‘\‘‘.$this->filter($v).‘\‘‘; 73 } 74 $str_k_v = implode(‘,‘,$str_k_v); 75 }else{ 76 $str_k_v = &$field; 77 } 78 $sql .= $where == ‘‘ ? $str_k_v : $str_k_v.‘ where ‘.$where; 79 return $this->query($sql,‘update‘); 80 } 81 /** 单表查询 82 */ 83 public function select($tbname,$params=array()) { 84 global $p; 85 $sql = ‘‘; //查询sql 86 $total_sql = ‘‘; //统计sql 87 if(count($params) == 0) { //没有传递数组参数，而是直接传递了sql语句 88 if(stripos($tbname,‘select‘) === false) { //只传递了表名 89 $sql = "select * from ".$this->tbpre.$tbname; 90 }else{ 91 $sql = $tbname; 92 } 93 } 94 $params[‘fields‘] = isset($params[‘fields‘]) ? $params[‘fields‘] : ‘*‘; 95 $params[‘where‘] = isset($params[‘where‘]) ? $params[‘where‘] : ‘‘; 96 $params[‘group‘] = isset($params[‘group‘]) ? $params[‘group‘] : ‘‘; 97 $params[‘having‘] = isset($params[‘having‘]) ? $params[‘having‘] : ‘‘; 98 $params[‘order‘] = isset($params[‘order‘]) ? $params[‘order‘] : ‘‘; 99 $params[‘p‘] = isset($params[‘p‘]) ? $params[‘p‘] : $p; //当前分页 100 $params[‘pagesize‘] = isset($params[‘pagesize‘]) ? $params[‘pagesize‘] : 10; //每页大小 101 $params[‘limit‘] = isset($params[‘limit‘]) ? $params[‘limit‘] : $this->get_limit($params[‘p‘],$params[‘pagesize‘]); 102 $params[‘count‘] = isset($params[‘count‘]) ? $params[‘count‘] : 0; //是否需要统计 0=不统计 1=统计 103 $params[‘index‘] = isset($params[‘index‘]) ? $params[‘index‘] : ‘‘; //统计时用的索引 104 105 if($sql == ‘‘) { //没有直接传递查询sql 106 $sql = ‘select ‘.$params[‘fields‘].‘ from ‘.$this->tbpre.$tbname; 107 if($params[‘where‘]) $sql .= ‘ where ‘.$params[‘where‘]; 108 if($params[‘group‘]) $sql .= ‘ group by ‘.$params[‘group‘]; 109 if($params[‘having‘]) $sql .= ‘ having ‘.$params[‘having‘]; 110 if($params[‘order‘]) $sql .= ‘ order by ‘.$params[‘order‘]; 111 if($params[‘limit‘]) $sql .= ‘ limit ‘.$params[‘limit‘]; 112 } 113 $rs = $this->query($sql,‘select‘); //开始查询 114 if($params[‘count‘]) { //需要统计 115 $tmp = $this->counts($tbname,$params[‘where‘],$params[‘index‘]); 116 $rs[‘total_sql‘] = $tmp[‘total_sql‘]; 117 $rs[‘total‘] = $tmp[‘total‘]; 118 $rs[‘total_error‘] = $tmp[‘error‘]; 119 $rs[‘page‘] = H::page($params[‘p‘],$params[‘pagesize‘],$tmp[‘total‘]); //生成分页 120 } 121 return $rs; 122 } 123 /** 获取单条数据 124 */ 125 public function find($tbname,$where,$fields=‘*‘) { 126 $sql = ‘select ‘.$fields.‘ from ‘.$this->tbpre.$tbname.‘ where ‘.$where.‘ limit 0,1‘; 127 $rs = $this->query($sql,‘select‘); 128 if($rs[‘num‘]) $rs[‘list‘] = $tmp[‘list‘][0]; //有数据 返回一维数组 129 return false; //没有数据返回 false 130 } 131 /** 统计表数据条数 132 */ 133 public function counts($tbname,$where=‘‘,$index=‘‘) { 134 $tmp = array( 135 ‘total_sql‘=>"select count(1) as total from ".$this->tbpre.$tbname.‘ ‘.$index.($where==‘‘?‘‘:" where ".$where), 136 ‘total‘=>0, 137 ‘error‘=>‘‘, 138 ); 139 $rs = $this->query($tmp[‘total_sql‘],‘select‘); 140 if(is_numeric($rs)) { //统计的sql 正确执行了 141 $tmp[‘total‘] = $rs; 142 return $tmp; 143 } 144 $tmp[‘error‘] = $rs; //sql语句执行错误 145 return $tmp; 146 } 147 /** 执行sql语句 支持直接传递sql 自动判断返回值 148 */ 149 public function query($sql,$type=‘‘) { 150 if($type == ‘‘) $type = $this->get_query_type($sql); 151 $time = H::getmicrotime(); 152 $rs = mysql_query($sql,$this->linkid); 153 $time = round(H::getmicrotime() - $time); 154 $this->error = mysql_error($this->linkid); 155 $this->sql[] = array(‘sql‘=>$sql,‘query_time‘=>$time,‘error‘=>$this->error); 156 //插入 157 if($type == ‘insert‘) { 158 return array( 159 ‘sql‘=>$sql, 160 ‘query_time‘=>$time, 161 ‘rows‘=>mysql_affected_rows($this->linkid), //如果是create database 失败 返回的是 -1 162 ‘error‘=>$this->error, 163 ‘autoid‘=>mysql_insert_id($this->linkid), 164 ); 165 } 166 //删除 167 if($type == ‘delete‘) { 168 return array( 169 ‘sql‘=>$sql, 170 ‘query_time‘=>$time, 171 ‘rows‘=>mysql_affected_rows($this->linkid), //如果是drop database 返回的是 0 失败返回 -1 172 ‘error‘=>$this->error, 173 ); 174 } 175 //更新 176 if($type == ‘update‘) { 177 return array( 178 ‘sql‘=>$sql, 179 ‘query_time‘=>$time, 180 ‘rows‘=>mysql_affected_rows($this->linkid), 181 ‘error‘=>$this->error, 182 ); 183 } 184 //查询 185 if($type == ‘select‘) { 186 if(stripos($sql,‘count(‘) === false) { //查询 注意count语句的时候 count 与 ( 要紧贴 才能匹配到 187 $i = 0; 188 $list = array(); 189 if($rs) { 190 while($row = mysql_fetch_assoc($rs)) { 191 $list[$i] = $row; 192 $i++; 193 } 194 mysql_free_result($rs); //释放结果内存 195 unset($rs); 196 } 197 return array( 198 ‘sql‘=>$sql, 199 ‘total_sql‘=>‘‘, 200 ‘query_time‘=>$time, 201 ‘error‘=>$this->error, 202 ‘total_error‘=>$this->error, 203 ‘total‘=>‘‘, 204 ‘num‘=>$i, 205 ‘list‘=>$list 206 ); 207 }else{ //统计 208 if($rs) { 209 $rs = mysql_fetch_assoc($rs); 210 $rs = $rs[‘total‘]; 211 }else{ 212 $rs = $this->error; //统计失败返回错误 213 } 214 return $rs; 215 } 216 } 217 //没有匹配到sql执行类型 218 return false; 219 } 220 /** 过滤sql单引号 221 */ 222 public function filter(&$str) { 223 if(function_exists(‘mysql_real_escape_string‘)) { 224 $str = mysql_real_escape_string($str); 225 }elseif(function_exists(‘mysql_escape_string‘)) { 226 $str = mysql_escape_string($str); 227 }else{ 228 $str = addslashes($str); 229 } 230 return $str; 231 } 232 /** 获取sql语句执行类型 233 */ 234 private function get_query_type(&$sql) { 235 $sql = ltrim(strtolower($sql)); 236 $tmp = substr($sql,0,stripos($sql,chr(32))); 237 switch ($tmp) { 238 case ‘show‘: 239 $tmp = ‘select‘; 240 break; 241 case ‘create‘: 242 $tmp = ‘insert‘; 243 break; 244 case ‘drop‘; 245 case ‘truncate‘; 246 $tmp = ‘delete‘; 247 break; 248 case ‘alter‘: 249 $tmp = ‘update‘; 250 break; 251 } 252 return $tmp; 253 } 254 /** 关闭数据库连接 255 */ 256 public function close() { 257 if ($this->linkid) { 258 mysql_close($this->linkid); 259 } 260 $this->linkid = null; 261 } 262 /** 返回limit语句 263 * @param $p 当前页码 264 * @param $pagesize 分页大小 265 */ 266 public function get_limit($p=1,$pagesize=10) { 267 return ($p-1) * $pagesize . ",$pagesize"; 268 } 269 /** 获取mysql的版本信息 270 */ 271 public function mysql_get_server_info() { 272 return mysql_get_server_info($this->linkid); 273 } 274 } //end class 275 ?>
欢迎交流，恳请大神指点。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
