本文實(shí)例講述了PHP實(shí)現(xiàn)的通過參數(shù)生成MYSQL語句類。分享給大家供大家參考，具體如下：

這個(gè)類可以通過指定的表和字段參數(shù)創(chuàng)建SELECT ,INSERT , UPDATE 和 DELETE 語句。

這個(gè)類可以創(chuàng)建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  " <b>Result Generate Insert</b><br> " ;

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

 echo  " <b>Result Generate Update</b><br> " ;

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

 echo  " <b>Result Generate Delete</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>Result Generate List</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>Result Generate List with search on all fields</b><br> " ;

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

 echo  " <b>Result Generate List with search on some fields</b><br> " ;

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

?> 

類代碼：

<?php

 /* 

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 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 ;}

  }

}

?> 

希望本文所述對(duì)大家PHP程序設(shè)計(jì)有所幫助。