widgets/data_list/SQLDataListSource.inc

<?php
/**
 * This file holds the DataListSource child that can
 * handle working with SQL databases.
 *
 * @author Walter A. Boring IV <waboring@buildabetterweb.com>
 * @package phpHtmlLib
 */


/**
 * 
 */
class SQLDataListSource extends DataListSource {

        /**
         * this holds all of the options
         * that make up the sql query
         *
         */
        var $_db_options = array("values" => "",
                                                         "tables" => "",
                                                         "where_clause" => "",
                                                         "groupby_clause" => "",
                                                         "count_column" => "*");

        /**
         * The entire query built by
         * build_query()
         */
        var $_query = "";


    /**
     * Set which DB options
     * to use for this list.
     *
     * @param string - $values - the values you
     *                           want to select
     *                           from the db table(s)
     * @param string - $tables - The tables you want
     *                           to select on.
     * @param string - $where_clause - portion of the where
     *                                 clause.
     *                                 This expects name=value
     *                                 pairs only.
     * @param string - $groupby_clause - the groupby portion of the
     *                                   query.  same format as where_clause
     *
     */
    function setup_db_options( $values, $tables, $where_clause='',
                               $groupby_clause='' ) {
        $this->set_db_values( $values );
        $this->set_db_tables( $tables );
        $this->set_db_where_clause( $where_clause );
        $this->set_db_groupby_clause( $groupby_clause );
    }

    /**
     * Set the values we want to select
     * from the db.
     *
     * @param string - $values
     */
    function set_db_values( $values ) {
        $this->_db_options["values"] = $values;
    }

        /**
         * This function returns the 
         * values we need to search for
         * in the sql query
         *
         * @return string
         */
        function get_db_values() {
                return $this->_db_options["values"];
        }

    /**
     * Set the tables from the DB you
     * want to select on.
     *
     * @param   string - $tables
     */
    function set_db_tables( $tables ) {
        $this->_db_options["tables"] = $tables;
    }

        /**
         * returns the table(s) used in the query
         *
         * @return string
         */
        function get_db_tables() {
                return $this->_db_options["tables"];
        }

    /**
     * Set the user portion of the where
     * clause.
     * @param string - name value pairs
     */
    function set_db_where_clause( $where_clause ) {
        $this->_db_options["where_clause"] = $where_clause;
    }

    /**
     * Sets the groupby clause for the query
     *
     * @param string name value pairs
     */
    function set_db_groupby_clause( $clause ) {
        $this->_db_options["groupby_clause"] = $clause;
    }

    /**
     * This function changes the counted column.
     * By default, we use count(1)
     * But sometime you need count(distinct KEY) for example
     *
     */
    function set_count_column($var) {
        $this->_db_options["count_column"] = $var;
    }

        /**
         * this returns the current count column
         *
         * @return string
         */
        function get_count_column() {
                return $this->_db_options["count_column"];
        }

    // This functions joins an arbitrary number of clauses using the
    // specified connector. It can be used in one of the following two
    // ways:
    //      join($connector, $clause_1, $clause_2, ...);
    //      join($connector, $clauses_array);
    //
    function join_clauses() {
        $num_args = func_num_args();
        $args = func_get_args();

        $connector = $args[0];
        $clauses = array();

        if (is_array($args[1])) {
            $arr = $args[1];
        } else {
            $arr = $args;
            array_shift($arr);
        }
        $count = sizeof($arr);

        for ($i = 0; $i < $count; ++$i) {
            if ($arr[$i]) {
                array_push($clauses, $arr[$i]);
            }
        }

        // Now the array "$clauses" contains all non null clauses.
        $num_clauses = sizeof($clauses);
        if ($num_clauses = 0) {
            return "";
        }
        return join(" $connector ", $clauses);
    }

