数据库操作封装

<?php
/**
 *@desc: 数据库操作辅助文件，包括sql语句的构造、增删修查函数
 *@author:JiaYusheng
 *@time:01.10.2011
 */
include_once("inc/auth.php");			// 如需登录验证则包含，未登录将退出
include_once("inc/utility_all.php");	        // 如需要使用公用函数

/**
 *@desc: 查询表中的所有记录
 *@param: 表名
 *@return: 包含查询结果的记录数组
 */
function queryAll($table)
{
    $sql = "select * from ".$table;
    global $connection;
    $result = exequery($connection,$sql);
    $rows = array(); 
    while($row = mysql_fetch_array($result))
    {
	$rows[] = $row;
    }
    return $rows;
}
/**
 *@desc:依据$cloumns条件查询表$table
 *@param: 表名，条件字段与值构成的数组,开始记录ID,每页显示记录数
 *@return：查询所得的记录构成的数组
 */
function queryByPaging($table,$cloumns,$firstcount,$displaypg)
{
    global $connection;
    $sql = "select * from ".$table;
    $sql .= getConditionClause($cloumns);
    $sql .= " limit $firstcount,$displaypg";
    
    $rows = array();
    $result = exequery($connection,$sql);
    while($row = mysql_fetch_array($result))
    {
	$rows[] = $row;
    }
   // echo $sql;
    return $rows;
}
/**
 *@desc:根据sql进行查询，并将查询结果用二维数组返回
 *@param: sql
 *@return：查询所得的记录构成的数组
 */
function query($sql)
{
    global $connection;
    $rows = array();
    $result = exequery($connection,$sql);
    while($row = mysql_fetch_array($result))
    {
	$rows[] = $row;
    }
    return $rows;
}
/*
 *eg:
 
    $sql="select * from $rtable where sid=$sid order by rid desc limit $firstcount,$displaypg";
    $rows = query($sql);
 */


/**
 *@desc:依据$cloumns条件查询表$table
 *@param: 表名，条件字段与值构成的数组
 *@return：查询所得的记录构成的数组
 */
function queryAllByCondtion($table,$cloumns)
{
    $rows = array();
    global $connection;
    $sql = "select * from ".$table;
    $sql .= getConditionClause($cloumns);
    
    $rows = array();
    $result = exequery($connection,$sql);
    while($row = mysql_fetch_array($result))
    {
	$rows[] = $row;
    }
    //echo $sql;
    return $rows;
}
/*
 *eg:
$table = "xl_teacherinfo";
$cloumns = array(
               // "teacherID" => "s201025006",
               // "username" => "jinyong",
               // "alias" => "xiaojin",
                "gender" => "1",
              //  "role" => "4"
                 );
$rows = queryAllByCondtion($table,$cloumns);

foreach($rows as $row)
{
    echo "username: ".$row['username']."<br>";
    echo "teacherID: ".$row['teacherID']."<br>";
}
 */

/**
 *@desc:查询表的某个字段
 *@param:表名、字段名
 *@return:包含查询结果的数组
 */
function queryCloumns($table,$arrCloumns,$arrCondition)
{
    $sql = getColumns($arrCloumns);
    $sql .= " from ".$table;
    $sql .= getConditionClause($arrCondition);
    
    $rows = array();
    global $connection;
    $result = exequery($connection,$sql);
    $num_rows = mysql_num_rows($result);
    
    //将结果保存到数组$rows中
    $rows = array();
    $result = exequery($connection,$sql);
    while($row = mysql_fetch_array($result))
    {
	$rows[] = $row;
    }
   // echo $sql;
    return $rows;
    
}
/*
 *eg：
$table = "xl_teacherinfo";
$arrCondition= array(
                    "role" => 4,
                    "gender" => 1
                    );
$arrCloumns = array(
                    "teacherID",
                    "teachername",
                    "username"
                       );
echo queryCloumns($table,$arrCloumns,$arrCondition);
 */

/**
 *@desc: 向表table的columns字段中插入值values
 *@param: 表名，要插入记录的字段，字段值构成的数组
 *@return: 包含查询结果的数组
 */
function insert($table,$cloumns)
{
    $count = 0;
    $sql = "insert into ".$table."(";
    foreach($cloumns as $key => $value)
    {
        if(0==$count)
            $sql .= $key;
        else
            $sql .= ",".$key;
        $count++;
    }           
    $sql .=") values(";  
    
    $count = 0;
    foreach($cloumns as $key => $value)
    {
        if(0==$count)
        {
            if(is_string($value))
            {
                $sql .= "'".$value."'";
            }
            else
                $sql .=$value;
        }
        else
        {
            if(is_string($value))
                $sql .= ",'".$value."'";
            else
                $sql .= ",".$value;
        }
        $count++;
    }
    $sql .= ") ";
    //echo $sql;//test@@@@
    global $connection;
    $result = exequery($connection,$sql);
    $num_rows = mysql_affected_rows();
    return $num_rows;
}
/*
 *eg
$table = "xl_dict_role";
$cloumns = array(
                "name" => "jys",
                "serial" =>99
                );
echo insert($table,$cloumns);
 */

/**
 *@desc: 向表table的columns字段中插入值values
 *@param: 表名，要插入记录的字段数组，字段值数组
 *@return: 包含查询结果的数组
 */
