/****************************************************************************************************************
为了便于自己的开发，又不想使用ADODB、PEAR::DB这样的庞然大物，
就用在PHPLibDB类的基础上、参考PEAR::DB类，封装的DB类，简单好使，非常方便。
MySQL有效
[连接数据库]
//包含数据库处理类文件
include_once("database.inc.php");
//本地数据库配置
define("DB_HOST","localhost");//数据库服务器
define("DB_USER_NAME","root");//数据库用户名
define("DB_USER_PASS","");//密码
define("DB_DATABASE","test");//数据库
//连接本地数据库
$db=newDB_Sql();
$db->connect(DB_DATABASE,DB_HOST,DB_USER_NAME,DB_USER_PASS);
[使用方法]
//获取所有记录
$sql="SELECT*FROMtable1";
$all_record=$db->get_all($sql);
//获取一条
$sql="SELECT*FROMtable1WHEREid='1'";
$one_row=$db->get_one($sql);
//分页查询，提取20条记录
$sql="SELECT*FROMtable1";
$page_record=$db->limit_query($sql,$start=0,$offset=20,$order="ORDERBYidDESC");
//提取指定数目的记录
$sql="SELECT*FROMtable1";
$limit_record=$db->get_limit($sql,10);
//统计记录数，统计所有类型为学生的
$count=$db->count("table1","id","type='student'");
//插入一条记录
$info_array=array(
"name"=>"heiyeluren",
"type"=>"student",
"age"=>"22",
"gender"=>"boy"
);
$db->insert("table1",$info_array);
//更新一条记录
$info_array=array(
"name"=>"heiyeluren",
"type"=>"teacher",
"age"=>"22",
"gender"=>"boy"
);
$db->update("table1",$info_array,"name='heiyeluren'");
//删除记录
$db->delete("table1","name='heiyeluren'");
//执行一条无结果集的SQL
$db->execute("DELETEFROMtable1WHEREname='heiyeluren'");
********************************************************************************************************/
/**
*文件:database.inc.php
*描述:数据库操作类
*说明:本库使用PHPLibDB库作为核心,同时增加一些实用方法,详细参考注释
*/
classDB_Sql
{
/*public:connectionparameters*/
var$Host="";
var$Database="";
var$User="";
var$Password="";
/*public:configurationparameters*/
var$Auto_Free=1;##Setto1forautomaticmysql_free_result()
var$Debug=0;##Setto1fordebuggingmessages.
var$Halt_On_Error="yes";##"yes"(haltwithmessage),"no"(ignoreerrorsquietly),"report"(ignoreerrror,butspitawarning)
var$PConnect=0;##Setto1tousepersistentdatabaseconnections
var$Seq_Table="db_sequence";
/*public:resultarrayandcurrentrownumber*/
var$Record=array();
var$Row;
/*public:currenterrornumberanderrortext*/
var$Errno=0;
var$Error="";
/*public:thisisanapirevision,notaCVSrevision.*/
var$type="mysql";
//var$revision="1.2";
/*private:linkandqueryhandles*/
var$Link_ID=0;
var$Query_ID=0;
var$locked=false;##settotruewhilewehavealock
/*public:constructor*/
functionDB_Sql(){
$this->query($query);
}
/*public:sometrivialreporting*/
functionlink_id(){
return$this->Link_ID;
}
functionquery_id(){
return$this->Query_ID;
}
/*public:connectionmanagement*/
functionconnect($Database="",$Host="",$User="",$Password=""){
/*Handledefaults*/
if(""==$Database)
$Database=$this->Database;
if(""==$Host)
$Host=$this->Host;
if(""==$User)
$User=$this->User;
if(""==$Password)
$Password=$this->Password;
/*establishconnection,selectdatabase*/
if(0==$this->Link_ID){
if(!$this->PConnect){
$this->Link_ID=mysql_connect($Host,$User,$Password);
}else{
$this->Link_ID=mysql_pconnect($Host,$User,$Password);
}
if(!$this->Link_ID){
$this->halt("connect($Host,$User,$Password)failed.");
return0;
}
if(!@mysql_select_db($Database,$this->Link_ID)){
$this->halt("cannotusedatabase".$Database);
return0;
}
}
return$this->Link_ID;
}
/*public:discardthequeryresult*/
functionfree(){
@mysql_free_result($this->Query_ID);
$this->Query_ID=0;
}
/*public:performaquery*/
functionquery($Query_String){
/*Noemptyqueries,please,sincePHP4chokesonthem.*/
if($Query_String=="")
/*Theemptyquerystringispassedonfromtheconstructor,
*whencallingtheclasswithoutaquery,e.g.insituations
*likethese:'$db=newDB_Sql_Subclass;'
*/
return0;
if(!$this->connect()){
return0;/*wealreadycomplainedinconnect()aboutthat.*/
};
#Newquery,discardpreviousresult.
if($this->Query_ID){
$this->free();
}
if($this->Debug)
printf("Debug:query=%s
n",$Query_String);
$this->Query_ID=@mysql_query($Query_String,$this->Link_ID);
$this->Row=0;
$this->Errno=mysql_errno();
$this->Error=mysql_error();
if(!$this->Query_ID){
$this->halt("InvalidSQL:".$Query_String);
}
#Willreturnnadaifitfails.That'sfine.
return$this->Query_ID;
}
/*public:walkresultset*/
functionnext_record(){
if(!$this->Query_ID){
$this->halt("next_recordcalledwithnoquerypending.");
return0;
}
$this->Record=@mysql_fetch_array($this->Query_ID);
$this->Row+=1;
$this->Errno=mysql_errno();
$this->Error=mysql_error();
$stat=is_array($this->Record);
if(!$stat&&$this->Auto_Free){
$this->free();
}
return$stat;
}
/*public:positioninresultset*/
functionseek($pos=0){
$status=@mysql_data_seek($this->Query_ID,$pos);
if($status)
$this->Row=$pos;
else{
$this->halt("seek($pos)failed:resulthas".$this->num_rows()."rows.");
/*halfassedattempttosavetheday,
*butdonotconsiderthisdocumentedoreven
*desireablebehaviour.
*/
@mysql_data_seek($this->Query_ID,$this->num_rows());
$this->Row=$this->num_rows();
return0;
}
return1;
}
/*public:tablelocking*/
functionlock($table,$mode="write"){
$query="locktables";
if(is_array($table)){
while(list($key,$value)=each($table)){
//textkeysare"read","readlocal","write","lowprioritywrite"
if(is_int($key))$key=$mode;
if(strpos($value,",")){
$query.=str_replace(",","$key,",$value)."$key,";
}else{
$query.="$value$key,";
}
}
$query=substr($query,0,-2);
}elseif(strpos($table,",")){
$query.=str_replace(",","$mode,",$table)."$mode";
}else{
$query.="$table$mode";
}
if(!$this->query($query)){
$this->halt("lock()failed.");
returnfalse;
}
$this->locked=true;
returntrue;
}
functionunlock(){
//setbeforeunlocktoavoidpotentialloop
$this->locked=false;
if(!$this->query("unlocktables")){
$this->halt("unlock()failed.");
returnfalse;
}
returntrue;
}
/*public:evaluatetheresult(size,width)*/
functionaffected_rows(){
return@mysql_affected_rows($this->Link_ID);
}
functionnum_rows(){
return@mysql_num_rows($this->Query_ID);
}
functionnum_fields(){
return@mysql_num_fields($this->Query_ID);
}
/*public:shorthandnotation*/
functionnf(){
return$this->num_rows();
}
functionnp(){
print$this->num_rows();
}
functionf($Name){
if(isset($this->Record[$Name])){
return$this->Record[$Name];
}
}
functionp($Name){
if(isset($this->Record[$Name])){
print$this->Record[$Name];
}
}
/*public:sequencenumbers*/
functionnextid($seq_name){
/*ifnocurrentlock,locksequencetable*/
if(!$this->locked){
if($this->lock($this->Seq_Table)){
$locked=true;
}else{
$this->halt("cannotlock".$this->Seq_Table."-hasitbeencreated?");
return0;
}
}
/*getsequencenumberandincrement*/
$q=sprintf("selectnextidfrom%swhereseq_name='%s'",
$this->Seq_Table,
$seq_name);
if(!$this->query($q)){
$this->halt('queryfailedinnextid:'.$q);
return0;
}
/*Nocurrentvalue,makeone*/
if(!$this->next_record()){
$currentid=0;
$q=sprintf("insertinto%svalues('%s',%s)",
$this->Seq_Table,
$seq_name,
$currentid);
if(!$this->query($q)){
$this->halt('queryfailedinnextid:'.$q);
return0;
}
}else{
$currentid=$this->f("nextid");
}
$nextid=$currentid+1;
$q=sprintf("update%ssetnextid='%s'whereseq_name='%s'",
$this->Seq_Table,
$nextid,
$seq_name);
if(!$this->query($q)){
$this->halt('queryfailedinnextid:'.$q);
return0;
}
/*ifnextid()lockedthesequencetable,unlockit*/
if($locked){
$this->unlock();
}
return$nextid;
}
/*public:returntablemetadata*/
functionmetadata($table="",$full=false){
$count=0;
$id=0;
$res=array();
/*
*DuetocompatibilityproblemswithTablewechangedthebehavior
*ofmetadata();
*dependingon$full,metadatareturnsthefollowingvalues:
*
*-fullisfalse(default):
*$result[]:
*[0]["table"]tablename
*[0]["name"]fieldname
*[0]["type"]fieldtype
*[0]["len"]fieldlength
*[0]["flags"]fieldflags
*
*-fullistrue
*$result[]:
*["num_fields"]numberofmetadatarecords
*[0]["table"]tablename
*[0]["name"]fieldname
*[0]["type"]fieldtype
*[0]["len"]fieldlength
*[0]["flags"]fieldflags
*["meta"][fieldname]indexoffieldnamed"fieldname"
*Thislastonecouldbeusedifyouhaveafieldname,butnoindex.
*Test:if(isset($result['meta']['myfield'])){...
*/
//ifno$tablespecified,assumethatweareworkingwithaquery
//result
if($table){
$this->connect();
$id=@mysql_list_fields($this->Database,$table);
if(!$id){
$this->halt("Metadataqueryfailed.");
returnfalse;
}
}else{
$id=$this->Query_ID;
if(!$id){
$this->halt("Noqueryspecified.");
returnfalse;
}
}
$count=@mysql_num_fields($id);
//madethisIFduetoperformance(oneifisfasterthan$countif's)
if(!$full){
for($i=0;$i<$count;$i++){
$res[$i]["table"]=@mysql_field_table($id,$i);
$res[$i]["name"]=@mysql_field_name($id,$i);
$res[$i]["type"]=@mysql_field_type($id,$i);
$res[$i]["len"]=@mysql_field_len($id,$i);
$res[$i]["flags"]=@mysql_field_flags($id,$i);
}
}else{//full
$res["num_fields"]=$count;
for($i=0;$i<$count;$i++){
$res[$i]["table"]=@mysql_field_table($id,$i);
$res[$i]["name"]=@mysql_field_name($id,$i);
$res[$i]["type"]=@mysql_field_type($id,$i);
$res[$i]["len"]=@mysql_field_len($id,$i);
$res[$i]["flags"]=@mysql_field_flags($id,$i);
$res["meta"][$res[$i]["name"]]=$i;
}
}
//freetheresultonlyifwewerecalledonatable
if($table){
@mysql_free_result($id);
}
return$res;
}
/*public:findavailabletablenames*/
functiontable_names(){
$this->connect();
$h=@mysql_query("showtables",$this->Link_ID);
$i=0;
while($info=@mysql_fetch_row($h)){
$return[$i]["table_name"]=$info[0];
$return[$i]["tablespace_name"]=$this->Database;
$return[$i]["database"]=$this->Database;
$i++;
}
@mysql_free_result($h);
return$return;
}
/*private:errorhandling*/
functionhalt($msg){
$this->Error=@mysql_error($this->Link_ID);
$this->Errno=@mysql_errno($this->Link_ID);
if($this->locked){
$this->unlock();
}
if($this->Halt_On_Error=="no")
return;
$this->haltmsg($msg);
if($this->Halt_On_Error!="report")
die("Sessionhalted.");
}
functionhaltmsg($msg){
printf("Databaseerror:%s
n",$msg);
printf("MySQLError:%s(%s)
n",
$this->Errno,
$this->Error);
}
//----------------------------------
//模块:自定义函数
//功能:部分实用的数据库处理方法
//作者:heiyeluren
//时间:2005-12-26
//----------------------------------
/**
*方法:execute($sql)
*功能:执行一条SQL语句，主要针对没有结果集返回的SQL
*参数:$sql需要执行的SQL语句，例如：execute("DELETEFROMtable1WHEREid='1'")
*返回:更新成功返回True，失败返回False
*/
functionexecute($sql)
{
if(empty($sql))
{
$this->error("Invalidparameter");
}
if(!$this->query($sql))
{
returnfalse;
}
returntrue;
}
/**
*方法:get_all($sql)
*功能:获取SQL执行的所有记录
*参数:$sql需要执行的SQL,例如:get_all("SELECT*FROMTable1")
*返回:返回包含所有查询结果的二维数组
*/
functionget_all($sql)
{
$this->query($sql);
$result_array=array();
while($this->next_record())
{
$result_array[]=$this->Record;
}
if(count($result_array)<=0)
{
return0;
}
return$result_array;
}
/**
*方法:get_one($sql)
*功能:获取SQL执行的一条记录
*参数:$sql需要执行的SQL,例如:get_one("SELECT*FROMTable1WHEREid='1'")
*返回:返回包含一条查询结果的一维数组
*/
functionget_one($sql)
{
$this->query($sql);
if(!$this->next_record())
{
return0;
}
return$this->Record;
}
/**
*方法:get_limit($sql,$limit)
*功能:获取SQL执行的指定数量的记录
*参数:
*$sql需要执行的SQL,例如:SELECT*FROMTable1
*$limit需要限制的记录数
*例如需要获取10条记录,get_limit("SELECT*FROMTable1",10);
*
*返回:返回包含所有查询结果的二维数组
*/
functionget_limit($sql,$limit)
{
$this->query($sql);
$result_array=array();
for($i=0;$i<$limit&&$this->next_record();$i++)
{
$result_array[]=$this->Record;
}
if(count($result_array)<=0)
{
return0;
}
return$result_array;
}
/**
*方法:limit_query($sql,$start=0,$offset=20,$order="")
*功能:为分页的获取SQL执行的指定数量的记录
*参数:
*$sql需要执行的SQL,例如:SELECT*FROMTable1
*$start记录的开始数,缺省为0
*$offset记录的偏移量，缺省为20
*$order排序方式，缺省为空，例如：ORDERBYidDESC
*例如需要获取从0到10的记录并且按照ID号倒排,get_limit("SELECT*FROMTable1",0,10,"ORDERBYidDESC");
*
*返回:返回包含所有查询结果的二维数组
*/
functionlimit_query($sql,$start=0,$offset=20,$order="")
{
$sql=$sql."$orderLIMIT$start,$offset";
$this->query($sql);
$result=array();
while($this->next_record())
{
$result[]=$this->Record;
}
if(count($result)<=0)
{
return0;
}
return$result;
}
/**
*方法:count($table,$field="*",$where="")
*功能:统计表中数据总数
*参数:
*$table需要统计的表名
*$field需要统计的字段，默认为*
*$where条件语句，缺省为空
*例如按照ID统计所有年龄小于20岁的用户,count("user_table","id","user_age<20")
*
*返回:返回统计结果的数字
*/
functioncount($table,$field="*",$where="")
{
$sql=(empty($where)?"SELECTCOUNT($field)FROM$table":"SELECTCOUNT($field)FROM$tableWHERE$where");
$result=$this->get_one($sql);
if(!is_array($result))
{
return0;
}
return$result[0];
}
/**
*方法:insert($table,$dataArray)
*功能:插入一条记录到表里
*参数:
*$table需要插入的表名
*$dataArray需要插入字段和值的数组，键为字段名，值为字段值，例如：array("user_name"=>"张三","user_age"=>"20岁");
*例如比如插入用户张三，年龄为20,insert("users",array("user_name"=>"张三","user_age"=>"20岁"))
*
*返回:插入记录成功返回True，失败返回False
*/
functioninsert($table,$dataArray)
{
if(!is_array($dataArray)||count($dataArray)<=0)
{
$this->error("Invalidparameter");
}
while(list($key,$val)=each($dataArray))
{
$field.="$key,";
$value.="'$val',";
}
$field=substr($field,0,-1);
$value=substr($value,0,-1);
$sql="INSERTINTO$table($field)VALUES($value)";
if(!$this->query($sql))
{
returnfalse;
}
returntrue;
}
/**
*方法:update($talbe,$dataArray,$where)
*功能:更新一条记录
*参数:
*$table需要更新的表名
*$dataArray需要更新字段和值的数组，键为字段名，值为字段值，例如：array("user_name"=>"张三","user_age"=>"20岁");
*$where条件语句
*例如比如更新姓名为张三的用户为李四，年龄为21
*update("users",array("user_name"=>"张三","user_age"=>"20岁"),"user_name='张三'")
*
*返回:更新成功返回True，失败返回False
*/
functionupdate($talbe,$dataArray,$where)
{
if(!is_array($dataArray)||count($dataArray)<=0)
{
$this->error("Invalidparameter");
}
while(list($key,$val)=each($dataArray))
{
$value.="$key='$val',";
}
$value=substr($value,0,-1);
$sql="UPDATE$talbeSET$valueWHERE$where";
if(!$this->query($sql))
{
returnfalse;
}
returntrue;
}
/**
*方法:delete($table,$where)
*功能:删除一条记录
*参数:
*$table需要删除记录的表名
*$where需要删除记录的条件语句
*例如比如要删除用户名为张三的用户，delete("users","user_name='张三'")
*
*返回:更新成功返回True，失败返回False
*/
functiondelete($table,$where)
{
if(empty($where))
{
$this->error("Invalidparameter");
}
$sql="DELETEFROM$tableWHERE$where";
if(!$this->query($sql))
{
returnfalse;
}
returntrue;
}
/**
*方法:error($msg="")
*功能:显示错误信息后中止脚本
*参数:$msg需要显示的错误信息
*返回:无返回
*/
functionerror($msg="")
{
echo"Error:$msgn
n";
exit();
}
/**
*方法：get_insert_id()
*功能：获取最后插入的ID
*参数:无参数
*返回：关闭成功返回ID，失败返回0
*/
functionget_insert_id()
{
returnmysql_insert_id($this->Link_ID);
}
/**
*方法：close()
*功能：关闭当前数据库连接
*参数:无参数
*返回：关闭成功返回true，失败返回false
*/
functionclose()
{
returnmysql_close($this->Link_ID);
}
}
?>
