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 gets the 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->_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.
     *
     */
    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");
    }

        /**
         * 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");
        }       

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