Example #1
0
 /**
  * Export the query results to the current output buffer.
  * 
  * Options:
  *   col_escape:           Array of booleans, true means to escape as string, false means no escaping
  *   output_callback       Output callback function for ob_start() OPTIONAL (defaults to 'ob_gzhandler')
  *   column_heads          Output column names in the first row OPTIONAL (defaults to true)
  * 
  * @param object $select   Select object
  * @param array  $columns  Array of column names as displayed in CSV,
  *                         must match the number of columns in the select
  * @param array  $options  Options (see above)
  */
 public function export(coreDatabaseSelect $select, $columns, $options = array())
 {
     $this->options = array_merge(array('output_callback' => 'ob_gzhandler', 'column_heads' => true), $options);
     ob_start($this->options['output_callback']);
     $numColumns = count($columns);
     if (isset($options['column_heads']) && $options['column_heads'] === true) {
         echo implode(self::FIELDS_TERMINATED_BY, $columns) . self::LINE_TERMINATED_BY;
     }
     // what columns to escape as strings
     $escapeCol = isset($options['col_escape']) ? $options['col_escape'] : null;
     $select->query();
     while ($row = $this->db->fetch(coreDatabase::FETCH_NUM)) {
         $cells = array();
         for ($i = 0; $i < $numColumns; $i++) {
             $t = $row[$i];
             if ($escapeCol !== false && $escapeCol[$i]) {
                 // escape string values
                 $t = preg_replace('/\\t/', '\\t', $t);
                 $t = preg_replace('/"/', '""', $t);
                 $t = '"' . $t . '"';
             }
             $cells[] = $t;
         }
         echo implode(self::FIELDS_TERMINATED_BY, $cells) . self::LINE_TERMINATED_BY;
     }
     return ob_get_clean();
 }
Example #2
0
function selectTests($db, $test)
{
    $query = $db->select()->from('TestTable');
    $test->out($query, "select() with no specified columns defaults to '*'");
    $query = $db->select(array('t.age', 't.firstname'))->from(array('t' => 'TestTable'));
    $test->out($query, "aliased table");
    $query = $db->select('single_column')->from('TestTable');
    $test->out($query, "single column");
    $query = $db->select(array('multiple', 'columns'))->from('TestTable');
    $test->out($query, "multiple columns");
    // mutliple where clauses
    $query = $db->select(array('multiple', 'columns'))->from('TestTable')->where('id = ? AND age>?', array(15, 33));
    $test->out($query, "multiple where clauses");
    // group by
    $query = $db->select()->from('TestTable')->group('group_col');
    $test->out($query, "group");
    // order by single
    $query = $db->select()->from('TestTable')->order('order_col1 ASC');
    $test->out($query, "order single");
    // order by multiple
    $query = $db->select()->from('TestTable')->order(array('order_col1 ASC', 'order_col2 DESC'));
    $test->out($query, "order multiple");
    // selected expression and alias
    $query = $db->select(array('productid', 'cost_plus_tax' => 'p.cost * 1.08'))->from('TestTable');
    $test->out($query, "selected expression, alias, order");
    // join
    $query = $db->select()->from(array('t' => 'TestTable'))->join('table2', 't.id = table2.id');
    $test->out($query, "join");
    // joinUsing
    $query = $db->select()->from(array('t' => 'TestTable'))->joinUsing('table2', 'id');
    $test->out($query, "joinUsing");
    // joinLeft
    $query = $db->select()->from(array('t' => 'TestTable'))->joinLeft('table2', 't.id = table2.id');
    $test->out($query, "joinLeft");
    // joinLeftUsing
    $query = $db->select()->from(array('t' => 'TestTable'))->joinLeftUsing('table2', 'id');
    $test->out($query, "joinLeftUsing");
    // where single
    $query = $db->select()->from('TestTable')->where('firstname = ?', 'John d\'Oe "le Quoted"');
    $test->out($query, "single where, quotes are escaped");
    // multiple where() calls
    $query = $db->select()->from('TestTable')->where('a = ?', 1)->where('b != ?', 2);
    $test->out($query, "multiple where() calls");
    // limit
    $query = $db->select()->from('TestTable')->limit(10, 4);
    $test->out($query, "limit(10, 4)");
    // limitPage
    $query = $db->select()->from('TestTable')->limitPage(2, 10);
    $test->out($query, "limitPage(2, 10)");
    // reset and replace part of previous query
    $query = $db->select()->from('TestTable')->limitPage(2, 10);
    $query->reset(coreDatabaseSelect::COLUMNS);
    $query->columns(array('snoopy', 'count' => 'COUNT(*)'));
    $query->limit(2008);
    $test->out($query, "reset() and columns() replacement");
    // create a select directly without passing through Database
    $query = new coreDatabaseSelect($db);
    $query->columns(array('firstname', 'age'));
    $query->from('super_clients');
    $test->out($query, "create a select directly");
}
Example #3
0
 /**
  * Apply user id filter to select object.
  * 
  * @param  coreDatabaseSelect  $select
  * @param  int   $userId
  * 
  * @return coreDatabaseSelect
  */
 private static function filterByUserId(coreDatabaseSelect $select, $userId)
 {
     return $select->where('userid = ?', $userId);
 }
Example #4
0
 /**
  * Return a copy of the Select object with current sorting applied to it.
  * 
  * @param  coreDatabaseSelect $select
  * @return coreDatabaseSelect
  */
 private function applySorting(coreDatabaseSelect $select)
 {
     $this->sortColumn = $this->request->get(self::QUERY_SORTCOLUMN, $this->settings['sortColumn']);
     if ($this->sortColumn === null) {
         // sort by default on first data column, ascending
         $this->sortColumn = isset($this->columns[0]->colSort) ? $this->columns[0]->colSort : $this->columns[0]->colData;
     }
     $this->sortOrder = intval($this->request->get(self::QUERY_SORTORDER, $this->settings['sortOrder']));
     $this->sortOrder = $this->sortOrder % count($this->sortOrders);
     return $select->order($this->sortColumn . ' ' . $this->sortOrders[$this->sortOrder]);
 }