您好,欢迎来到思海网络,我们将竭诚为您提供优质的服务! 诚征网络推广 | 网站备案 | 帮助中心 | 软件下载 | 购买流程 | 付款方式 | 联系我们 [ 会员登录/注册 ]
促销推广
客服中心
业务咨询
有事点击这里…  531199185
有事点击这里…  61352289
点击这里给我发消息  81721488
有事点击这里…  376585780
有事点击这里…  872642803
有事点击这里…  459248018
有事点击这里…  61352288
有事点击这里…  380791050
技术支持
有事点击这里…  714236853
有事点击这里…  719304487
有事点击这里…  1208894568
有事点击这里…  61352289
在线客服
有事点击这里…  531199185
有事点击这里…  61352288
有事点击这里…  983054746
有事点击这里…  893984210
当前位置:首页 >> 技术文章 >> 文章浏览
技术文章

PHP通过参数来生成MYSQL语句类

添加时间:2015-1-3 20:47:22  添加: 思海网络 

这个类可以通过具有参数的数组来构建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 
DEION : 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 \nVariable 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 \nProperty 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 \nProperty Order was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty 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 \nProperty Order not Numeric ” ; return ;} 
if ( Order > count ( this -> Fields)){ this -> Error = ” className::funct \nMax 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 \nProperty Search was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty 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 \nVariable fields not Array ” ; 
return ; 

if ( ! is_array ( values )) 

this -> Error = ” className::funct \nVariable values not Array ” ; 
return ; 

if ( count ( fields ) != count ( values )) 

this -> Error = ” className::funct \nCount 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 \nTable 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 \nProperty Values was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 
if ( ! this -> getTables()){ this -> Error = ” className::funct \nProperty 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 \nProperty Values was empty ” ; return ;} 
if ( ! this -> getFields()){ this -> Error = ” className::funct \nProperty Fields was empty ” ; return ;} 
if ( ! this -> getTables()){ this -> Error = ” className::funct \nProperty 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 \nProperty Fields was empty ” ; return ;} 
if ( ! this -> getTables()){ this -> Error = ” className::funct \nProperty 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 \nParameter input not array ” ; return ;} 

function setFields( input ) 

if ( is_array ( input )){ this -> Fields = input ;} 
else { this -> Error = get_class ( this ) . ” ::setFields \nParameter input not array ” ; return ;} 

function setLeftJoin( input ) 

if ( is_array ( input )){ this -> LeftJoin = input ;} 
else { this -> Error = get_class ( this ) . ” ::setFields \nParameter 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 \nParameter input not array ” ; return ;} 

function setValues( input ) 

if ( is_array ( input )){ this -> Values = input ;} 
else { this -> Error = get_class ( this ) . ” ::setValues \nParameter input not array ” ; return ;} 


?>

关键字:PHP、MYSQL、参数

分享到:

顶部 】 【 关闭
版权所有:佛山思海电脑网络有限公司 ©1998-2024 All Rights Reserved.
联系电话:(0757)22630313、22633833
中华人民共和国增值电信业务经营许可证: 粤B1.B2-20030321 备案号:粤B2-20030321-1
网站公安备案编号:44060602000007 交互式栏目专项备案编号:200303DD003  
察察 工商 网安 举报有奖  警警  手机打开网站