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

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