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