var $key; // key field name
// db_pager constructor
- // accepts $sql like 'SELECT ...[FROM ...][WHERE ...][GROUP ...][ORDER ...]'
+ // accepts $sql query either as:
+ // a. string in form 'SELECT field_list FROM table_joins [WHERE conditions [GROUP group_list [ORDER order_list]]]'
+ // - if WHERE keyword is used in table_joins, WHERE conditions is obligatory
+ // b. associative array using select, where, group and order keys ex :
+ // array('select' => 'SUM(quantity)', 'from' => TB_PREF."stock_moves", 'group' => 'location')
+ //
// $name is base name for pager controls
function db_pager($sql, $name, $table = null, $page_len=0)
{
$this->set_sql($sql);
}
//
- // Parse base sql select query.
- // or use an associative array.
- // Usefull is the query can't be split correctly (using subquery for example).
- // The associative array shouldn't contain the keyword itself.
- // ex :
- // array('select' => 'SUM(quantity)', 'from' => TB_PREF."stock_moves", 'group' => 'location')
+ // Parse base sql select query or use an associative array.
+ //
function set_sql($sql)
{
+ global $SysPrefs;
+
if ($sql != $this->sql) {
$this->sql = $sql;
$this->ready = false;
}
else {
// parse the query
- $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2);
- if(count($parts) == 2) {
- $sql = $parts[0];
- $this->order = $parts[1];
+ $parts = preg_split('/\sFROM\s/si', $sql, 2);
+ if (count($parts) == 2) {
+ $this->select = $parts[0];
+ $sql = $parts[1];
+ } else {
+ if ($SysPrefs->go_debug)
+ display_error("Invalid sql input for db_pager");
}
- $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2);
- if(count($parts) == 2) {
- $sql = $parts[0];
- $this->group = $parts[1];
- }
- $parts = preg_split('/\sWHERE\s/si', $sql, 2);
- if(count($parts) == 2) {
- $sql = $parts[0];
- $this->where = $parts[1];
- }
- $parts = preg_split('/\sFROM\s/si', $sql, 2);
+
+ $parts = preg_split('/\sWHERE(?!.*WHERE.*)\s/si', $sql, 2); // last occurence
if(count($parts) == 2) {
- $sql = $parts[0];
- $this->from = $parts[1];
+ $this->from = $parts[0];
+ $sql = $parts[1];
+
+ $parts = preg_split('/\sORDER\s*BY\s/si', $sql, 2);
+ if(count($parts) == 2) {
+ $sql = $parts[0];
+ $this->order = $parts[1];
+ }
+ $parts = preg_split('/\sGROUP\s*BY\s/si', $sql, 2);
+ if(count($parts) == 2) {
+ $sql = $parts[0];
+ $this->group = $parts[1];
+ }
+ $this->where = $sql;
}
- $this->select = $sql;
}
}
+ //_vd($this->select);
+ //_vd($this->from);
+ //_vd($this->where);
+ //_vd($this->group);
+ //_vd($this->order);
}
//
// Set additional constraint on record set
//
function sort_table($col)
{
+
+ $max_priority = 0;
+ foreach($this->columns as $id => $_col) {
+ if(!isset($_col['ord_priority'])) continue;
+ $max_priority = max($max_priority, $_col['ord_priority']);
+ };
+
+
$ord = $this->columns[$col]['ord'];
+ $this->columns[$col]['ord_priority'] = $max_priority+1; // set priority , higher than anything else
$ord = ($ord == '') ? 'asc' : (($ord == 'asc') ? 'desc' : '');
$this->columns[$col]['ord'] = $ord;
$this->set_page(1);
} else
return false;
return true;
- }
+ }
//
// Calculates page numbers for html controls.
//
if ($group) $sql.= " GROUP BY $group";
$ord = array();
+ // sort order column by priority instead of table order.
+ $columns = array();
foreach ($this->columns as $col) {
+ if(isset($col['ord_priority'])) {
+ $columns[$col['ord_priority']] = $col;
+ }
+ }
+ krsort($columns);
+
+ foreach ($columns as $col) {
if (isset($col['ord'])) {
if ( $col['ord'] != '' && isset($col['name'])) {
$ord[] = $col['name'] .' '. $col['ord'];
}
if (count($ord)) {
- $ord = array_map(function_exists('mysql_real_escape_string') ?
- 'mysql_real_escape_string': 'mysql_escape_string', $ord);
+ $ord = array_map('db_escape_function', $ord);
$sql .= " ORDER BY " . implode(',', $ord);
} else {
if($order)
//
function _init()
{
- global $go_debug;
+ global $SysPrefs;
if ($this->ready == false ) {
$sql = $this->_sql_gen(true);
$this->max_page = $this->page_len ?
ceil($this->rec_count/$this->page_len) : 0;
- if ($go_debug) { // FIX - need column name parsing, but for now:
+ if ($SysPrefs->go_debug) { // FIX - need column name parsing, but for now:
// check if field names are set explicite in col def
// for all initially ordered columns
foreach ($this->columns as $col) {
if (isset($col['ord']) && $col['ord'] != ''
&& !isset($col['name'])) {
display_warning("Result field names must be set
- for all intially ordered db_pager columns.");
+ for all initially ordered db_pager columns.");
}
}
}
if (isset($_SESSION[$name]))
$_SESSION[$name]->ready = false;
}
-?>
\ No newline at end of file