PHP实现的通过参数生成MYSQL语句类完整实例，mysql语句

<?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 Variable 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 Property 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 Property Order was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property 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 Property Order not Numeric " ; return ;} if ( $Order > count ( $this -> Fields)){ $this -> Error = " $className::$funct Max 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 Property Search was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property 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 Variable fields not Array " ; return ; } if ( ! is_array ( $values )) { $this -> Error = " $className::$funct Variable values not Array " ; return ; } if ( count ( $fields ) != count ( $values )) { $this -> Error = " $className::$funct Count 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 Table 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 Property Values was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property 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 Property Values was empty " ; return ;} if ( ! $this -> getFields()){ $this -> Error = " $className::$funct Property Fields was empty " ; return ;} if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property 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 Property Fields was empty " ; return ;} if ( ! $this -> getTables()){ $this -> Error = " $className::$funct Property 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 Parameter input not array " ; return ;} } function setFields( $input ) { if ( is_array ( $input )){ $this -> Fields = $input ;} else { $this -> Error = get_class ( $this ) . " ::setFields Parameter input not array " ; return ;} } function setLeftJoin( $input ) { if ( is_array ( $input )){ $this -> LeftJoin = $input ;} else { $this -> Error = get_class ( $this ) . " ::setFields Parameter 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 Parameter input not array " ; return ;} } function setValues( $input ) { if ( is_array ( $input )){ $this -> Values = $input ;} else { $this -> Error = get_class ( $this ) . " ::setValues Parameter input not array " ; return ;} } } ?>