Go homepage(回首页)
Upload pictures (上传图片)
Write articles (发文字帖)

The author:(作者)归海一刀
published in(发表于) 2014/2/17 7:32:15
PHP实例：PHP通过参数来生成MYSQL语句类_[PHP教程]

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

/* *******************************************************************
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 . "
" ;}
?>

类代码:
/*
Created By : Fadjar Nurswanto <fajr_n@rindudendam.net>
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 < count ( conditions ); 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 < 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 );
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 < count ( Tables ); 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 ;}
}
}
?>

赞