    /**
     * this builds the search clause
     *
     * @return string
     */
    function simple_search_where_clause() {
        $search_field = $this->get_searchby();
        $search_value = $this->search_value_filter($this->get_searchby_value());

        if (($search_field == '') || ($search_value == ''))
            return '';

        $where_clause = "lower(".$search_field.")";

        //see if the simple search modifier is turned on
        //and then use it for the like clause
        if ($this->get_simplesearch_modifier()) {
            $search_value = strtolower($search_value);
            //its on.  lets see if it has a value.
            $modifier = $this->get_simplesearch_modifier();
            switch ($modifier) {
            case "BEGINS":
                $like_clause = " like '".$search_value."%' ";
                break;
            case "ENDS":
                $like_clause = " like '%".$search_value."' ";
                break;
            case "EXACT":
                $like_clause = " like '".$search_value."' ";
                break;

            case "CONTAINS":
            default:
                $like_clause = " like '%".$search_value."%' ";
                break;
            }
        }
        else {
            $like_clause = " like '%".$search_value."%'";
        }

                $where_clause .= $like_clause;
        return $where_clause;
    }

    /**
     * This function is responsible for
     * building the portion of the where clause
     * for doing an advanced search.
     * NOTE: the child class MUST override this
     *       method
     * @return string - portion of the where clause
     */
    function advanced_search_where_clause() {
        return "";
    }

    /**
     * This function does the variable cleaning
     * that is required to use a search value
     * in a sql query
     *
     * @param string - the origina string from the user
     * @return string - the "cleaned" string
     */
    function search_value_filter( $value ) {
        $value = $this->sql_varchar_filter( strip_tags(stripslashes(trim($value))) );
        $value = str_replace('%', '\%', $value);
        $value = str_replace('_', '\_', $value);
        return $value;
    }

        /**
         * Clean up the string so it can work in a db
         *
         * @param string
         * @return string
         */
        function sql_varchar_filter( $value ) {
                return str_replace("'","''",$value);
        }

    /**
     * This builds the where clause for
     * the query
     *
     * @return string the where clause.
     */
    function build_where_clause() {
        $where_clause = $this->_db_options["where_clause"];
        if ($where_clause) {
            $where_clause = "( $where_clause ) ";
        }

        $search_clause = $this->build_search_clause();
        if ($search_clause) {
            $search_clause = " ( $search_clause ) ";
        }

        $where_clause = trim($this->join_clauses("AND",
                                                 $where_clause,
                                                 $search_clause));
        if ($where_clause) {
            $where_clause = " WHERE $where_clause";
        }
        return $where_clause;
    }

    /**
     * This method builds the where clause.
     *
     * @return string
     */
    function build_search_clause() {
        if ($this->get_search_type() == "advanced") {
            return $this->advanced_search_where_clause();
        } else {
            return $this->simple_search_where_clause();
        }
    }

    /**
     * This function builds the orderby clause
     * for the DB query.
     *
     * @param string - DB table field to order by
     * @param string - order assension value.
     *
     * @return string.
     */
    function setup_order($orderby, $reverseorder) {
        if ($reverseorder == "true") {
            return "order by ".$orderby." DESC";
        }
        else {
            return "order by ".$orderby;
        }
    }

    /**
     * This builds the db query ORDER BY
     * clause used to sort the data from the DB.
     *
     * @return string - the order by clause
     */
    function build_order_clause() {

        // Set up reverseorder correctly
        foreach( $this->_columns as $col_name => $data ) {
            if (($data["data_name"] == $this->get_orderby()) &&
                ($data["reverseorder"] == "true") && 
                                !isset( ${$this->reverseorderVar})) {
                $this->set_reverseorder( $data["reverseorder"] );
            }
        }

        $orderClause = $this->setup_order($this->get_orderby(), $this->get_reverseorder() );
        return $orderClause;
    }

