加强版phplib的DB类

<div class="codetitle"><a style="CURSOR: pointer" data="35555" class="copybut" id="copybut35555" onclick="doCopy('code35555')"> 代码如下:<div class="codebody" id="code35555">
<?php
/****
为了便于自己的开发，又不想使用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="SELECTFROMtable1";
$all_record=$db->get_all($sql); //获取一条
$sql="SELECTFROMtable1WHEREid='1'";
$one_row=$db->get_one($sql); //分页查询，提取20条记录
$sql="SELECTFROMtable1";
$page_record=$db->limit_query($sql,$start=0,$offset=20,$order="ORDERBYidDESC"); //提取指定数目的记录
$sql="SELECTFROMtable1";
$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",
"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,$Password);
}
if(!$this->Link_ID){
$this->halt("connect($Host,$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,-2);
}elseif(strpos($table,"$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)",
$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'",
$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["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("</td></tr></table>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("SELECTFROMTable1")
返回:返回包含所有查询结果的二维数组
/
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("SELECTFROMTable1WHEREid='1'")
返回:返回包含一条查询结果的一维数组
/
functionget_one($sql)
{
$this->query($sql);
if(!$this->next_record())
{
return0;
}
return$this->Record;
} /
方法:get_limit($sql,$limit)
功能:获取SQL执行的指定数量的记录
参数:
$sql需要执行的SQL,例如:SELECTFROMTable1
$limit需要限制的记录数
例如需要获取10条记录,get_limit("SELECTFROMTable1",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,$order="")
功能:为分页的获取SQL执行的指定数量的记录
参数:
$sql需要执行的SQL,例如:SELECTFROMTable1
$start记录的开始数,缺省为0
$offset记录的偏移量，缺省为20
$order排序方式，缺省为空，例如：ORDERBYidDESC
例如需要获取从0到10的记录并且按照ID号倒排,10,"ORDERBYidDESC");

返回:返回包含所有查询结果的二维数组
/
functionlimit_query($sql,$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","user_age<20")

返回:返回统计结果的数字
/
functioncount($table,$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,-1);
$value=substr($value,-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","user_age"=>"20岁"),"user_name='张三'")

返回:更新成功返回True，失败返回False
/
functionupdate($talbe,$where)
{
if(!is_array($dataArray)||count($dataArray)<=0)
{
$this->error("Invalidparameter");
}
while(list($key,$val)=each($dataArray))
{
$value.="$key='$val',";
}
$value=substr($value,-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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!