PHP实例：PHP通过参数来生成MYSQL语句类

 

这个类可以通过具有参数的数组来构建MySQL查询语句。 
这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。 
这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFT JOIN和ORDER 语句。 
例子:

php 
/* ******************************************************************* 
Example file 
This example shows how to use the MyLibSQLGen class 

The example is based on the following MySQL table: 

CREATE TABLE customer ( 
id int(10) unsigned NOT NULL auto_increment, 
name varchar(60) NOT NULL default ”, 
address varchar(60) NOT NULL default ”, 
city varchar(60) NOT NULL default ”, 
PRIMARY KEY (cust_id) 
) TYPE=MyISAM; 

******************************************************************* */ 

require_once ( ” class_mylib_SQLGen-1.0.php ” ); 

fields = Array ( ” name ” , ” address ” , ” city ” ); 
values = Array ( ” Fadjar ” , ” Resultmang Raya Street ” , ” Jakarta ” ); 
tables = Array ( ” customer ” ); 

echo ” Result Generate Insert
” ; 
object = new MyLibSQLGen(); 
object -> clear_all_assign(); // to refresh all property but it no need when first time execute 
object -> setFields( fields ); 
object -> setValues( values ); 
object -> setTables( tables ); 

if ( ! object -> getInsertSQL()){ echo object -> Error; exit ;} 
else { sql = object -> Result; echo sql . ”
” ;} 


echo ” Result Generate Update
” ; 
fields = Array ( ” name ” , ” address ” , ” city ” ); 
values = Array ( ” Fadjar ” , ” Resultmang Raya Street ” , ” 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 . ”
” ;} 

echo ” Result Generate Delete
” ; 
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 . ”
” ;} 

echo ” Result Generate List
” ; 
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 . ”
” ;} 

echo ” Result Generate List with search on all fields
” ; 
fields = Array ( ” id ” , ” name ” , ” address ” , ” city ” ); 
tables = Array ( ” customer ” ); 
id = 1 ; 
search = ” Fadjar Nurswanto ” ; 
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 . ”
” ;} 

echo ” Result Generate List with search on some fields
” ; 
fields = Array ( ” id ” , ” name ” , ” address ” , ” city ” ); 
tables = Array ( ” customer ” ); 
id = 1 ; 
search = Array ( 
” name ” => ” Fadjar Nurswanto ” , 
” address ” => ” Tomang Raya ” 
); 

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

