찾다
집php教程php手册PHP实现的通过参数生成MYSQL语句类完整实例，mysql语句

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 13, 2016 am 08:42 AM
mysqlphp

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

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

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

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

<&#63;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> " ;}
&#63;>

类代码：

<&#63;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 ;}
  }
}
&#63;>

更多关于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数据库的类
성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

Video Face Swap

Video Face Swap

완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!

더보기

인기 기사

Windows 11 KB5054979의 새로운 기능 및 업데이트 문제를 해결하는 방법
4 몇 주 전ByDDD
KB5055523을 수정하는 방법 Windows 11에 설치되지 않습니까?
3 몇 주 전ByDDD
Inzoi : 학교 및 대학에 지원하는 방법
1 몇 달 전ByDDD
KB5055518을 수정하는 방법 Windows 10에 설치되지 않습니까?
3 몇 주 전ByDDD
Atomfall에서 사이트 사무실 키를 찾을 위치
1 몇 달 전ByDDD
더보기

뜨거운 도구

SecList

SecList

SecLists는 최고의 보안 테스터의 동반자입니다. 보안 평가 시 자주 사용되는 다양한 유형의 목록을 한 곳에 모아 놓은 것입니다. SecLists는 보안 테스터에게 필요할 수 있는 모든 목록을 편리하게 제공하여 보안 테스트를 더욱 효율적이고 생산적으로 만드는 데 도움이 됩니다. 목록 유형에는 사용자 이름, 비밀번호, URL, 퍼징 페이로드, 민감한 데이터 패턴, 웹 셸 등이 포함됩니다. 테스터는 이 저장소를 새로운 테스트 시스템으로 간단히 가져올 수 있으며 필요한 모든 유형의 목록에 액세스할 수 있습니다.

Atom Editor Mac 버전 다운로드

Atom Editor Mac 버전 다운로드

가장 인기 있는 오픈 소스 편집기

에디트플러스 중국어 크랙 버전

에디트플러스 중국어 크랙 버전

작은 크기, 구문 강조, 코드 프롬프트 기능을 지원하지 않음

PhpStorm 맥 버전

PhpStorm 맥 버전

최신(2018.2.1) 전문 PHP 통합 개발 도구

DVWA

DVWA

DVWA(Damn Vulnerable Web App)는 매우 취약한 PHP/MySQL 웹 애플리케이션입니다. 주요 목표는 보안 전문가가 법적 환경에서 자신의 기술과 도구를 테스트하고, 웹 개발자가 웹 응용 프로그램 보안 프로세스를 더 잘 이해할 수 있도록 돕고, 교사/학생이 교실 환경 웹 응용 프로그램에서 가르치고 배울 수 있도록 돕는 것입니다. 보안. DVWA의 목표는 다양한 난이도의 간단하고 간단한 인터페이스를 통해 가장 일반적인 웹 취약점 중 일부를 연습하는 것입니다. 이 소프트웨어는

더보기

뜨거운 주제

Gmail 이메일의 로그인 입구는 어디에 있나요?
7911
15
자바 튜토리얼
1652
14
Cakephp 튜토리얼
1411
52
라라벨 튜토리얼
1303
25
PHP 튜토리얼
1248
29
더보기