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程序设计有所帮助。
本文地址：https://www.stayed.cn/item/25761
转载请注明出处。
本站部分内容来源于网络,如侵犯到您的权益,请 联系我
我的博客
人生若只如初见，何事秋风悲画扇。
我的标签
随笔档案
- 2024-02(2)
- 2023-06(1)
- 2023-05(1)
- 2023-04(14)
- 2023-03(3)
- 2023-01(6)
- 2022-12(5)
- 2022-11(5)
- 2022-07(2)
- 2022-06(4)
- 2022-05(3)
- 2022-03(1)
- 2021-12(6)
- 2021-11(1)
- 2021-10(3)
- 2021-09(5)
- 2021-07(5)
- 2021-02(2)
- 2021-01(7)
- 2020-12(18)
- 2020-11(14)
- 2020-10(12)
- 2020-09(10)
- 2020-08(22)
- 2020-07(2)
- 2020-06(1)
- 2020-04(5)
- 2020-03(9)
- 2020-02(7)
- 2020-01(9)
- 2019-12(8)
- 2019-11(10)
- 2019-10(11)
- 2019-09(17)
- 2019-08(16)
- 2019-07(6)
- 2019-06(3)
- 2019-04(1)
- 2019-03(8)
- 2019-02(5)
- 2019-01(1)
- 2018-11(2)
- 2018-10(3)
- 2018-09(1)
- 2018-08(3)
- 2018-07(3)
- 2018-06(7)
- 2018-04(4)
- 2018-03(5)
- 2018-02(4)
- 2018-01(22)
- 2017-12(3)
- 2017-11(5)
- 2017-10(15)
- 2017-09(26)
- 2017-08(1)
- 2017-07(3)