|
php数据库操作类，实现表的增删改查，获取行数，查询多条数据记录，左连接查询，创建数据表结构等。功能丰富，方便移值，有需要的朋友，可以参考下。
php数据库类完整代码与示例如下。
1、代码
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()."
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 条件数组
*
$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';
*
* @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 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 = "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 ";
}
else{
//从服务器端取得url信息
if($_GET[$id] === null){
$_SESSION[$id] = $_SERVER["REQUEST_URI"];
if(!sizeof($_GET))$_SESSION[$id].="?p=1";
}
$str = " 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 ";
}
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 $sql确定 ";
}
elseif($_GET[sure] && $this->query($sql)){
echo "完成操作";
}
else{
echo "操作失败： > $sql ";
}
exit;
}
};
?>
调用示例：
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");
?>
|