网站首页  汉语字词  英语词汇  考试资料  写作素材  旧版资料

请输入您要查询的范文：

　

标题 PHP实现的通过参数生成MYSQL语句类完整实例
范文
    本文实例讲述了PHP实现的通过参数生成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  " <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 ;}
      }
    }
    ?> 
    希望本文所述对大家PHP程序设计有所帮助。
随便看

　

在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。

　

Copyright © 2002-2024 cuapp.net All Rights Reserved
更新时间：2025/5/21 3:21:40