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	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
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