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 |
?> |