php如何对mysql进行增删修查_数据库操作封装

/**

*@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']."
";

echo "teacherID: ".$row['teacherID']."
";

}

*/

/**

*@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 "
update sql: ".$sql."
";

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;

}

?>