function insertCV($table,$cloumns,$values)
{
    $count = 0;
    //update xl_dict_role(name,seiral) values('xinlilaoshi',10) where id = 1;
    $sql = "insert into ".$table."(";
    foreach($cloumns as $item)
    {
        if(0==$count)
            $sql .= $item;
        else
            $sql .= ",".$item;
        $count++;
    }
    $sql .=") values(";
    
    $count = 0;
    foreach($values as $item)
    {
        if(0==$count)
        {
            if(is_string($item))
            {
                $sql .= "'".$item."'";
            }
            else
                $sql .=$item;
        }
        else
        {
            if(is_string($item))
                $sql .= ",'".$item."'";
            else
                $sql .= ",".$item;
        }
        $count++;
    }
    $sql .= ") ";
    
    global $connection;
    $result = exequery($connection,$sql);
    $num_rows = mysql_affected_rows();
    return $num_rows;
}
/*
 *eg $table = "xl_dict_role";
$cloumns = array("name","serial");
$values = array("xinlilaoshi",10);
echo insertCV($table,$cloumns,$values);
*/


/**
 *@desc: 删除table中cloumn字段为value的记录
 *@param: 表名，字段，字段值
 *@return: 影响的记录数目
 */
function deleteByCloumn($table,$condCloumns)
{
    $sql = "delete from ".$table;
    //"where ".$cloumn."='".$value."'";
    $sql .= getConditionClause($condCloumns);

    global $connection;
    $result = exequery($connection,$sql);
    $num_rows = mysql_affected_rows();
    return $num_rows;
}

/*
 *eg
   $delID = "7";
   $table = "xl_teacherinfo";
   $cloumns = array("tid"=>$delID);
   echo deleteByCloumn($table,$cloumns);
*/

/**
 *@desc: 修改table中condCloumn字段为condValue的记录,
 *将cloumns字段修改为值values
 *@param: 表名，被修改字段，被修改字段值，条件字段与条件字段值构造的数组
 *@return: 影响的记录数目
 */
function update($table,$cloumns,$condCloumns)
{
    $count = 0;
    //update xl_dict_role(name,seiral) values('xinlilaoshi',10) where id = 1;
    $sql = "update ".$table." set ";
    foreach($cloumns as $key => $value)
    {
        if(0==$count)
        {
            if(is_string($value))
            $sql .= $key."='".$value."'";
            else
                $sql .= $key."=".$value;
        }
        else
        {
            if(is_string($value))
            $sql .= ",".$key."='".$value."'";
            else
                $sql .= ",".$key."=".$value;
        }
        $count++;        
    }
  
    $sql .= getConditionClause($condCloumns);
    //echo "<br>update sql: ".$sql."<br>";
    global $connection;
    $result = exequery($connection,$sql);
    $num_rows = mysql_affected_rows();
    return $num_rows;
}
/*
 *eg
$table = "xl_dict_role";
$cloumns = array("name"=>"心理",
                 "serial"=>"88");
$condCloumns = array("id"=>11);
echo update($table,$cloumns,$condCloumns);
 */

/*
function update($table,$cloumns,$values,$condCloumns)
{
    $count = 0;
    //update xl_dict_role(name,seiral) values('xinlilaoshi',10) where id = 1;
    $sql = "update ".$table;

    $sql .= getConditionClause($condCloumns);
    
    global $connection;
    $result = exequery($connection,$sql);
    $num_rows = mysql_num_rows($result);
    return $num_rows;
}
*/

/*
 *eg
$table = "xl_dict_role";
$cloumns = array("name","serial");
$values = array("xinlilaoshi",10);
$condCloumns = array("id"=>1);
echo update($table,$cloumns,$values,$condCloumns);
 */

/**
 *@desc: 构造select 语句的被选择字段语句
 *@param: 被选择字段的数组
 *@return: "select clo1, clo2"
 */
function getColumns($arrCloumns)
{
   $count = 0;
   $sql = "select ";
   foreach($arrCloumns as $item)
   {
     if(0==$count)
     {
        $sql .=$item;
     }
     else
     {
        $sql .=", ".$item;
     }
      $count++;
   }
  return $sql;
}
/*
 *eg:
 *
$arr = array('leo','jia');
//echo getColumns($arr);
 */

/**
 *@desc:根据数组queryItems构造SQL语句中的AND条件子句
 *@param:条件字段的数组
 *@return:sql语句的条件语句
 */
function getConditionClause($queryItems)
{

    $count = 0;
    $where = "";
    //根据数组，循环生成条件子句
    foreach($queryItems as $key => $value)
    {
        
        if ($count == 0)
                $where = " where ";
        else
                $where .= " and ";
        
        //根据查询列的数据类型，决定是否加单引号
        if(is_numeric($value))
        {
            $where .= $key."=".$value;
        }
        else if(!is_numeric($value))
        {
            $where .=  $key." Like '%".$value."%'";
        }
        $count++;    
    }
    return $where;
}
/*
 *eg:
$arr1 = array("id" => 1);
echo getConditionClause($arr1);
 */

/**
 *@desc:利用包含条件字段的数组构造条件子句
 *@param:包含条件字段的数组，与还是或查询，取值={"and","or"}
 *@return:条件子句
 */
function getConditionClauseByType($queryItems,$type)
{

    $count = 0;
    $where = "";
    
    foreach($queryItems as $key => $value)
    {
        
        if ($count == 0)
            $where = " where ";
        else
            $where .= " ".$type." ";

        //根据查询列的数据类型，决定是否加单引号
        if(is_numeric($value))
        {
            $where .= $key."=".$value;
        }
        else if(!is_numeric($value))
        {
            $where .=  $key." Like '%".$value."%'";
        }
        $count++;      
    }
    return $where;
}
?>