/**
* 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();
}
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");
}
/**
* 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);
}
/**
* 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]);
}