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

php  /  管理员 发布于 7年前   159

本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考，具体如下：

这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。

这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFT JOIN和ORDER 语句

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

类代码：

DATE      : 2006-08-02PRODUCTNAME    : class MyLibSQLGenPRODUCTVERSION  : 1.0.0DESCRIPTION    : class yang berfungsi untuk menggenerate SQLDENPENCIES    : */  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 ;}  }}?> 

更多关于PHP相关内容感兴趣的读者可查看本站专题：《PHP基于pdo操作数据库技巧总结》、《PHP运算与运算符用法总结》、《PHP网络编程技巧总结》、《PHP基本语法入门教程》、《php操作office文档技巧总结(包括word,excel,access,ppt)》、《php日期与时间用法总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》

希望本文所述对大家PHP程序设计有所帮助。

您可能感兴趣的文章:

• PHP基于单例模式实现的mysql类
• php封装的连接Mysql类及用法分析
• 一个php Mysql类 可以参考学习熟悉下
• 十二个常见的PHP+MySql类免费CMS系统
• PHP实现基于mysqli的Model基类完整实例
• PHP使用Mysqli类库实现完美分页效果的方法
• PHP格式化MYSQL返回float类型的方法
• php实现Mysql简易操作类
• php简单操作mysql数据库的类