<?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;}?>