类代码: 
php 
/* 
Created By : Fadjar Nurswanto  
DATE : 2006-08-02 
PRODUCTNAME : class MyLibSQLGen 
PRODUCTVERSION : 1.0.0 
DESCRIPTION : class yang berfungsi untuk menggenerate SQL 
DENPENCIES : 
*/ 
class MyLibSQLGen 
{ 
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 ; 

function MyLibSQLGen(){} 
function BuildCondition() 
{ 
funct = ” BuildCondition ” ; 
className = get_class ( this ); 
conditions = this -> getConditions(); 
if ( ! conditions ){ this -> dbgDone( funct ); return true ;} 
if ( ! is_array ( conditions )) 
{ 
this -> Error = ” className::funct \nVariable conditions not Array ” ; 
return ; 
} 
for ( i = 0 ; i { 
this -> Condition .= conditions [ i ][ " condition " ] . ” ” . conditions [ i ][ " connection " ] . ” ” ; 
} 
return true ; 
} 
function BuildLeftJoin() 
{ 
funct = ” BuildLeftJoin ” ; 
className = get_class ( this ); 
if ( ! this -> getLeftJoin()){ this -> Error = ” className::funct \nProperty LeftJoin was empty ” ; return ;} 

LeftJoinVars = this -> getLeftJoin(); 

hasil = false ; 
foreach ( LeftJoinVars as LeftJoinVar ) 
{ 
@ hasil .= ” LEFT JOIN ” . LeftJoinVar [ " table " ]; 
foreach ( LeftJoinVar [ " on " ] as var ) 
{ 
@ condvar .= var [ " condition " ] . ” ” . var [ " connection " ] . ” ” ; 
} 
hasil .= ” ON ( ” . condvar . ” ) ” ; 
unset ( condvar ); 
} 

this -> ResultLeftJoin = hasil ; 

return true ; 
} 
function BuildOrder() 
{ 
funct = ” BuildOrder ” ; 
className = get_class ( this ); 
if ( ! this -> getOrder()){ this -> Error = ” className::funct \nProperty Order was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 

Fields = this -> getFields(); 
Orders = this -> getOrder(); 
if ( ereg ( ” , ” , Orders )){ Orders = explode ( ” , ” , Order );} 
if ( ! is_array ( Orders )){ Orders = Array ( Orders );} 

foreach ( Orders as Order ) 
{ 
if ( ! is_numeric ( Order )){ this -> Error = ” className::funct \nProperty Order not Numeric ” ; return ;} 
if ( Order > count ( this -> Fields)){ this -> Error = ” className::funct \nMax value of property Sort is ” . count ( this -> Fields); return ;} 

@ xorder .= Fields [ Order ] . ” , ” ; 
} 

this -> ResultOrder = ” ORDER BY ” . substr ( xorder , 0 ,- 1 ); 

return true ; 
} 
function BuildSearch() 
{ 
funct = ” BuildSearch ” ; 
className = get_class ( this ); 

if ( ! this -> getSearch()){ this -> Error = ” className::funct \nProperty Search was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 

Fields = this -> getFields(); 
xvalue = this -> getSearch(); 

if ( is_array ( xvalue )) 
{ 
foreach ( Fields as field ) 
{ 
if (@ xvalue [ field ]) 
{ 
Values = explode ( ” ” , xvalue [ field ]); 
foreach ( Values as Value ) 
{ 
@ hasil .= field . ” LIKE ‘% ” . Value . ” %’ OR ” ; 
} 
if ( hasil ) 
{ 
@ hasil_final .= ” ( ” . substr ( hasil , 0 ,- 4 ) . ” ) AND ” ; 
unset ( hasil ); 
} 
} 
} 
hasil = hasil_final ; 
} 
else 
{ 
foreach ( Fields as field ) 
{ 
Values = explode ( ” ” , xvalue ); 
foreach ( Values as Value ) 
{ 
@ hasil .= field . ” LIKE ‘% ” . Value . ” %’ OR ” ; 
} 
} 
} 

this -> ResultSearch = substr ( hasil , 0 ,- 4 ); 
return true ; 
} 
function clear_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 ; 
return true ; 
} 
function CombineFieldValue( manual = false ) 
{ 
funct = ” CombineFieldsPostVar ” ; 
className = get_class ( this ); 
fields = this -> getFields(); 
values = this -> getValues(); 
if ( ! is_array ( fields )) 
{ 
this -> Error = ” className::funct \nVariable fields not Array ” ; 
return ; 
} 
if ( ! is_array ( values )) 
{ 
this -> Error = ” className::funct \nVariable values not Array ” ; 
return ; 
} 
if ( count ( fields ) != count ( values )) 
{ 
this -> Error = ” className::funct \nCount of fields and values not match ” ; 
return ; 
} 
for ( i = 0 ; 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 ); 
return true ; 
} 
function getDeleteSQL() 
{ 
funct = ” getDeleteSQL ” ; 
className = get_class ( this ); 
Tables = this -> getTables(); 
if ( ! Tables ! count ( Tables )) 
{ 
this -> dbgFailed( funct ); 
this -> Error = ” className::funct \nTable was empty ” ; 
return ; 
} 
for ( i = 0 ; i { 
@ Table .= Tables [ i ] . ” , ” ; 
} 
Table = substr ( Table , 0 ,- 1 ); 

sql = ” DELETE FROM ” . Table ; 

if ( this -> getConditions()) 
{ 
if ( ! this -> BuildCondition()){ this -> dbgFailed( funct ); return ;} 
sql .= ” WHERE ” . this -> getCondition(); 
} 
this -> Result = sql ; 
return true ; 
} 
function getInsertSQL() 
{ 
funct = ” getInsertSQL ” ; 
className = get_class ( this ); 
if ( ! this -> getValues()){ this -> Error = ” className::funct \nProperty Values was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 
if ( ! this -> getTables()){ this -> Error = ” className::funct \nProperty Tables was empty ” ; return ;} 

if ( ! this -> CombineFieldValue()){ this -> dbgFailed( funct ); return ;} 
Tables = this -> getTables(); 

sql = ” INSERT INTO ” . Tables [ 0 ] . ” ( ” . this -> fieldSQL . ” ) VALUES ( ” . this -> valueSQL . ” ) ” ; 

this -> Result = sql ; 

return true ; 
} 
function getUpdateSQL() 
{ 
funct = ” getUpdateSQL ” ; 
className = get_class ( this ); 

if ( ! this -> getValues()){ this -> Error = ” className::funct \nProperty Values was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 
if ( ! this -> getTables()){ this -> Error = ” className::funct \nProperty Tables was empty ” ; 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 ; 

return true ; 
} 
function getQuerySQL() 
{ 
funct = ” getQuerySQL ” ; 
className = get_class ( this ); 

if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 
if ( ! this -> getTables()){ this -> Error = ” className::funct \nProperty Tables was empty ” ; return ;} 

Fields = this -> getFields(); 
Tables = this -> getTables(); 
foreach ( Fields as Field ){@ sql_raw .= Field . ” , ” ;} 
foreach ( Tables as 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(); 
} 
} 

return true ; 
} 

function getCondition(){ return @ this -> Condition;} 
function getConditions(){ if ( count (@ this -> Conditions) && is_array (@ this -> Conditions)){ return @ this -> Conditions;}} 
function getFields(){ if ( count (@ this -> Fields) && is_array (@ this -> Fields)){ return @ this -> Fields;}} 
function getLeftJoin(){ if ( count (@ this -> LeftJoin) && is_array (@ this -> LeftJoin)){ return @ this -> LeftJoin;}} 
function getOrder(){ return @ this -> Order;} 
function getSearch(){ return @ this -> Search;} 
function getSort(){ return @ this -> Sort ;} 
function getTables(){ if ( count (@ this -> Tables) && is_array (@ this -> Tables)){ return @ this -> Tables;}} 
function getValues(){ if ( count (@ this -> Values) && is_array (@ this -> Values)){ return @ this -> Values;}} 

function setCondition( input ){ this -> Condition = input ;} 
function setConditions( input ) 
{ 
if ( is_array ( input )){ this -> Conditions = input ;} 
else { this -> Error = get_class ( this ) . ” ::setConditions \nParameter input not array ” ; return ;} 
} 
function setFields( input ) 
{ 
if ( is_array ( input )){ this -> Fields = input ;} 
else { this -> Error = get_class ( this ) . ” ::setFields \nParameter input not array ” ; return ;} 
} 
function setLeftJoin( input ) 
{ 
if ( is_array ( input )){ this -> LeftJoin = input ;} 
else { this -> Error = get_class ( this ) . ” ::setFields \nParameter input not array ” ; return ;} 
} 
function setOrder( input ){ this -> Order = input ;} 
function setSearch( input ){ this -> Search = input ;} 
function setSort( input ){ this -> Sort = input ;} 
function setTables( input ) 
{ 
if ( is_array ( input )){ this -> Tables = input ;} 
else { this -> Error = get_class ( this ) . ” ::setTables \nParameter input not array ” ; return ;} 
} 
function setValues( input ) 
{ 
if ( is_array ( input )){ this -> Values = input ;} 
else { this -> Error = get_class ( this ) . ” ::setValues \nParameter input not array ” ; return ;} 
} 
} 
?>