登录
当前位置： 首页 > 编程笔记 >

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

苏昂雄
2023-03-14
本文向大家介绍PHP实现的通过参数生成MYSQL语句类完整实例，包括了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相关内容感兴趣的读者可查看本站专题：《PHP基于pdo操作数据库技巧总结》、《PHP运算与运算符用法总结》、《PHP网络编程技巧总结》、《PHP基本语法入门教程》、《php操作office文档技巧总结(包括word,excel,access,ppt)》、《php日期与时间用法总结》、《php面向对象程序设计入门教程》、《php字符串(string)用法总结》、《php+mysql数据库操作入门教程》及《php常见数据库操作技巧汇总》

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

 类似资料：
  • PHP+Mysql实现多关键字与多字段生成SQL语句的函数

    本文向大家介绍PHP+Mysql实现多关键字与多字段生成SQL语句的函数，包括了PHP+Mysql实现多关键字与多字段生成SQL语句的函数的使用技巧和注意事项，需要的朋友参考一下 本文实例讲述了PHP+Mysql实现多关键字与多字段生成SQL语句的函数的方法。分享给大家供大家参考。具体实现方法如下： 先看实例: 生成: $keyword由POST或者GET获得.按空格分开 可以多字段去查找. 实现

  • php+mysql结合Ajax实现点赞功能完整实例

    本文向大家介绍php+mysql结合Ajax实现点赞功能完整实例，包括了php+mysql结合Ajax实现点赞功能完整实例的使用技巧和注意事项，需要的朋友参考一下 本文实例讲述了php+mysql结合Ajax实现点赞功能的方法。分享给大家供大家参考。具体如下： 要实现点赞功能，有多种实现方式，这里总结一下利用Ajax，php和mysql来实现点赞的数据的功能。具体步骤如下： 一、页面中的HTML代

  • C#实现Access通用访问类OleDbHelper完整实例

    本文向大家介绍C#实现Access通用访问类OleDbHelper完整实例，包括了C#实现Access通用访问类OleDbHelper完整实例的使用技巧和注意事项，需要的朋友参考一下 本文实例讲述了C#实现Access通用访问类OleDbHelper。分享给大家供大家参考，具体如下： 最近在做一个项目数据库用的是Access,第一次使用Access数据库，刚开始做有些不顺，数据库的操作和SqlSe

  • 基于php实现的php代码加密解密类完整实例

    本文向大家介绍基于php实现的php代码加密解密类完整实例，包括了基于php实现的php代码加密解密类完整实例的使用技巧和注意事项，需要的朋友参考一下 本文实例讲述了基于php实现的php代码加密解密类。分享给大家供大家参考，具体如下： php 代码加密类，大家可以根据自己的需求进行修改，原类如下，该实例在ubuntu下测试没有问题。 PS：关于加密解密感兴趣的朋友还可以参考本站在线工具： 密码安

  • php封装的mysqli类完整实例

    本文向大家介绍php封装的mysqli类完整实例，包括了php封装的mysqli类完整实例的使用技巧和注意事项，需要的朋友参考一下 本文实例讲述了php封装的mysqli类。分享给大家供大家参考，具体如下： 类： 用法测试： 更多关于PHP相关内容感兴趣的读者可查看本站专题：《php+mysqli数据库程序设计技巧总结》、《php面向对象程序设计入门教程》、《PHP数组(Array)操作技巧大全》

  • PHP数据库操作Helper类完整实例

    本文向大家介绍PHP数据库操作Helper类完整实例，包括了PHP数据库操作Helper类完整实例的使用技巧和注意事项，需要的朋友参考一下 本文实例讲述了PHP数据库操作Helper类。分享给大家供大家参考，具体如下： php操作数据库分为几个步骤(这里以MYSQL为例)： 1. 建立连接 2. 选择数据库 3. 执行CRUD操作 (mysql_affected_rows()前一次mysql操作所

相关阅读

php封装的mysqli类完整实例PHP数据库操作Helper类完整实例MySQL 数据查重、去重的实现语句php基于单例模式封装mysql类完整实例MySQL语句加锁的实现分析

相关文章

通过类型信息创建实例Go语言整型（整数类型）MySQL 是如何实现 ACID 的C++运算符重载（成员函数实现、友元函数实现）小鹅通 Go实习生

相关问答

用throw语句实现try-catch块用MySQL和Oracle实现Hibernate自动密钥生成无法通过Laravel迁移实现MySQL/MariaDB功能更改中通过Swashbuckle生成的Swagger JSON的参数名。NETWebAPI 2通过tomcat中的Servlet实现Websocket

相关工具

PHP代码生成器php 实现数据库连接池、直播平台蚂蚱网址导航DAMAZHA V3 Php+mysql 完美版async-mysql-php数字键盘添加“完成”按钮

相关文档

通过实例学习 C 语言Nginx 开发从入门到精通（不完整）PHP 最佳实践PHP 开发者实践Go语言博客实践
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.