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

本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考，具体如下：
这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。
这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFT JOIN和ORDER 语句

<?php /* *******************************************************************Example fileThis example shows how to use the MyLibSQLGen 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> " ;}?>

类代码：

<?php /* Created By: Fadjar Nurswanto <fajr_n@rindudendam.net>DATE: 2006-08-02PRODUCTNAME: class MyLibSQLGenPRODUCTVERSION: 1.0.0DESCRIPTION: class yang berfungsi untuk menggenerate SQLDENPENCIES: */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 ）; returntrue ;} 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 " ] . "" ;} returntrue ;} 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 （ $LeftJoinVarsas$LeftJoinVar ）{@ $hasil .= " LEFT JOIN " . $LeftJoinVar [ " table " ]; foreach （ $LeftJoinVar [ " on " ] as$var ）{@ $condvar .= $var [ " condition " ] . "" . $var [ " connection " ] . "" ;} $hasil .= " ON （ " . $condvar . " ） " ; unset （ $condvar ）;} $this -> ResultLeftJoin = $hasil ; returntrue ;} 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 （ $Ordersas$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 ）; returntrue ;} 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 （ $Fieldsas$field ）{ if （@ $xvalue [ $field ]）{ $Values = explode （ "" , $xvalue [ $field ]）; foreach （ $Valuesas$Value ）{@ $hasil .= $field . " LIKE "% " . $Value . " %" OR " ;} if （ $hasil ）{@ $hasil_final .= " （ " . substr （ $hasil , 0 ,- 4 ） . " ） AND " ; unset （ $hasil ）;}}} $hasil = $hasil_final ;} else { foreach （ $Fieldsas$field ）{ $Values = explode （ "" , $xvalue ）; foreach （ $Valuesas$Value ）{@ $hasil .= $field . " LIKE "% " . $Value . " %" OR " ;}}} $this -> ResultSearch = substr （ $hasil , 0 ,- 4 ）; returntrue ;} 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 ; returntrue ;} 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 ）; returntrue ;} 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 ; returntrue ;} 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 ; returntrue ;} 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 ; returntrue ;} 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 （ $Fieldsas$Field ）{@ $sql_raw .= $Field . " , " ;} foreach （ $Tablesas$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（）;}} returntrue ;} 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程序设计有所帮助。