    /**
     * this builds the groupby clause of the query
     *
     * @return string
     */
    function build_groupby_clause() {
        $groupby = $this->_db_options["groupby_clause"];
        if ($groupby) {
            $groupby = " GROUP BY $groupby ";
        }

        return $groupby;
    }

    /**
     * build the entire DB query
     *
     * @param boolean - build the limit clause or not/
     * @return string - the full query string to the DB.
     */
    function build_query($limit_flag=TRUE) {

        $where_clause = $this->build_where_clause();
        $order_clause = $this->build_order_clause();
        $groupby_clause = $this->build_groupby_clause();
                if ($limit_flag) {
                        $limit_clause = $this->build_limit_clause($this->get_offset(), $this->get_limit());
                }

        $query = "select ".$this->get_db_values()." from ".$this->get_db_tables()." " .
                 $where_clause." ".$groupby_clause." ".$order_clause .
                             " ".$limit_clause;

        return $query;
    }


        function do_prequery() {
                //ok now count the total # of rows so
        //we can calculate the # of pages.
                //count rows
        //ok now count the total # of rows so 
        //we can calculate the # of pages.
                $this->set_total_rows( $this->count( $this->get_db_tables(),
                                                                                         $this->build_where_clause(),
                                                                                         $this->get_count_column() ) );

                $this->_query = $this->build_query();
        }


        /************************************************/
        /*            DB Specific Routines              */
        /*          ------------------------            */
        /*  These must be extended by the child class   */
        /* This gives you the power to do the specific  */
        /* DB interactions as you see fit.  Some folks  */
        /* use the low level php db API's, some use the */
        /* PEAR db abstraction objects, and others roll */
        /* their own DB objects.  Extend the functions  */
        /* to provide:                                  */
        /*                                              */
        /* do_query() - this function's purpose is to   */
    /*              execute the sql query that is   */
        /*              available in $this->_query by   */
        /*              the time do_query() is called.  */
        /*                                              */
        /* get_next_data_row() - This function returns  */
        /*              an associative array of the     */
        /*              next row of data from the query.*/
        /*                                              */
        /* build_limit_clause() - This function builds  */
        /*              and returns the "limit clause"  */
        /*              portion of a sql query.  Each   */
        /*              DB implementation is different  */
        /*              on how it deals with limiting   */
        /*              the amount of data you want     */
        /*                              back from the query.  Oracle    */
        /*                              has no limit clause, so it has  */
        /*                              to do this in very tricky/nasty */
        /*                              ways.                                                   */
        /*                                              */
        /* count() - this function simply returns the   */
        /*           number of rows (to be) found in    */
        /*           sql query.                         */
        /*                                              */
        /************************************************/

    /**
     * Do the query to the DB and pull in all of
     * the records locally.
     *
     * @return boolean - the query passed/failed.
     */
    function do_query() {
                //execute the sql query.
                //we don't actually get the results of
                //the query here, when just execute it.
                user_error("SQLDataListSource::do_query() - Child must override");
        return false;
    }

        /**
         * This function gets the next data row
         * from the query()
         *
         * @return array()
         */
        function get_next_data_row() {
           user_error("SQLDataListSource::get_next_data_row() - Child must override");
       return array();
        }       

    /**
     * This function builds the limit
     * clause portion of a DB query.
         * This is specific to the particular
         * DB implementation.  
     *
     * @return string - the limit portion of 
     *                  the query.
     */
    function build_limit_clause($offset, $limit) {
                user_error("SQLDataListSource::build_limit_clause() - Child must override");
    }

    /**
     * find the number of rows to be returned 
     * from a query from a table and where clause
     *
     * @param string $table - the table to count from
     * @param string $where_clause - a where clause
     *
     * @return int the # of rows
     */
    function count($tables, $where_clause='', $count_clause='*') {
                user_error("SQLDataListSource::count() - Child must override");
    }

}

?>
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	?>
MailToCvsAdmin">MailToCvsAdmin	ViewVC Help
Powered by ViewVC 1.1.26