PHP实现的通过参数生成MYSQL语句类完整实例
本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考，具体如下：
这个类可以通过指定的表和字段参数创建SELECT,INSERT,UPDATE和DELETE语句。
这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFTJOIN和ORDER语句
<?php
/********************************************************************
Examplefile
ThisexampleshowshowtousetheMyLibSQLGenclass
TheexampleisbasedonthefollowingMySQLtable:
CREATETABLEcustomer(
idint(10)unsignedNOTNULLauto_increment,
namevarchar(60)NOTNULLdefault'',
addressvarchar(60)NOTNULLdefault'',
cityvarchar(60)NOTNULLdefault'',
PRIMARYKEY(cust_id)
)TYPE=MyISAM;
********************************************************************/
require_once("class_mylib_SQLGen-1.0.php");
$fields=Array("name","address","city");
$values=Array("Fadjar","ResultmangRayaStreet","Jakarta");
$tables=Array("customer");
echo"<b>ResultGenerateInsert</b><br>";
$object=newMyLibSQLGen();
$object->clear_all_assign();//torefreshallpropertybutitnoneedwhenfirsttimeexecute
$object->setFields($fields);
$object->setValues($values);
$object->setTables($tables);
if(!$object->getInsertSQL()){echo$object->Error;exit;}
else{$sql=$object->Result;echo$sql."<br>";}
echo"<b>ResultGenerateUpdate</b><br>";
$fields=Array("name","address","city");
$values=Array("Fadjar","ResultmangRayaStreet","Jakarta");
$tables=Array("customer");
$id=1;
$conditions[0]["condition"]="id='$id'";
$conditions[0]["connection"]="";
$object->clear_all_assign();
$object->setFields($fields);
$object->setValues($values);
$object->setTables($tables);
$object->setConditions($conditions);
if(!$object->getUpdateSQL()){echo$object->Error;exit;}
else{$sql=$object->Result;echo$sql."<br>";}
echo"<b>ResultGenerateDelete</b><br>";
$tables=Array("customer");
$conditions[0]["condition"]="id='1'";
$conditions[0]["connection"]="OR";
$conditions[1]["condition"]="id='2'";
$conditions[1]["connection"]="OR";
$conditions[2]["condition"]="id='4'";
$conditions[2]["connection"]="";
$object->clear_all_assign();
$object->setTables($tables);
$object->setConditions($conditions);
if(!$object->getDeleteSQL()){echo$object->Error;exit;}
else{$sql=$object->Result;echo$sql."<br>";}
echo"<b>ResultGenerateList</b><br>";
$fields=Array("id","name","address","city");
$tables=Array("customer");
$id=1;
$conditions[0]["condition"]="id='$id'";
$conditions[0]["connection"]="";
$object->clear_all_assign();
$object->setFields($fields);
$object->setTables($tables);
$object->setConditions($conditions);
if(!$object->getQuerySQL()){echo$object->Error;exit;}
else{$sql=$object->Result;echo$sql."<br>";}
echo"<b>ResultGenerateListwithsearchonallfields</b><br>";
$fields=Array("id","name","address","city");
$tables=Array("customer");
$id=1;
$search="FadjarNurswanto";
$object->clear_all_assign();
$object->setFields($fields);
$object->setTables($tables);
$object->setSearch($search);
if(!$object->getQuerySQL()){echo$object->Error;exit;}
else{$sql=$object->Result;echo$sql."<br>";}
echo"<b>ResultGenerateListwithsearchonsomefields</b><br>";
$fields=Array("id","name","address","city");
$tables=Array("customer");
$id=1;
$search=Array(
"name"=>"FadjarNurswanto",
"address"=>"TomangRaya"
);
$object->clear_all_assign();
$object->setFields($fields);
$object->setTables($tables);
$object->setSearch($search);
if(!$object->getQuerySQL()){echo$object->Error;exit;}
else{$sql=$object->Result;echo$sql."<br>";}
?>
类代码：
<?php
/*
CreatedBy:FadjarNurswanto<fajr_n@rindudendam.net>
DATE:2006-08-02
PRODUCTNAME:classMyLibSQLGen
PRODUCTVERSION:1.0.0
DESCRIPTION:classyangberfungsiuntukmenggenerateSQL
DENPENCIES:
*/
classMyLibSQLGen
{
var$Result;
var$Tables=Array();
var$Values=Array();
var$Fields=Array();
var$Conditions=Array();
var$Condition;
var$LeftJoin=Array();
var$Search;
var$Sort="ASC";
var$Order;
var$Error;
functionMyLibSQLGen(){}
functionBuildCondition()
{
$funct="BuildCondition";
$className=get_class($this);
$conditions=$this->getConditions();
if(!$conditions){$this->dbgDone($funct);returntrue;}
if(!is_array($conditions))
{
$this->Error="$className::$functVariableconditionsnotArray";
return;
}
for($i=0;$i<count($conditions);$i++)
{
$this->Condition.=$conditions[$i]["condition"]."".$conditions[$i]["connection"]."";
}
returntrue;
}
functionBuildLeftJoin()
{
$funct="BuildLeftJoin";
$className=get_class($this);
if(!$this->getLeftJoin()){$this->Error="$className::$functPropertyLeftJoinwasempty";return;}
$LeftJoinVars=$this->getLeftJoin();
$hasil=false;
foreach($LeftJoinVarsas$LeftJoinVar)
{
@$hasil.="LEFTJOIN".$LeftJoinVar["table"];
foreach($LeftJoinVar["on"]as$var)
{
@$condvar.=$var["condition"]."".$var["connection"]."";
}
$hasil.="ON(".$condvar.")";
unset($condvar);
}
$this->ResultLeftJoin=$hasil;
returntrue;
}
functionBuildOrder()
{
$funct="BuildOrder";
$className=get_class($this);
if(!$this->getOrder()){$this->Error="$className::$functPropertyOrderwasempty";return;}
if(!$this->getFields()){$this->Error="$className::$functPropertyFieldswasempty";return;}
$Fields=$this->getFields();
$Orders=$this->getOrder();
if(ereg(",",$Orders)){$Orders=explode(",",$Order);}
if(!is_array($Orders)){$Orders=Array($Orders);}
foreach($Ordersas$Order)
{
if(!is_numeric($Order)){$this->Error="$className::$functPropertyOrdernotNumeric";return;}
if($Order>count($this->Fields)){$this->Error="$className::$functMaxvalueofpropertySortis".count($this->Fields);return;}
@$xorder.=$Fields[$Order].",";
}
$this->ResultOrder="ORDERBY".substr($xorder,0,-1);
returntrue;
}
functionBuildSearch()
{
$funct="BuildSearch";
$className=get_class($this);
if(!$this->getSearch()){$this->Error="$className::$functPropertySearchwasempty";return;}
if(!$this->getFields()){$this->Error="$className::$functPropertyFieldswasempty";return;}
$Fields=$this->getFields();
$xvalue=$this->getSearch();
if(is_array($xvalue))
{
foreach($Fieldsas$field)
{
if(@$xvalue[$field])
{
$Values=explode("",$xvalue[$field]);
foreach($Valuesas$Value)
{
@$hasil.=$field."LIKE'%".$Value."%'OR";
}
if($hasil)
{
@$hasil_final.="(".substr($hasil,0,-4).")AND";
unset($hasil);
}
}
}
$hasil=$hasil_final;
}
else
{
foreach($Fieldsas$field)
{
$Values=explode("",$xvalue);
foreach($Valuesas$Value)
{
@$hasil.=$field."LIKE'%".$Value."%'OR";
}
}
}
$this->ResultSearch=substr($hasil,0,-4);
returntrue;
}
functionclear_all_assign()
{
$this->Result=null;
$this->ResultSearch=null;
$this->ResultLeftJoin=null;
$this->Result=null;
$this->Tables=Array();
$this->Values=Array();
$this->Fields=Array();
$this->Conditions=Array();
$this->Condition=null;
$this->LeftJoin=Array();
$this->Sort="ASC";
$this->Order=null;
$this->Search=null;
$this->fieldSQL=null;
$this->valueSQL=null;
$this->partSQL=null;
$this->Error=null;
returntrue;
}
functionCombineFieldValue($manual=false)
{
$funct="CombineFieldsPostVar";
$className=get_class($this);
$fields=$this->getFields();
$values=$this->getValues();
if(!is_array($fields))
{
$this->Error="$className::$functVariablefieldsnotArray";
return;
}
if(!is_array($values))
{
$this->Error="$className::$functVariablevaluesnotArray";
return;
}
if(count($fields)!=count($values))
{
$this->Error="$className::$functCountoffieldsandvaluesnotmatch";
return;
}
for($i=0;$i<count($fields);$i++)
{
@$this->fieldSQL.=$fields[$i].",";
if($fields[$i]=="pwd"||$fields[$i]=="password"||$fields[$i]=="pwd")
{
@$this->valueSQL.="password('".$values[$i]."'),";
@$this->partSQL.=$fields[$i]."=password('".$values[$i]."'),";
}
else
{
if(is_numeric($values[$i]))
{
@$this->valueSQL.=$values[$i].",";
@$this->partSQL.=$fields[$i]."=".$values[$i].",";
}
else
{
@$this->valueSQL.="'".$values[$i]."',";
@$this->partSQL.=$fields[$i]."='".$values[$i]."',";
}
}
}
$this->fieldSQL=substr($this->fieldSQL,0,-1);
$this->valueSQL=substr($this->valueSQL,0,-1);
$this->partSQL=substr($this->partSQL,0,-1);
returntrue;
}
functiongetDeleteSQL()
{
$funct="getDeleteSQL";
$className=get_class($this);
$Tables=$this->getTables();
if(!$Tables||!count($Tables))
{
$this->dbgFailed($funct);
$this->Error="$className::$functTablewasempty";
return;
}
for($i=0;$i<count($Tables);$i++)
{
@$Table.=$Tables[$i].",";
}
$Table=substr($Table,0,-1);
$sql="DELETEFROM".$Table;
if($this->getConditions())
{
if(!$this->BuildCondition()){$this->dbgFailed($funct);return;}
$sql.="WHERE".$this->getCondition();
}
$this->Result=$sql;
returntrue;
}
functiongetInsertSQL()
{
$funct="getInsertSQL";
$className=get_class($this);
if(!$this->getValues()){$this->Error="$className::$functPropertyValueswasempty";return;}
if(!$this->getFields()){$this->Error="$className::$functPropertyFieldswasempty";return;}
if(!$this->getTables()){$this->Error="$className::$functPropertyTableswasempty";return;}
if(!$this->CombineFieldValue()){$this->dbgFailed($funct);return;}
$Tables=$this->getTables();
$sql="INSERTINTO".$Tables[0]."(".$this->fieldSQL.")VALUES(".$this->valueSQL.")";
$this->Result=$sql;
returntrue;
}
functiongetUpdateSQL()
{
$funct="getUpdateSQL";
$className=get_class($this);
if(!$this->getValues()){$this->Error="$className::$functPropertyValueswasempty";return;}
if(!$this->getFields()){$this->Error="$className::$functPropertyFieldswasempty";return;}
if(!$this->getTables()){$this->Error="$className::$functPropertyTableswasempty";return;}
if(!$this->CombineFieldValue()){$this->dbgFailed($funct);return;}
if(!$this->BuildCondition()){$this->dbgFailed($funct);return;}
$Tables=$this->getTables();
$sql="UPDATE".$Tables[0]."SET".$this->partSQL."WHERE".$this->getCondition();
$this->Result=$sql;
returntrue;
}
functiongetQuerySQL()
{
$funct="getQuerySQL";
$className=get_class($this);
if(!$this->getFields()){$this->Error="$className::$functPropertyFieldswasempty";return;}
if(!$this->getTables()){$this->Error="$className::$functPropertyTableswasempty";return;}
$Fields=$this->getFields();
$Tables=$this->getTables();
foreach($Fieldsas$Field){@$sql_raw.=$Field.",";}
foreach($Tablesas$Table){@$sql_table.=$Table.",";}
$this->Result="SELECT".substr($sql_raw,0,-1)."FROM".substr($sql_table,0,-1);
if($this->getLeftJoin())
{
if(!$this->BuildLeftJoins()){$this->dbgFailed($funct);return;}
$this->Result.="".$this->ResultLeftJoin;
}
if($this->getConditions())
{
if(!$this->BuildCondition()){$this->dbgFailed($funct);return;}
$this->Result.="WHERE(".$this->Condition.")";
}
if($this->getSearch())
{
if(!$this->BuildSearch()){$this->dbgFailed($funct);return;}
if($this->ResultSearch)
{
if(eregi("WHERE",$this->Result)){$this->Result.="AND".$this->ResultSearch;}
else{$this->Result.="WHERE".$this->ResultSearch;}
}
}
if($this->getOrder())
{
if(!$this->BuildOrder()){$this->dbgFailed($funct);return;}
$this->Result.="".$this->ResultOrder;
}
if($this->getSort())
{
if(@$this->ResultOrder)
{
$this->Result.="".$this->getSort();
}
}
returntrue;
}
functiongetCondition(){return@$this->Condition;}
functiongetConditions(){if(count(@$this->Conditions)&&is_array(@$this->Conditions)){return@$this->Conditions;}}
functiongetFields(){if(count(@$this->Fields)&&is_array(@$this->Fields)){return@$this->Fields;}}
functiongetLeftJoin(){if(count(@$this->LeftJoin)&&is_array(@$this->LeftJoin)){return@$this->LeftJoin;}}
functiongetOrder(){return@$this->Order;}
functiongetSearch(){return@$this->Search;}
functiongetSort(){return@$this->Sort;}
functiongetTables(){if(count(@$this->Tables)&&is_array(@$this->Tables)){return@$this->Tables;}}
functiongetValues(){if(count(@$this->Values)&&is_array(@$this->Values)){return@$this->Values;}}
functionsetCondition($input){$this->Condition=$input;}
functionsetConditions($input)
{
if(is_array($input)){$this->Conditions=$input;}
else{$this->Error=get_class($this)."::setConditionsParameterinputnotarray";return;}
}
functionsetFields($input)
{
if(is_array($input)){$this->Fields=$input;}
else{$this->Error=get_class($this)."::setFieldsParameterinputnotarray";return;}
}
functionsetLeftJoin($input)
{
if(is_array($input)){$this->LeftJoin=$input;}
else{$this->Error=get_class($this)."::setFieldsParameterinputnotarray";return;}
}
functionsetOrder($input){$this->Order=$input;}
functionsetSearch($input){$this->Search=$input;}
functionsetSort($input){$this->Sort=$input;}
functionsetTables($input)
{
if(is_array($input)){$this->Tables=$input;}
else{$this->Error=get_class($this)."::setTablesParameterinputnotarray";return;}
}
functionsetValues($input)
{
if(is_array($input)){$this->Values=$input;}
else{$this->Error=get_class($this)."::setValuesParameterinputnotarray";return;}
}
}
?>
更多关于PHP相关内容感兴趣的读者可查看本站专题：《PHP基于pdo操作数据库技巧总结》、《PHP运算与运算符用法总结》、《PHP网络编程技巧总结》、《PHP基本语法入门教程》、《php操作office文档技巧总结(包括word,excel,access,ppt)》、《php日期与时间用法总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》
希望本文所述对大家PHP程序设计有所帮助。