本文实例讲述了PHP实现的通过参数生成MYSQL语句类。分享给大家供大家参考，具体如下：
这个类可以通过指定的表和字段参数创建SELECT ,INSERT , UPDATE 和 DELETE 语句。
这个类可以创建SQL语句的WHERE条件，像LIKE的查询语句，使用LEFT JOIN和ORDER 语句
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
<?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> " ;}
?>
|
类代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
|
<?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程序设计有所帮助。