php数据库操作类,实现表的增删改查,获取行数,查询多条数据记录,左连接查询,创建数据表结构等。功能丰富,方便移值,有需要的朋友,可以参考下。
php数据库类完整代码与示例如下。 1、代码 <?php /** * * 数据操作类 * @author Xiaoqiang * @link http://bbs.it-home.org */ class Db { var $links; // 构造函数,初始化数据库(主机,用户名,密码,数据库名) function __construct() { $this->links = mysql_connect(DB_HOST, DB_USER, DB_PWD); if(DEBUG){ mysql_select_db(DB_NAME) or die('ERROR:'.mysql_error()); } else{ mysql_select_db(DB_NAME); } $char_sql = "SET NAMES '" . CHARSET . "'"; $this->query($char_sql); } /** * ****************** 操作 ****************** */ /** * 增删改查操作方法 * 输入sql语句 * 返回布尔值或结果集$row */ function query($sql) { if(DEBUG){ $render = mysql_query($sql) or die('query ERROR:'.mysql_error()."<br>sql语句出错:" . $sql); return $render; } else{ return mysql_query($sql); } } /** * 计算行数方法 * 输入 * 结果数组 */ function count($table,$condition='1') { $sql = "select count(*) from `".DB_PREFIX.$table."` where $condition"; $result = $this->select($sql); return $result[0]['count(*)']; } /** * 原始的sql语句查操作方法 * 输入sql语句 * 结果数组 */ function select($sql) { $row = $this->query($sql); $results = array(); while($arr = $this->fetch($row)){ $results[] = $arr; } //$this->free_result($row); return $results; } /** * 检查某字段是否存在某值 * @param 输入表名.字段,值 * @return id 或者 false **/ function check_exists($table,$val){ $render = false; $tab = explode('.',$table); if($tab['1'] && $tab['1']!='id'){ $fields = $tab['1']; $table = "{$tab[0]}.id,{$fields}"; } else{ $fields = 'id'; $table = $tab[0].".id"; } $condition = "`$fields` = '{$val}'"; $detail = $this->read($table,$condition); if($detail[$fields]){ $render = $detail['id']; } return $render; } /** * 查询多条数据方法 * 输入表名.字段,字段;查询条件,条数 * 如果条件是数组,则进入高级搜索模式 * 返回结果数组 */ function readall($table,$condition='1',$limit='') { $tab = explode('.',$table); $table = $tab['0']; if($tab['1']){ $fields = $tab['1']; $fields_array =explode(',',$fields); $fields = ''; foreach( $fields_array as $one){ $fields .= "`$one`,"; } $fields = rtrim($fields,','); } else{ $fields = '*'; } if(is_array($condition)){ $condition = $this->parse_condition($condition); } $sql = "select $fields from `".DB_PREFIX.$table."` where $condition"; if($limit)$sql .= " limit $limit"; return $this->select($sql); } /** * 查询单条数据方法 * 输入表名.字段,字段;查询条件 * 返回结果数组 */ function read($table,$condition='1') { $render = $this ->readall($table,$condition,1); return $render[0]; } /** * 修改数据方法 * 输入表名,插入数据array('字段'=>'值'),条件 * 返回布尔值 */ function update($table,$data,$condition ) { $set = ''; foreach( $data as $key=>$val){ $set .= "`$key` = '".$val."',"; } $set = rtrim($set,','); if(is_array($condition)){ $condition = $this->parse_condition($condition); } $sql = "update `".DB_PREFIX.$table."` set $set where $condition"; return $this->query($sql); } /** * 插入数据方法 * 输入表名,数据array('字段'=>'值') * 返回布尔 */ function insert($table,$data) { $fields = array(); $values = array(); foreach( $data as $key=> $val){ if(is_array($val)){ $_values = array(); $_fields = array(); foreach( $val as $k=> $v){ $_fields[]= "`$k`"; $_values[]= "'{$v}'"; } $fields = $_fields; $values[] = '('.implode(',',$_values).')'; } else{ $fields[] = "`$key`"; $values[] = "'{$val}'"; } } $fields = implode(',',$fields); $values = implode(',',$values); $sql = "insert into `".DB_PREFIX.$table."` ($fields) values($values)"; return $this->query($sql); } /** * 删除数据方法 * 输入表名,条件 * 返回bool */ function delete($table,$condition) { if(empty($condition)){ die('条件不能为空'); } if(is_array($condition)){ $condition = $this->parse_condition($condition); } $sql = "delete from `".DB_PREFIX.$table."` where $condition"; return $this->query($sql); } /** * 解析条件的函数 * @param 条件数组 * <code> $arr[] = "`id`==0"; $arr[] = "`id`==5"; $arr['id'] = "5"; $arr['or'][] = "`id`!=2"; $arr['or'][] = "`id`!=1"; $arr['or'][] = "`id`!=2"; $arr['groups'][]='id'; $arr['orders']['id']='asc'; $arr['orders']['td']='DESC'; * </code> * @return str **/ function parse_condition($condition){ $and = '1'; $or = '0'; $groups = array(); $orders = array(); foreach( $condition as $key=>$val){ if(is_numeric($key)){ $and .= " and $val"; } elseif(strtolower($key)== 'or'){ //处理or条件 if(is_array($val)){ foreach( $val as $k=>$v){ if(is_numeric($k)){ $or .= " or {$v}"; } elseif(is_array($v)){ $v = implode(',',$v); $or .= " or `$k` in ($v)"; }else{ $or .= " or `$k='{$v}'"; } } }else{ $or .= " or $val'"; } } elseif(strtolower($key)== 'groups'){ //处理group by foreach( $val as $k=>$v){ $groups[] = $v; } }elseif(strtolower($key)== 'orders'){ //处理order by foreach( $val as $k=>$v){ $orders[] = $k.' '.$v; } }else{ if(is_array($val)){ $val = implode(',',$val); $and .= " and `$key` in ($val)"; }else{ $and .= " and `$key`='{$val}'"; } } } if($and!='1' && $or!='0')$where = $and.' or '.$or; elseif($and!='1') $where = $and; elseif($or!='0') $where = $or; if($groups)$where .= " group by ".implode(',',$groups); if($orders)$where .= " order by ".implode(',',$orders); $where = str_replace('1 and','',str_replace('0 or','',$where)); return $where; } /** * 锁表方法 * 输入表名,锁定类型,r or w 写锁要放在读锁前面 * 返回bool */ function lock($table,$type='r') { if($type=='r'){ $type = 'READ'; } else{ $type = 'WRITE'; } $sql = "lock table `".DB_PREFIX.$table."` $type"; return $this->query($sql); } /** * 解锁表方法 * * 返回bool */ function unlock( ) { $sql = "unlock tables"; return $this->query($sql); } /** * 结果集放入数组方法 * 返回数组,指针下移 */ function fetch($row) { return mysql_fetch_array($row,MYSQL_ASSOC); } /** * 计算结果集行数方法 * 输入$row * 返回行数 */ function num_rows($row) { return mysql_num_rows($row); } /** * 计算结果集列数方法 * 输入$row * 返回列数 */ function num_fields($row) { return mysql_num_fields($row); } /** * 释放result结果集内存 * 返回布尔值 */ function free_result($row) { return mysql_free_result($row); } /** * 查看指定表的字段名 * 输入表名 * 返回全部字段名数组 */ function list_fields($table) { $fields = mysql_list_fields(DB_NAME, DB_PREFIX.$table, $this->links); $columns = mysql_num_fields($fields); for ($i = 0; $i < $columns; $i++) { $row[$i] = mysql_field_name($fields, $i); } return $row; } /** * 查看数据库版本方法 */ function version() { return mysql_get_server_info(); } /** * 插入时查看插入ID */ function insert_id() { return mysql_insert_id(); } /** * 分页方法 */ function page($table,$condition='1',$pagesize=20,$id='page') { $page = $_GET[$id]; if(!$page)$page= 0 ; elseif(!is_numeric($page))die('分页出错'); //查找结果集 $p = $page * $pagesize; $limit = $p.",".$pagesize; $results = $this->readall($table,$condition,$limit); //取得结果集行数 $num = $this->count($table,$condition); //定义最后页 $maxpage if ($num % $pagesize) { $maxpage = (int) ($num / $pagesize +1); } else $maxpage = $num / $pagesize; if(STATICS){ //从服务器端取得url信息 if($_GET[$id] === null){ $_SERVER["REQUEST_URI"] = str_replace('index.php','',$_SERVER["REQUEST_URI"]); $_SESSION[$id] = str_replace('.html','',$_SERVER["REQUEST_URI"],$count); $_SESSION[$id] = $count?$_SESSION[$id] :$_SESSION[$id].'index'; if(!sizeof($_GET))$_SESSION[$id].="-htm"; } $str = "<div class=pg style=padding-top:25px;>First "; if($page)$str .= "Previous "; if($page-3>=0)$str .="".($page-2)." "; if($page-2>=0)$str .="".($page-1)." "; if($page-1>=0)$str .="".$page." "; if($page < $maxpage)$str .=($page+1)." "; if($page+1 < $maxpage)$str .="".($page+2)." "; if($page+2 < $maxpage)$str .="".($page+3)." "; if($page+3 < $maxpage)$str .="".($page+4)." "; if($page+1 < $maxpage)$str .="Next "; if(!$maxpage)$maxpage=1; $str .="Last ".($page+1)."/".$maxpage."Total </div>"; } else{ //从服务器端取得url信息 if($_GET[$id] === null){ $_SESSION[$id] = $_SERVER["REQUEST_URI"]; if(!sizeof($_GET))$_SESSION[$id].="?p=1"; } $str = "<div class=pg style=padding-top:25px;>First "; if($page)$str .= "Previous "; if($page-3>=0)$str .="".($page-2)." "; if($page-2>=0)$str .="".($page-1)." "; if($page-1>=0)$str .="".$page." "; if($page < $maxpage)$str .=($page+1)." "; if($page+1 < $maxpage)$str .="".($page+2)." "; if($page+2 < $maxpage)$str .="".($page+3)." "; if($page+3 < $maxpage)$str .="".($page+4)." "; if($page+1 < $maxpage)$str .="Next "; if(!$maxpage)$maxpage=1; $str .="Last ".($page+1)."/".$maxpage."Total </div>"; } return array($results,$str); } /** * 左连接多表查询 * @param * @return **/ function leftjoin($left,$right,$on,$condition,$limit=1){ $left = explode('.',$left); $right = explode('.',$right); $left['0'] = "`".DB_PREFIX.$left['0']."`"; $right['0'] = "`".DB_PREFIX.$right['0']."`"; if($left['1'] || $right['1']){ $fields = ''; if(!empty($left['1'])){ $_field = explode(',',$left['1']); foreach( $_field as $one){ $fields.=$left['0'].'.`'.$one."`,"; } } if(!empty($right['1'])){ $_field = explode(',',$right['1']); foreach( $_field as $one){ $fields.=$right['0'].".`".$one."`,"; } } $fields = rtrim($fields,','); } else{ $fields = '*'; } $on = str_replace('\2',$right[0],str_replace('\1',$left[0],$on)); $condition = str_replace('\2',$right[0],str_replace('\1',$left[0],$condition)); $sql = "SELECT {$fields} FROM {$left[0]} LEFT JOIN {$right[0]} ON ( {$on} ) WHERE ( {$condition} ) LIMIT {$limit} "; $query = $this->query($sql); $field_num = mysql_num_fields($query); while($arr = mysql_fetch_array($query,MYSQL_NUM)){ $_arr = array(); for( $i=0 ; $i<$field_num ; $i++ ){ $table = str_replace(DB_PREFIX,'',mysql_field_table($query, $i)); $field = mysql_field_name($query, $i); $_arr[$table.'.'.$field] = $arr[$i]; } $array[]=$_arr; } $array=$limit==1?$arrat[0]:$array; return $array; } /** * 用于创建一个表结构 * @param 表名,结构 array(字段,格式,注释) 表注释 索引array(字段,字段) 全文搜索(字段,字段) * @return 打印 **/ function createTable($tablename,$data,$comment='',$key='',$fulltext=''){ $_key=''; $_type = ''; $_fulltext = ''; $tablename = DB_PREFIX.$tablename; $sql = "CREATE TABLE IF NOT EXISTS `$tablename` ( `id` int(10) unsigned NOT NULL auto_increment COMMENT 'ID', "; foreach( $data as $one){ switch($one[1]){ case '': $_type = 'varchar(255)'; break; case 'tinyint': $_type = 'tinyint(1)'; break; case 'time': $_type = 'int(10)'; break; default: if(strpos($one[1],'.')!==false){ $_type = explode('.',$one[1]); $_type = $_type[0].'('.$_type[1].')'; } else{ $_type = $one[1]; } break; } $sql.="`{$one[0]}` $_type NOT NULL COMMENT '{$one[2]}', "; } if(!empty($key)){ foreach( $key as $one){ $_key.="KEY `$one` (`$one`), "; } } if(!empty($fulltext)){ foreach( $fulltext as $one){ $_key.="FULLTEXT `$one` (`$one`), "; } } $sql.= $_key.$_fulltext."PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=".CHARSET." COMMENT '$comment'; "; if(!$_GET[sure]){ if(empty($_GET)){ $url = '?sure=1'; } else{ $url = $_SERVER["REQUEST_URI"].'&sure=1'; } echo "即将执行以下建表操作:<br><pre class="brush:php;toolbar:false">$sql确定 Copy after login > $sql Copy after login 调用示例: <?php //实例 define("DB_HOST", 'localhost'); define("DB_USER", 'root'); define("DB_PWD", ''); define("DB_NAME", "test"); define("DB_PREFIX", "test_"); define('CHARSET', 'gbk'); // 编码 define("GEBUG", 1); //建表 $Db=new Db(); $data[] = array('title','','标题'); $data[] = array('open','tinyint.1','是否公开'); $data[] = array('keyword','','关键词'); $data[] = array('content','text','内容'); $data[] = array('created','time','时间'); //$Db->createTable('article',$data,'文章表'); //增删改查 $data['title']='t'; $data['keyword']='k'; $Db->insert('article',$data); $num = $Db->read('article.id','1 order by id desc'); $data['created'] = mktime()+$num['id']; $Db->update('article',$data,"`id` =2"); $Db->delete('article',"`id` =3"); ?> Copy after login |