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