// $name is base name for pager controls
function db_pager($sql, $name, $table = null, $page_len=0)
{
- global $table_style;
$this->width = "95%";
if ($page_len == 0) $page_len = user_query_size();
$this->name = $name;
}
//
// 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')
function set_sql($sql)
{
if ($sql != $this->sql) {
$this->sql = $sql;
$this->ready = false;
- $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];
- }
- $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);
- if(count($parts) == 2) {
- $sql = $parts[0];
- $this->from = $parts[1];
+
+ if(is_array($sql)) {
+ foreach(explode(' ', 'select from where group order') as $section) {
+ $this->$section = @$sql[$section];
+ }
+ $this->select = "SELECT ".$this->select;
+ }
+ 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('/\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);
+ if(count($parts) == 2) {
+ $sql = $parts[0];
+ $this->from = $parts[1];
+ }
+ $this->select = $sql;
}
- $this->select = $sql;
}
}
//
//
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);
$c = 0;
// add result field names to column defs for
// col value retrieve and sort purposes
- $cnt = min(mysql_num_fields($result), count($this->columns));
+ $cnt = min(db_num_fields($result), count($this->columns));
for ($c = $i = 0; $c < $cnt; $c++) {
if (!(isset($this->columns[$c]['insert']) && $this->columns[$c]['insert'])) {
// if (!@($this->columns[$c]['type']=='skip'))
- $this->columns[$c]['name']= mysql_field_name($result, $i);
+ $this->columns[$c]['name']= db_field_name($result, $i);
if (!@($this->columns[$c]['type']=='insert'))
$i++;
}
if(count($this->extra_where)) {
$where .= ($where=='' ? '' : ' AND ')
- .implode( $this->extra_where, ' AND ');
+ .implode(' AND ', $this->extra_where);
}
if ($where) $where = " WHERE ($where)";
- if ($count) {
- $group = $group == '' ? "*" : "DISTINCT $group";
-
- return "SELECT COUNT($group) FROM $from $where";
- }
+ if ($count)
+ return "SELECT COUNT(*) FROM ($this->sql) tmp_count";
$sql = "$select FROM $from $where";
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)) {
- $sql .= " ORDER BY " . implode($ord, ',');
+ $ord = array_map(function_exists('mysql_real_escape_string') ?
+ 'mysql_real_escape_string': 'mysql_escape_string', $ord);
+ $sql .= " ORDER BY " . implode(',', $ord);
} else {
if($order)
$sql .= " ORDER BY $order"; // original base query order
update_record_status($id, !$value, $table, $key);
$value = !$value;
}
- echo '<td align="center">'. checkbox(null, $name, $value, true, '', "align='center'")
+ echo '<td align="center">'. checkbox(null, $name, $value, true, '')
. hidden("LInact[$id]", $value, false) . '</td>';
}
} else
if (isset($_SESSION[$name]))
$_SESSION[$name]->ready = false;
}
-?>
\ No newline at end of file
+?>