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