PHP實(shí)現(xiàn)的通過參數(shù)生成MYSQL語句類完整實(shí)例
更新時(shí)間：2016年04月11日 12:22:00 作者：lgjlry
這篇文章主要介紹了PHP實(shí)現(xiàn)的通過參數(shù)生成MYSQL語句類,結(jié)合完整實(shí)例形式分析了生成MYSQL語句類的實(shí)現(xiàn)與使用技巧,需要的朋友可以參考下
本文實(shí)例講述了PHP實(shí)現(xiàn)的通過參數(shù)生成MYSQL語句類。分享給大家供大家參考，具體如下：
這個(gè)類可以通過指定的表和字段參數(shù)創(chuàng)建SELECT ,INSERT , UPDATE 和 DELETE 語句。
這個(gè)類可以創(chuàng)建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 ;}
}
}
?>
更多關(guān)于PHP相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《PHP基于pdo操作數(shù)據(jù)庫技巧總結(jié)》、《PHP運(yùn)算與運(yùn)算符用法總結(jié)》、《PHP網(wǎng)絡(luò)編程技巧總結(jié)》、《PHP基本語法入門教程》、《php操作office文檔技巧總結(jié)(包括word,excel,access,ppt)》、《php日期與時(shí)間用法總結(jié)》、《php面向?qū)ο蟪绦蛟O(shè)計(jì)入門教程》、《php字符串(string)用法總結(jié)》、《php+mysql數(shù)據(jù)庫操作入門教程》及《php常見數(shù)據(jù)庫操作技巧匯總》
希望本文所述對(duì)大家PHP程序設(shè)計(jì)有所幫助。
相關(guān)文章
PHP5.5基于mysqli連接MySQL數(shù)據(jù)庫和讀取數(shù)據(jù)操作實(shí)例詳解
這篇文章主要介紹了PHP5.5基于mysqli連接MySQL數(shù)據(jù)庫和讀取數(shù)據(jù)操作,結(jié)合實(shí)例形式詳細(xì)分析了php5.5使用mysqli連接、讀取mysql數(shù)據(jù)庫,以及PDO預(yù)處理相關(guān)操作技巧,需要的朋友可以參考下2019-02-02
在開發(fā)中經(jīng)常需要用到樹形數(shù)據(jù)，比如無限多級(jí)分類，就是典型的樹形結(jié)構(gòu)。這里面的算法，用到了遞歸思想，為了簡(jiǎn)化開發(fā)過程，我編寫了一個(gè)工具來提升開發(fā)效率,需要的朋友可以參考下2018-03-03
PHP實(shí)現(xiàn)單文件、多個(gè)單文件、多文件上傳函數(shù)的封裝示例
這篇文章主要介紹了PHP實(shí)現(xiàn)單文件、多個(gè)單文件、多文件上傳函數(shù)的封裝,結(jié)合實(shí)例形式詳細(xì)分析了php文件上傳的原理及針對(duì)文件上傳函數(shù)的封裝相關(guān)操作技巧,需要的朋友可以參考下2019-09-09
這篇文章主要介紹了php版微信小店調(diào)用api用法,結(jié)合實(shí)例形式分析了微信小店API接口調(diào)用展示商品信息的基本操作技巧,需要的朋友可以參考下2016-11-11
基于PHP導(dǎo)出Excel的小經(jīng)驗(yàn) 完美解決亂碼問題
本篇文章是對(duì)PHP導(dǎo)出Excel亂碼問題的解決方法就行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
PHP+JS實(shí)現(xiàn)文件分塊上傳的示例代碼
我們?cè)谏蟼鞔笪募r(shí)，可能會(huì)由于服務(wù)器的原因?qū)е挛募蟼魇?，文件過大時(shí)由于服務(wù)器的配置或響應(yīng)事件過長導(dǎo)致上傳文件失敗，這時(shí)候我們可以將一個(gè)大的文件分為若干塊，然后分批次上傳到服務(wù)端。本文介紹了實(shí)現(xiàn)的方法，需要的可以參考一下2022-11-11
用php+javascript實(shí)現(xiàn)二級(jí)級(jí)聯(lián)菜單的制作
二級(jí)級(jí)聯(lián)菜單在我去年的時(shí)候就用asp+js做過，而現(xiàn)在忽然拿出來再做的時(shí)候我發(fā)現(xiàn)我忘記了，而且原來用asp寫的程序都找不到了，真暈[emot]sweat[/emot]，于是到網(wǎng)上搜，找了半天，我發(fā)現(xiàn)網(wǎng)上的寫法各異，而且都特別復(fù)雜，這么一個(gè)二級(jí)級(jí)聯(lián)菜單，有必要弄這么復(fù)雜嗎？于是自己想重新寫一個(gè)簡(jiǎn)單的。在經(jīng)過半個(gè)小時(shí)左右的思考后，我完成了二級(jí)級(jí)聯(lián)菜單的設(shè)計(jì)和制作。2008-05-05
如果你的網(wǎng)站或者博客上有內(nèi)鏈的Facebook頁面，你或許想知道擁有多少粉絲，下面php可以幫助你查看Facebook粉絲數(shù)2014-01-01
php實(shí)現(xiàn)壓縮多個(gè)CSS與JS文件的方法
這篇文章主要介紹了php實(shí)現(xiàn)壓縮多個(gè)CSS與JS文件的方法,通過兩個(gè)完整實(shí)例分別實(shí)現(xiàn)了針對(duì)css文件與js文件的壓縮,需要的朋友可以參考下2014-11-11
那些年我們錯(cuò)過的魔術(shù)方法（Magic Methods）
PHP 對(duì)象的一個(gè)優(yōu)勢(shì)是可以使用魔術(shù)方法，這些方法可以不需要修改外部代碼而重寫一個(gè)類的默認(rèn)行為，這使得PHP 語法有更少的冗余性和更具有擴(kuò)展性。這些方法很好識(shí)別，他們都是以雙下劃線(__)開始的2014-01-01