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 |
// This functions joins an arbitrary number of clauses using the |
138 |
// specified connector. It can be used in one of the following two |
139 |
// ways: |
140 |
// join($connector, $clause_1, $clause_2, ...); |
141 |
// join($connector, $clauses_array); |
142 |
// |
143 |
function join_clauses() { |
144 |
$num_args = func_num_args(); |
145 |
$args = func_get_args(); |
146 |
|
147 |
$connector = $args[0]; |
148 |
$clauses = array(); |
149 |
|
150 |
if (is_array($args[1])) { |
151 |
$arr = $args[1]; |
152 |
} else { |
153 |
$arr = $args; |
154 |
array_shift($arr); |
155 |
} |
156 |
$count = sizeof($arr); |
157 |
|
158 |
for ($i = 0; $i < $count; ++$i) { |
159 |
if ($arr[$i]) { |
160 |
array_push($clauses, $arr[$i]); |
161 |
} |
162 |
} |
163 |
|
164 |
// Now the array "$clauses" contains all non null clauses. |
165 |
$num_clauses = sizeof($clauses); |
166 |
if ($num_clauses = 0) { |
167 |
return ""; |
168 |
} |
169 |
return join(" $connector ", $clauses); |
170 |
} |
171 |
|
172 |
/** |
173 |
* this builds the search clause |
174 |
* |
175 |
* @return string |
176 |
*/ |
177 |
function simple_search_where_clause() { |
178 |
$search_field = $this->get_searchby(); |
179 |
$search_value = $this->search_value_filter($this->get_searchby_value()); |
180 |
|
181 |
if (($search_field == '') || ($search_value == '')) |
182 |
return ''; |
183 |
|
184 |
$where_clause = "lower(".$search_field.")"; |
185 |
|
186 |
//see if the simple search modifier is turned on |
187 |
//and then use it for the like clause |
188 |
if ($this->get_simplesearch_modifier()) { |
189 |
$search_value = strtolower($search_value); |
190 |
//its on. lets see if it has a value. |
191 |
$modifier = $this->get_simplesearch_modifier(); |
192 |
switch ($modifier) { |
193 |
case "BEGINS": |
194 |
$like_clause = " like '".$search_value."%' "; |
195 |
break; |
196 |
case "ENDS": |
197 |
$like_clause = " like '%".$search_value."' "; |
198 |
break; |
199 |
case "EXACT": |
200 |
$like_clause = " like '".$search_value."' "; |
201 |
break; |
202 |
|
203 |
case "CONTAINS": |
204 |
default: |
205 |
$like_clause = " like '%".$search_value."%' "; |
206 |
break; |
207 |
} |
208 |
} |
209 |
else { |
210 |
$like_clause = " like '%".$search_value."%'"; |
211 |
} |
212 |
|
213 |
$where_clause .= $like_clause; |
214 |
return $where_clause; |
215 |
} |
216 |
|
217 |
/** |
218 |
* This function is responsible for |
219 |
* building the portion of the where clause |
220 |
* for doing an advanced search. |
221 |
* NOTE: the child class MUST override this |
222 |
* method |
223 |
* @return string - portion of the where clause |
224 |
*/ |
225 |
function advanced_search_where_clause() { |
226 |
return ""; |
227 |
} |
228 |
|
229 |
/** |
230 |
* This function does the variable cleaning |
231 |
* that is required to use a search value |
232 |
* in a sql query |
233 |
* |
234 |
* @param string - the origina string from the user |
235 |
* @return string - the "cleaned" string |
236 |
*/ |
237 |
function search_value_filter( $value ) { |
238 |
$value = $this->sql_varchar_filter( strip_tags(stripslashes(trim($value))) ); |
239 |
$value = str_replace('%', '\%', $value); |
240 |
$value = str_replace('_', '\_', $value); |
241 |
return $value; |
242 |
} |
243 |
|
244 |
/** |
245 |
* Clean up the string so it can work in a db |
246 |
* |
247 |
* @param string |
248 |
* @return string |
249 |
*/ |
250 |
function sql_varchar_filter( $value ) { |
251 |
return str_replace("'","''",$value); |
252 |
} |
253 |
|
254 |
/** |
255 |
* This builds the where clause for |
256 |
* the query |
257 |
* |
258 |
* @return string the where clause. |
259 |
*/ |
260 |
function build_where_clause() { |
261 |
$where_clause = $this->_db_options["where_clause"]; |
262 |
if ($where_clause) { |
263 |
$where_clause = "( $where_clause ) "; |
264 |
} |
265 |
|
266 |
$search_clause = $this->build_search_clause(); |
267 |
if ($search_clause) { |
268 |
$search_clause = " ( $search_clause ) "; |
269 |
} |
270 |
|
271 |
$where_clause = trim($this->join_clauses("AND", |
272 |
$where_clause, |
273 |
$search_clause)); |
274 |
if ($where_clause) { |
275 |
$where_clause = " WHERE $where_clause"; |
276 |
} |
277 |
return $where_clause; |
278 |
} |
279 |
|
280 |
/** |
281 |
* This method builds the where clause. |
282 |
* |
283 |
* @return string |
284 |
*/ |
285 |
function build_search_clause() { |
286 |
if ($this->get_search_type() == "advanced") { |
287 |
return $this->advanced_search_where_clause(); |
288 |
} else { |
289 |
return $this->simple_search_where_clause(); |
290 |
} |
291 |
} |
292 |
|
293 |
/** |
294 |
* This function builds the orderby clause |
295 |
* for the DB query. |
296 |
* |
297 |
* @param string - DB table field to order by |
298 |
* @param string - order assension value. |
299 |
* |
300 |
* @return string. |
301 |
*/ |
302 |
function setup_order($orderby, $reverseorder) { |
303 |
if ($reverseorder == "true") { |
304 |
return "order by ".$orderby." DESC"; |
305 |
} |
306 |
else { |
307 |
return "order by ".$orderby; |
308 |
} |
309 |
} |
310 |
|
311 |
/** |
312 |
* This builds the db query ORDER BY |
313 |
* clause used to sort the data from the DB. |
314 |
* |
315 |
* @return string - the order by clause |
316 |
*/ |
317 |
function build_order_clause() { |
318 |
|
319 |
// Set up reverseorder correctly |
320 |
foreach( $this->_columns as $col_name => $data ) { |
321 |
if (($data["data_name"] == $this->get_orderby()) && |
322 |
($data["reverseorder"] == "true") && |
323 |
!isset( ${$this->reverseorderVar})) { |
324 |
$this->set_reverseorder( $data["reverseorder"] ); |
325 |
} |
326 |
} |
327 |
|
328 |
$orderClause = $this->setup_order($this->get_orderby(), $this->get_reverseorder() ); |
329 |
return $orderClause; |
330 |
} |
331 |
|
332 |
/** |
333 |
* this builds the groupby clause of the query |
334 |
* |
335 |
* @return string |
336 |
*/ |
337 |
function build_groupby_clause() { |
338 |
$groupby = $this->_db_options["groupby_clause"]; |
339 |
if ($groupby) { |
340 |
$groupby = " GROUP BY $groupby "; |
341 |
} |
342 |
|
343 |
return $groupby; |
344 |
} |
345 |
|
346 |
/** |
347 |
* build the entire DB query |
348 |
* |
349 |
* @param boolean - build the limit clause or not/ |
350 |
* @return string - the full query string to the DB. |
351 |
*/ |
352 |
function build_query($limit_flag=TRUE) { |
353 |
|
354 |
$where_clause = $this->build_where_clause(); |
355 |
$order_clause = $this->build_order_clause(); |
356 |
$groupby_clause = $this->build_groupby_clause(); |
357 |
if ($limit_flag) { |
358 |
$limit_clause = $this->build_limit_clause($this->get_offset(), $this->get_limit()); |
359 |
} |
360 |
|
361 |
$query = "select ".$this->get_db_values()." from ".$this->get_db_tables()." " . |
362 |
$where_clause." ".$groupby_clause." ".$order_clause . |
363 |
" ".$limit_clause; |
364 |
|
365 |
return $query; |
366 |
} |
367 |
|
368 |
|
369 |
function do_prequery() { |
370 |
//ok now count the total # of rows so |
371 |
//we can calculate the # of pages. |
372 |
//count rows |
373 |
//ok now count the total # of rows so |
374 |
//we can calculate the # of pages. |
375 |
$this->set_total_rows( $this->count( $this->get_db_tables(), |
376 |
$this->build_where_clause(), |
377 |
$this->get_count_column() ) ); |
378 |
|
379 |
$this->_query = $this->build_query(); |
380 |
} |
381 |
|
382 |
|
383 |
/************************************************/ |
384 |
/* DB Specific Routines */ |
385 |
/* ------------------------ */ |
386 |
/* These must be extended by the child class */ |
387 |
/* This gives you the power to do the specific */ |
388 |
/* DB interactions as you see fit. Some folks */ |
389 |
/* use the low level php db API's, some use the */ |
390 |
/* PEAR db abstraction objects, and others roll */ |
391 |
/* their own DB objects. Extend the functions */ |
392 |
/* to provide: */ |
393 |
/* */ |
394 |
/* do_query() - this function's purpose is to */ |
395 |
/* execute the sql query that is */ |
396 |
/* available in $this->_query by */ |
397 |
/* the time do_query() is called. */ |
398 |
/* */ |
399 |
/* get_next_data_row() - This function returns */ |
400 |
/* an associative array of the */ |
401 |
/* next row of data from the query.*/ |
402 |
/* */ |
403 |
/* build_limit_clause() - This function builds */ |
404 |
/* and returns the "limit clause" */ |
405 |
/* portion of a sql query. Each */ |
406 |
/* DB implementation is different */ |
407 |
/* on how it deals with limiting */ |
408 |
/* the amount of data you want */ |
409 |
/* back from the query. Oracle */ |
410 |
/* has no limit clause, so it has */ |
411 |
/* to do this in very tricky/nasty */ |
412 |
/* ways. */ |
413 |
/* */ |
414 |
/* count() - this function simply returns the */ |
415 |
/* number of rows (to be) found in */ |
416 |
/* sql query. */ |
417 |
/* */ |
418 |
/************************************************/ |
419 |
|
420 |
/** |
421 |
* Do the query to the DB and pull in all of |
422 |
* the records locally. |
423 |
* |
424 |
* @return boolean - the query passed/failed. |
425 |
*/ |
426 |
function do_query() { |
427 |
//execute the sql query. |
428 |
//we don't actually get the results of |
429 |
//the query here, when just execute it. |
430 |
user_error("SQLDataListSource::do_query() - Child must override"); |
431 |
return false; |
432 |
} |
433 |
|
434 |
/** |
435 |
* This function gets the next data row |
436 |
* from the query() |
437 |
* |
438 |
* @return array() |
439 |
*/ |
440 |
function get_next_data_row() { |
441 |
user_error("SQLDataListSource::get_next_data_row() - Child must override"); |
442 |
return array(); |
443 |
} |
444 |
|
445 |
/** |
446 |
* This function builds the limit |
447 |
* clause portion of a DB query. |
448 |
* This is specific to the particular |
449 |
* DB implementation. |
450 |
* |
451 |
* @return string - the limit portion of |
452 |
* the query. |
453 |
*/ |
454 |
function build_limit_clause($offset, $limit) { |
455 |
user_error("SQLDataListSource::build_limit_clause() - Child must override"); |
456 |
} |
457 |
|
458 |
/** |
459 |
* find the number of rows to be returned |
460 |
* from a query from a table and where clause |
461 |
* |
462 |
* @param string $table - the table to count from |
463 |
* @param string $where_clause - a where clause |
464 |
* |
465 |
* @return int the # of rows |
466 |
*/ |
467 |
function count($tables, $where_clause='', $count_clause='*') { |
468 |
user_error("SQLDataListSource::count() - Child must override"); |
469 |
} |
470 |
|
471 |
} |
472 |
|
473 |
?> |