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

2020-03-22 17:49:45

字体：大中小

来源：转载

供稿：网友

本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考，具体如下：这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFT JOIN和ORDER 语句 /* *******************************************************************Example fileThis example shows how to use the MyLibSQLGen html' target='_blank'>classThe 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 }
$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教程

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。

上一篇：简述php操作mysql数据库的类

下一篇：PHP把MSSQL数据导入到MYSQL的方法