3 // Controler part of database table pager with column sort.
4 // To display actual html object call display_db_pager($name) inside
7 // View definition you will find in the following file:
8 include_once($path_to_root."/includes/ui/db_pager_view.inc");
13 var $columns; // column definitions (head, type, order)
15 var $marker; // marker check function
40 // db_pager constructor
41 // accepts $sql like 'SELECT .... FROM .... [WHERE ...] [GROUP ...]'
42 // $name is base name for pager controls
43 function db_pager($sql, $name, $page_len=0)
45 if ($page_len == 0) $page_len = user_query_size();
47 $this->page_len = $page_len;
51 // Parse base sql select query.
53 function set_sql($sql)
55 if ($sql != $this->sql) {
59 $from = $where = $group = '';
60 $parts = preg_split('/\sFROM\s/si', $sql, 2);
61 if(count($parts) == 2) {
64 $parts = preg_split('/\sWHERE\s/si', $from, 2);
65 if(count($parts) == 2) {
68 $parts = preg_split('/\sGROUP\s*BY\s/si', $where, 2);
69 if(count($parts) == 2) {
75 $this->select = $select;
77 $this->where = $where;
78 $this->group = $group;
80 display_error("sql: $sql");
81 display_error($select);
82 display_error("FROM $from");
83 display_error("WHERE $where");
84 display_error("GROUP BY $group");
89 // Set additional constraint on record set
91 function set_where($where)
93 if (!is_array($where))
94 $where = array($where);
96 if (count($where) != count($this->extra_where) ||
97 count(array_diff($this->extra_where, $where))) {
98 $this->extra_where = $where;
103 // Set query result page
105 function change_page($page=null)
107 $this->set_page($page);
112 // Change sort column direction
113 // in order asc->desc->none->asc
115 function sort_table($col=null)
117 $ord = $this->columns[$col]['ord'];
118 $ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : '');
119 $this->columns[$col]['ord'] = $ord;
131 $Ajax->activate("_{$this->name}_span");
132 $this->data = array();
136 if ($this->rec_count == 0) return true;
138 $sql = $this->_sql_gen(false);
140 $result = db_query($sql, 'Error browsing database: '.$sql );
143 // setting field names for subsequent queries
145 // add result field names to column defs for
146 // col value retrieve and sort purposes
147 for ($c = $i = 0; $c < count($this->columns); $c++) {
148 if ($this->columns[$c]['type'] != 'insert')
149 $this->columns[$c]['name']= mysql_field_name($result, $i++);
152 while ($row = db_fetch_assoc($result))
153 $this->data[] = $row;
160 // Calculates page numbers for html controls.
162 function set_page($to)
166 $page = $this->curr_page+1; break;
168 $page = $this->curr_page-1; break;
170 $page = $this->last_page; break;
172 if (is_numeric($to)) {
180 $max = $this->max_page;
183 $this->curr_page = $page;
184 $this->next_page = ($page < $max) ? $page+1 : null;
185 $this->prev_page = ($page > 1) ? ($page-1) : null;
186 $this->last_page = ($page < $max) ? $max : null;
187 $this->first_page = ($page != 1) ? 1: null;
190 // Set column definitions
191 // $flds: array( fldname1, fldname2=>type,...)
192 function set_columns($flds)
194 $this->columns = array();
195 if (!is_array($flds)) {
196 $flds = array($flds);
198 foreach ($flds as $colnum=>$coldef) {
199 if (!is_numeric($colnum)) { // 'colname'=>params
202 } else { // n=>params
203 if (is_array($coldef)) {
212 if (!is_array($c)) // params is simple column type
213 $c = array('type'=>$c);
215 if (!isset($c['type']))
221 $c['head'] = $h; break;
222 case 'skip': // skip the column (no header)
223 unset($c['head']); // paranoid code
225 $this->columns[] = $c;
229 // Generate db query from base sql
230 // $count==false - for current page data retrieval
231 // $count==true - for total records count
233 function _sql_gen($count=false)
235 $select = $this->select;
237 $where = $this->where;
238 $group = $this->group;
240 if(count($this->extra_where)) {
241 $wherw .= ($where=='' ? '' : ' AND ')
242 .implode( $this->extra_where, ' AND ');
244 if ($where) $where = " WHERE($where)";
247 $group = $group == '' ? "*" : "DISTINCT $group";
249 return "SELECT COUNT($group) FROM $from $where";
252 $sql = "$select FROM $from $where GROUP BY $group";
255 foreach( $this->columns as $col) {
256 if (isset($col['ord'])) {
257 if ( $col['ord'] != '' && isset($col['name'])) {
258 $ord[] = $col['name'] .' '. $col['ord'];
264 $sql .= " ORDER BY " . implode($ord, ',');
266 $page_len = $this->page_len;
267 $offset = ($this->curr_page - 1) * $page_len;
269 $sql .= " LIMIT $offset, $page_len";
275 // Initialization after changing record set
279 if ($this->ready == false ) {
280 $sql = $this->_sql_gen(true);
281 $result = db_query($sql, 'Error reading record set');
282 if ($result == false)
284 $row = db_fetch_row($result);
285 $this->rec_count = $row[0];
286 $this->max_page = ceil($this->rec_count/$this->page_len);
293 // Set current page in response to user control.
295 function select_records()
299 $page = find_submit($this->name.'_page_', false);
300 $sort = find_submit($this->name.'_sort_', false);
302 $this->change_page($page);
304 $this->sort_table($sort);
309 // Set check function to mark some rows.
311 function set_marker($func, $notice='', $markercl='overduebg', $msgclass='overduefg' )
313 $this->marker = $func;
314 $this->marker_txt = $notice;
315 $this->marker_class = $markercl;
316 $this->notice_class = $msgclass;
319 //-----------------------------------------------------------------------------
320 // Creates new db_pager $_SESSION object on first page call.
321 // Retrieves from $_SESSION var on subsequent $_POST calls
323 // $name - base name for pager controls and $_SESSION object name
324 // $sql - base sql for data inquiry. Order of fields implies
325 // pager columns order.
326 // $coldef - array of column definitions. Example definitions
327 // Text column with title 'User name':
329 // Skipped field from sql query. Data for the field is not displayed:
331 // Column without title, formated with function func(). Field value
332 // is passed as parameter:
333 // array('type'=>'spec', 'fun'=>'func')
334 // Inserted column with title 'Some', formated with function rowfun(). Row
335 // values are passed as parameter array:
336 // 'Some' => array('type'=>'insert', 'fun'=>'rowfun')
337 // Column with name 'Another', formatted as date,
338 // sortable with ascending start order (available orders: asc,desc, '').
339 // 'Another' => array('type'=>'date', 'ord'=>'asc')
341 // All available column format types you will find in db_pager_view.inc file.
342 // If query result has more fields than count($coldef), rest of data is ignored
343 // during display, but can be used in format handlers for 'spec' and 'insert'
346 function &new_db_pager($name, $sql, $coldef, $page_len = 0) {
348 if (isset($name) && isset($_SESSION[$name])) {
349 // kill old pager if any on first page call
350 if ($_SERVER['REQUEST_METHOD'] == 'GET')
351 unset($_SESSION[$name]);
353 return $_SESSION[$name];
355 $ret =& new db_pager($sql, $name, $page_len);
356 $ret->set_columns($coldef);
358 if (isset($name)) $_SESSION[$name] = &$ret;