1 |
jonen |
1.1 |
<?php |
2 |
|
|
/** |
3 |
|
|
* This file holds the DataListSource child that can |
4 |
|
|
* handle working with SQL databases. |
5 |
|
|
* |
6 |
|
|
* @author Walter A. Boring IV <waboring@buildabetterweb.com> |
7 |
|
|
* @package phpHtmlLib |
8 |
|
|
*/ |
9 |
|
|
|
10 |
|
|
|
11 |
|
|
|
12 |
|
|
/** |
13 |
|
|
* |
14 |
|
|
*/ |
15 |
|
|
class SQLDataListSource extends DataListSource { |
16 |
|
|
|
17 |
|
|
/** |
18 |
|
|
* this holds all of the options |
19 |
|
|
* that make up the sql query |
20 |
|
|
* |
21 |
|
|
*/ |
22 |
|
|
var $_db_options = array("values" => "", |
23 |
|
|
"tables" => "", |
24 |
|
|
"where_clause" => "", |
25 |
|
|
"groupby_clause" => "", |
26 |
|
|
"count_column" => "*"); |
27 |
|
|
|
28 |
|
|
/** |
29 |
|
|
* The entire query built by |
30 |
|
|
* build_query() |
31 |
|
|
*/ |
32 |
|
|
var $_query = ""; |
33 |
|
|
|
34 |
|
|
|
35 |
|
|
/** |
36 |
|
|
* Set which DB options |
37 |
|
|
* to use for this list. |
38 |
|
|
* |
39 |
|
|
* @param string - $values - the values you |
40 |
|
|
* want to select |
41 |
|
|
* from the db table(s) |
42 |
|
|
* @param string - $tables - The tables you want |
43 |
|
|
* to select on. |
44 |
|
|
* @param string - $where_clause - portion of the where |
45 |
|
|
* clause. |
46 |
|
|
* This expects name=value |
47 |
|
|
* pairs only. |
48 |
|
|
* @param string - $groupby_clause - the groupby portion of the |
49 |
|
|
* query. same format as where_clause |
50 |
|
|
* |
51 |
|
|
*/ |
52 |
|
|
function setup_db_options( $values, $tables, $where_clause='', |
53 |
|
|
$groupby_clause='' ) { |
54 |
|
|
$this->set_db_values( $values ); |
55 |
|
|
$this->set_db_tables( $tables ); |
56 |
|
|
$this->set_db_where_clause( $where_clause ); |
57 |
|
|
$this->set_db_groupby_clause( $groupby_clause ); |
58 |
|
|
} |
59 |
|
|
|
60 |
|
|
/** |
61 |
|
|
* Set the values we want to select |
62 |
|
|
* from the db. |
63 |
|
|
* |
64 |
|
|
* @param string - $values |
65 |
|
|
*/ |
66 |
|
|
function set_db_values( $values ) { |
67 |
|
|
$this->_db_options["values"] = $values; |
68 |
|
|
} |
69 |
|
|
|
70 |
|
|
/** |
71 |
|
|
* This function returns the |
72 |
|
|
* values we need to search for |
73 |
|
|
* in the sql query |
74 |
|
|
* |
75 |
|
|
* @return string |
76 |
|
|
*/ |
77 |
|
|
function get_db_values() { |
78 |
|
|
return $this->_db_options["values"]; |
79 |
|
|
} |
80 |
|
|
|
81 |
|
|
/** |
82 |
|
|
* Set the tables from the DB you |
83 |
|
|
* want to select on. |
84 |
|
|
* |
85 |
|
|
* @param string - $tables |
86 |
|
|
*/ |
87 |
|
|
function set_db_tables( $tables ) { |
88 |
|
|
$this->_db_options["tables"] = $tables; |
89 |
|
|
} |
90 |
|
|
|
91 |
|
|
/** |
92 |
|
|
* returns the table(s) used in the query |
93 |
|
|
* |
94 |
|
|
* @return string |
95 |
|
|
*/ |
96 |
|
|
function get_db_tables() { |
97 |
|
|
return $this->_db_options["tables"]; |
98 |
|
|
} |
99 |
|
|
|
100 |
|
|
/** |
101 |
|
|
* Set the user portion of the where |
102 |
|
|
* clause. |
103 |
|
|
* @param string - name value pairs |
104 |
|
|
*/ |
105 |
|
|
function set_db_where_clause( $where_clause ) { |
106 |
|
|
$this->_db_options["where_clause"] = $where_clause; |
107 |
|
|
} |
108 |
|
|
|
109 |
|
|
/** |
110 |
|
|
* Sets the groupby clause for the query |
111 |
|
|
* |
112 |
|
|
* @param string name value pairs |
113 |
|
|
*/ |
114 |
|
|
function set_db_groupby_clause( $clause ) { |
115 |
|
|
$this->_db_options["groupby_clause"] = $clause; |
116 |
|
|
} |
117 |
|
|
|
118 |
|
|
/** |
119 |
|
|
* This function changes the counted column. |
120 |
|
|
* By default, we use count(1) |
121 |
|
|
* But sometime you need count(distinct KEY) for example |
122 |
|
|
* |
123 |
|
|
*/ |
124 |
|
|
function set_count_column($var) { |
125 |
|
|
$this->_db_options["count_column"] = $var; |
126 |
|
|
} |
127 |
|
|
|
128 |
|
|
/** |
129 |
|
|
* this returns the current count column |
130 |
|
|
* |
131 |
|
|
* @return string |
132 |
|
|
*/ |
133 |
|
|
function get_count_column() { |
134 |
|
|
return $this->_db_options["count_column"]; |
135 |
|
|
} |
136 |
|
|
|
137 |
|
|
|
138 |
|
|
/** |
139 |
|
|
* this gets the count column |
140 |
|
|
* |
141 |
|
|
* @return string |
142 |
|
|
*/ |
143 |
|
|
function get_count_column() { |
144 |
|
|
return $this->_db_options["count_column"]; |
145 |
|
|
} |
146 |
|
|
|
147 |
|
|
// This functions joins an arbitrary number of clauses using the |
148 |
|
|
// specified connector. It can be used in one of the following two |
149 |
|
|
// ways: |
150 |
|
|
// join($connector, $clause_1, $clause_2, ...); |
151 |
|
|
// join($connector, $clauses_array); |
152 |
|
|
// |
153 |
|
|
function join_clauses() { |
154 |
|
|
$num_args = func_num_args(); |
155 |
|
|
$args = func_get_args(); |
156 |
|
|
|
157 |
|
|
$connector = $args[0]; |
158 |
|
|
$clauses = array(); |
159 |
|
|
|
160 |
|
|
if (is_array($args[1])) { |
161 |
|
|
$arr = $args[1]; |
162 |
|
|
} else { |
163 |
|
|
$arr = $args; |
164 |
|
|
array_shift($arr); |
165 |
|
|
} |
166 |
|
|
$count = sizeof($arr); |
167 |
|
|
|
168 |
|
|
for ($i = 0; $i < $count; ++$i) { |
169 |
|
|
if ($arr[$i]) { |
170 |
|
|
array_push($clauses, $arr[$i]); |
171 |
|
|
} |
172 |
|
|
} |
173 |
|
|
|
174 |
|
|
// Now the array "$clauses" contains all non null clauses. |
175 |
|
|
$num_clauses = sizeof($clauses); |
176 |
|
|
if ($num_clauses = 0) { |
177 |
|
|
return ""; |
178 |
|
|
} |
179 |
|
|
return join(" $connector ", $clauses); |
180 |
|
|
} |
181 |
|
|
|
182 |
|
|
/** |
183 |
|
|
* this builds the search clause |
184 |
|
|
* |
185 |
|
|
* @return string |
186 |
|
|
*/ |
187 |
|
|
function simple_search_where_clause() { |
188 |
|
|
$search_field = $this->get_searchby(); |
189 |
|
|
$search_value = $this->search_value_filter($this->get_searchby_value()); |
190 |
|
|
|
191 |
|
|
if (($search_field == '') || ($search_value == '')) |
192 |
|
|
return ''; |
193 |
|
|
|
194 |
|
|
$where_clause = "lower(".$search_field.")"; |
195 |
|
|
|
196 |
|
|
//see if the simple search modifier is turned on |
197 |
|
|
//and then use it for the like clause |
198 |
|
|
if ($this->get_simplesearch_modifier()) { |
199 |
|
|
$search_value = strtolower($search_value); |
200 |
|
|
//its on. lets see if it has a value. |
201 |
|
|
$modifier = $this->get_simplesearch_modifier(); |
202 |
|
|
switch ($modifier) { |
203 |
|
|
case "BEGINS": |
204 |
|
|
$like_clause = " like '".$search_value."%' "; |
205 |
|
|
break; |
206 |
|
|
case "ENDS": |
207 |
|
|
$like_clause = " like '%".$search_value."' "; |
208 |
|
|
break; |
209 |
|
|
case "EXACT": |
210 |
|
|
$like_clause = " like '".$search_value."' "; |
211 |
|
|
break; |
212 |
|
|
|
213 |
|
|
case "CONTAINS": |
214 |
|
|
default: |
215 |
|
|
$like_clause = " like '%".$search_value."%' "; |
216 |
|
|
break; |
217 |
|
|
} |
218 |
|
|
} |
219 |
|
|
else { |
220 |
|
|
$like_clause = " like '%".$search_value."%'"; |
221 |
|
|
} |
222 |
|
|
|
223 |
|
|
$where_clause .= $like_clause; |
224 |
|
|
return $where_clause; |
225 |
|
|
} |
226 |
|
|
|
227 |
|
|
/** |
228 |
|
|
* This function is responsible for |
229 |
|
|
* building the portion of the where clause |
230 |
|
|
* for doing an advanced search. |
231 |
|
|
* NOTE: the child class MUST override this |
232 |
|
|
* method |
233 |
|
|
* @return string - portion of the where clause |
234 |
|
|
*/ |
235 |
|
|
function advanced_search_where_clause() { |
236 |
|
|
return ""; |
237 |
|
|
} |
238 |
|
|
|
239 |
|
|
/** |
240 |
|
|
* This function does the variable cleaning |
241 |
|
|
* that is required to use a search value |
242 |
|
|
* in a sql query |
243 |
|
|
* |
244 |
|
|
* @param string - the origina string from the user |
245 |
|
|
* @return string - the "cleaned" string |
246 |
|
|
*/ |
247 |
|
|
function search_value_filter( $value ) { |
248 |
|
|
$value = $this->sql_varchar_filter( strip_tags(stripslashes(trim($value))) ); |
249 |
|
|
$value = str_replace('%', '\%', $value); |
250 |
|
|
$value = str_replace('_', '\_', $value); |
251 |
|
|
return $value; |
252 |
|
|
} |
253 |
|
|
|
254 |
|
|
/** |
255 |
|
|
* Clean up the string so it can work in a db |
256 |
|
|
* |
257 |
|
|
* @param string |
258 |
|
|
* @return string |
259 |
|
|
*/ |
260 |
|
|
function sql_varchar_filter( $value ) { |
261 |
|
|
return str_replace("'","''",$value); |
262 |
|
|
} |
263 |
|
|
|
264 |
|
|
/** |
265 |
|
|
* This builds the where clause for |
266 |
|
|
* the query |
267 |
|
|
* |
268 |
|
|
* @return string the where clause. |
269 |
|
|
*/ |
270 |
|
|
function build_where_clause() { |
271 |
|
|
$where_clause = $this->_db_options["where_clause"]; |
272 |
|
|
if ($where_clause) { |
273 |
|
|
$where_clause = "( $where_clause ) "; |
274 |
|
|
} |
275 |
|
|
|
276 |
|
|
$search_clause = $this->build_search_clause(); |
277 |
|
|
if ($search_clause) { |
278 |
|
|
$search_clause = " ( $search_clause ) "; |
279 |
|
|
} |
280 |
|
|
|
281 |
|
|
$where_clause = trim($this->join_clauses("AND", |
282 |
|
|
$where_clause, |
283 |
|
|
$search_clause)); |
284 |
|
|
if ($where_clause) { |
285 |
|
|
$where_clause = " WHERE $where_clause"; |
286 |
|
|
} |
287 |
|
|
return $where_clause; |
288 |
|
|
} |
289 |
|
|
|
290 |
|
|
/** |
291 |
|
|
* This method builds the where clause. |
292 |
|
|
* |
293 |
|
|
* @return string |
294 |
|
|
*/ |
295 |
|
|
function build_search_clause() { |
296 |
|
|
if ($this->get_search_type() == "advanced") { |
297 |
|
|
return $this->advanced_search_where_clause(); |
298 |
|
|
} else { |
299 |
|
|
return $this->simple_search_where_clause(); |
300 |
|
|
} |
301 |
|
|
} |
302 |
|
|
|
303 |
|
|
/** |
304 |
|
|
* This function builds the orderby clause |
305 |
|
|
* for the DB query. |
306 |
|
|
* |
307 |
|
|
* @param string - DB table field to order by |
308 |
|
|
* @param string - order assension value. |
309 |
|
|
* |
310 |
|
|
* @return string. |
311 |
|
|
*/ |
312 |
|
|
function setup_order($orderby, $reverseorder) { |
313 |
|
|
if ($reverseorder == "true") { |
314 |
|
|
return "order by ".$orderby." DESC"; |
315 |
|
|
} |
316 |
|
|
else { |
317 |
|
|
return "order by ".$orderby; |
318 |
|
|
} |
319 |
|
|
} |
320 |
|
|
|
321 |
|
|
/** |
322 |
|
|
* This builds the db query ORDER BY |
323 |
|
|
* clause used to sort the data from the DB. |
324 |
|
|
* |
325 |
|
|
* @return string - the order by clause |
326 |
|
|
*/ |
327 |
|
|
function build_order_clause() { |
328 |
|
|
|
329 |
|
|
// Set up reverseorder correctly |
330 |
|
|
foreach( $this->_columns as $col_name => $data ) { |
331 |
|
|
if (($data["data_name"] == $this->get_orderby()) && |
332 |
|
|
($data["reverseorder"] == "true") && |
333 |
|
|
!isset( ${$this->reverseorderVar})) { |
334 |
|
|
$this->set_reverseorder( $data["reverseorder"] ); |
335 |
|
|
} |
336 |
|
|
} |
337 |
|
|
|
338 |
|
|
$orderClause = $this->setup_order($this->get_orderby(), $this->get_reverseorder() ); |
339 |
|
|
return $orderClause; |
340 |
|
|
} |
341 |
|
|
|
342 |
|
|
/** |
343 |
|
|
* this builds the groupby clause of the query |
344 |
|
|
* |
345 |
|
|
* @return string |
346 |
|
|
*/ |
347 |
|
|
function build_groupby_clause() { |
348 |
|
|
$groupby = $this->_groupby_clause; |
349 |
|
|
if ($groupby) { |
350 |
|
|
$groupby = " GROUP BY $groupby "; |
351 |
|
|
} |
352 |
|
|
|
353 |
|
|
return $groupby; |
354 |
|
|
} |
355 |
|
|
|
356 |
|
|
/** |
357 |
|
|
* build the entire DB query |
358 |
|
|
* |
359 |
|
|
* @param boolean - build the limit clause or not/ |
360 |
|
|
* @return string - the full query string to the DB. |
361 |
|
|
*/ |
362 |
|
|
function build_query($limit_flag=TRUE) { |
363 |
|
|
|
364 |
|
|
$where_clause = $this->build_where_clause(); |
365 |
|
|
$order_clause = $this->build_order_clause(); |
366 |
|
|
$groupby_clause = $this->build_groupby_clause(); |
367 |
|
|
if ($limit_flag) { |
368 |
|
|
$limit_clause = $this->build_limit_clause($this->get_offset(), $this->get_limit()); |
369 |
|
|
} |
370 |
|
|
|
371 |
|
|
$query = "select ".$this->get_db_values()." from ".$this->get_db_tables()." " . |
372 |
|
|
$where_clause." ".$groupby_clause." ".$order_clause . |
373 |
|
|
" ".$limit_clause; |
374 |
|
|
|
375 |
|
|
return $query; |
376 |
|
|
} |
377 |
|
|
|
378 |
|
|
|
379 |
|
|
function do_prequery() { |
380 |
|
|
//ok now count the total # of rows so |
381 |
|
|
//we can calculate the # of pages. |
382 |
|
|
//count rows |
383 |
|
|
//ok now count the total # of rows so |
384 |
|
|
//we can calculate the # of pages. |
385 |
|
|
$this->set_total_rows( $this->count( $this->get_db_tables(), |
386 |
|
|
$this->build_where_clause(), |
387 |
|
|
$this->get_count_column() ) ); |
388 |
|
|
|
389 |
|
|
$this->_query = $this->build_query(); |
390 |
|
|
} |
391 |
|
|
|
392 |
|
|
|
393 |
|
|
/************************************************/ |
394 |
|
|
/* DB Specific Routines */ |
395 |
|
|
/* ------------------------ */ |
396 |
|
|
/* These must be extended by the child class */ |
397 |
|
|
/* This gives you the power to do the specific */ |
398 |
|
|
/* DB interactions as you see fit. Some folks */ |
399 |
|
|
/* use the low level php db API's, some use the */ |
400 |
|
|
/* PEAR db abstraction objects, and others roll */ |
401 |
|
|
/* their own DB objects. Extend the functions */ |
402 |
|
|
/* to provide: */ |
403 |
|
|
/* */ |
404 |
|
|
/* do_query() - this function's purpose is to */ |
405 |
|
|
/* execute the sql query that is */ |
406 |
|
|
/* available in $this->_query by */ |
407 |
|
|
/* the time do_query() is called. */ |
408 |
|
|
/* */ |
409 |
|
|
/* get_next_data_row() - This function returns */ |
410 |
|
|
/* an associative array of the */ |
411 |
|
|
/* next row of data from the query.*/ |
412 |
|
|
/* */ |
413 |
|
|
/* build_limit_clause() - This function builds */ |
414 |
|
|
/* and returns the "limit clause" */ |
415 |
|
|
/* portion of a sql query. Each */ |
416 |
|
|
/* DB implementation is different */ |
417 |
|
|
/* on how it deals with limiting */ |
418 |
|
|
/* the amount of data you want */ |
419 |
|
|
/* back from the query. Oracle */ |
420 |
|
|
/* has no limit clause, so it has */ |
421 |
|
|
/* to do this in very tricky/nasty */ |
422 |
|
|
/* ways. */ |
423 |
|
|
/* */ |
424 |
|
|
/* count() - this function simply returns the */ |
425 |
|
|
/* number of rows (to be) found in */ |
426 |
|
|
/* sql query. */ |
427 |
|
|
/* */ |
428 |
|
|
/************************************************/ |
429 |
|
|
|
430 |
|
|
/** |
431 |
|
|
* Do the query to the DB and pull in all of |
432 |
|
|
* the records locally. |
433 |
|
|
* |
434 |
|
|
*/ |
435 |
|
|
function do_query() { |
436 |
|
|
//execute the sql query. |
437 |
|
|
//we don't actually get the results of |
438 |
|
|
//the query here, when just execute it. |
439 |
|
|
user_error("SQLDataListSource::do_query() - Child must override"); |
440 |
|
|
} |
441 |
|
|
|
442 |
|
|
/** |
443 |
|
|
* This function gets the next data row |
444 |
|
|
* from the query() |
445 |
|
|
* |
446 |
|
|
* @return array() |
447 |
|
|
*/ |
448 |
|
|
function get_next_data_row() { |
449 |
|
|
user_error("SQLDataListSource::get_next_data_row() - Child must override"); |
450 |
|
|
} |
451 |
|
|
|
452 |
|
|
/** |
453 |
|
|
* This function builds the limit |
454 |
|
|
* clause portion of a DB query. |
455 |
|
|
* This is specific to the particular |
456 |
|
|
* DB implementation. |
457 |
|
|
* |
458 |
|
|
* @return string - the limit portion of |
459 |
|
|
* the query. |
460 |
|
|
*/ |
461 |
|
|
function build_limit_clause($offset, $limit) { |
462 |
|
|
user_error("SQLDataListSource::build_limit_clause() - Child must override"); |
463 |
|
|
} |
464 |
|
|
|
465 |
|
|
/** |
466 |
|
|
* find the number of rows to be returned |
467 |
|
|
* from a query from a table and where clause |
468 |
|
|
* |
469 |
|
|
* @param string $table - the table to count from |
470 |
|
|
* @param string $where_clause - a where clause |
471 |
|
|
* |
472 |
|
|
* @return int the # of rows |
473 |
|
|
*/ |
474 |
|
|
function count($tables, $where_clause='', $count_clause='*') { |
475 |
|
|
user_error("SQLDataListSource::count() - Child must override"); |
476 |
|
|
} |
477 |
|
|
|
478 |
|
|
} |
479 |
|
|
|
480 |
|
|
?> |