加入收藏 | 设为首页 | 会员中心 | 我要投稿 汽车网 （https://www.0577qiche.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > PHP教程 > 正文

实例讲述PHP实现的通过参数生成MYSQL语句类

发布时间：2023-10-09 10:57:46 所属栏目：PHP教程 来源：
导读：本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考，具体如下：

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

这个类可以创建SQL语句的WHERE条件，像
本文实例讲述了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 ); 
//Cuoxin.com 
 $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 ;} 
 
  } 
 
} 
 
?>  
 
</fajr_n@rindudendam.net> 
 

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的出色浏览效果

        ICP备案：浙ICP备07501134号