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 |
|
|
// 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 |
jonen |
1.3 |
$groupby = $this->_db_options["groupby_clause"]; |
339 |
jonen |
1.1 |
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 |
jonen |
1.3 |
* @return boolean - the query passed/failed. |
425 |
jonen |
1.1 |
*/ |
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 |
jonen |
1.3 |
return false; |
432 |
jonen |
1.1 |
} |
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 |
jonen |
1.3 |
return array(); |
443 |
jonen |
1.1 |
} |
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 |
|
|
?> |