/**
* Retrieves information from the database.
* Select utilizes parameters set previously in the object
* (i.e. addWhere, addColumn, setLimit, etc.)
* You may also set the "type" of result: assoc (associative)
* col (columns), min (minimum result), max (maximum result), one (a single column from a
* single row), row (a single row), count (a tally of rows) or all, the default.
* All returns an associate array containing the requested information.
*
*/
public function select($type = null, $sql = null)
{
if (empty($sql)) {
if (!empty($this->sql)) {
$sql =& $this->sql;
}
}
PHPWS_DB::touchDB();
if (isset($type) && is_string($type)) {
$type = strtolower($type);
}
$mode = $this->getMode();
$indexby = $this->getIndexBy();
if (!isset($sql)) {
$sql = $this->getTheQuery($type);
} else {
$mode = MDB2_FETCHMODE_ASSOC;
}
$sql = PHPWS_DB::prefixQuery($sql);
if ($this->_test_mode) {
var_dump($sql);
exit($sql);
}
if ($this->return_query) {
return trim($sql);
}
// assoc does odd things if the resultant return is two items or less
// not sure why it is coded that way. Use the default instead
switch ($type) {
case 'assoc':
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->getAssoc($sql, null, null, $mode);
break;
case 'col':
if (empty($sql) && empty($this->columns)) {
return PHPWS_Error::get(PHPWS_DB_NO_COLUMN_SET, 'core', 'PHPWS_DB::select');
}
if (isset($indexby)) {
PHPWS_DB::logDB($sql);
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
return PHPWS_DB::_indexBy($result, $indexby, true);
}
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->queryCol($sql);
break;
case 'min':
case 'max':
case 'one':
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->queryOne($sql, null, $mode);
break;
case 'row':
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->queryRow($sql, null, $mode);
break;
case 'count':
PHPWS_DB::logDB($sql);
if (empty($this->columns)) {
$result = $GLOBALS['PHPWS_DB']['connection']->queryRow($sql);
if (PHPWS_Error::isError($result)) {
return $result;
}
return $result[0];
} else {
$result = $GLOBALS['PHPWS_DB']['connection']->queryCol($sql);
if (PHPWS_Error::isError($result)) {
return $result;
}
return count($result);
}
break;
case 'count_array':
PHPWS_DB::logDB($sql);
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
return $result;
break;
case 'all':
default:
PHPWS_DB::logDB($sql);
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
if (isset($indexby)) {
return PHPWS_DB::_indexBy($result, $indexby);
}
return $result;
break;
}
}
/**
* Retrieves information from the database.
* Select utilizes parameters set previously in the object
* (i.e. addWhere, addColumn, setLimit, etc.)
* You may also set the "type" of result: assoc (associative)
* col (columns), min (minimum result), max (maximum result), one (a single column from a
* single row), row (a single row), count (a tally of rows) or all, the default.
* All returns an associate array containing the requested information.
*
*/
public function select($type = null, $sql = null)
{
if (empty($sql)) {
if (!empty($this->sql)) {
$sql =& $this->sql;
}
}
PHPWS_DB::touchDB();
if (isset($type) && is_string($type)) {
$type = strtolower($type);
}
$mode = $this->getMode();
$indexby = $this->getIndexBy();
if (!isset($sql)) {
$sql_array = $this->getSelectSQL($type);
if (PHPWS_Error::isError($sql_array)) {
return $sql_array;
}
// extract will get $columns, $table, $where, $group_by
// $order, and $limit
extract($sql_array);
if ($type == 'count' || $type == 'count_array') {
if (empty($columns)) {
// order and group_by are not needed if count is
// using all rows
$order = null;
$group_by = null;
$columns = 'COUNT(*)';
} else {
$add_group = $columns;
$columns .= ', COUNT(*)';
if (empty($group_by)) {
$group_by = "GROUP BY {$add_group}";
}
}
}
if (!empty($where)) {
$where = 'WHERE ' . $where;
}
if ($this->isDistinct()) {
$distinct = 'DISTINCT';
} else {
$distinct = null;
}
$sql = "SELECT {$distinct} {$columns} FROM {$table} {$where} {$group_by} {$order} {$limit}";
} else {
$mode = MDB2_FETCHMODE_ASSOC;
}
$sql = PHPWS_DB::prefixQuery($sql);
if ($this->_test_mode) {
exit($sql);
}
if ($this->return_query) {
return trim($sql);
}
switch ($type) {
case 'assoc':
/*
* Note: Previously, Pear's DB class would return two column results
* as an array with the key as the first column and the value as the
* second. This behavior was inconsistent with the expected functionality.
* We had code programmed expecting this behavior. MDB2 did not
* replicate it, so two column results were broken.
* The seemingly errant code below brings the result back to the
* expected action.
* Users should NOT depend on this code but should instead use the
* Global\DB class or use this class with setIndexBy and select('col').
*/
PHPWS_DB::logDB($sql);
if (count($this->columns) == 2) {
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
return PHPWS_DB::_indexBy($result, $this->columns[0]['name'], true);
}
return $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
break;
case 'col':
if (empty($sql) && empty($this->columns)) {
return PHPWS_Error::get(PHPWS_DB_NO_COLUMN_SET, 'core', 'PHPWS_DB::select');
}
if (isset($indexby)) {
PHPWS_DB::logDB($sql);
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
return PHPWS_DB::_indexBy($result, $indexby, true);
}
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->queryCol($sql);
break;
case 'min':
case 'max':
case 'one':
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->queryOne($sql);
break;
case 'row':
PHPWS_DB::logDB($sql);
return $GLOBALS['PHPWS_DB']['connection']->queryRow($sql, null, $mode);
break;
case 'count':
PHPWS_DB::logDB($sql);
if (empty($this->columns)) {
$result = $GLOBALS['PHPWS_DB']['connection']->queryRow($sql);
if (PHPWS_Error::isError($result)) {
return $result;
}
return $result[0];
} else {
$result = $GLOBALS['PHPWS_DB']['connection']->queryCol($sql);
if (PHPWS_Error::isError($result)) {
return $result;
}
return count($result);
}
break;
case 'count_array':
PHPWS_DB::logDB($sql);
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
return $result;
break;
case 'assoc':
case 'all':
default:
PHPWS_DB::logDB($sql);
$result = $GLOBALS['PHPWS_DB']['connection']->queryAll($sql, null, $mode);
if (PHPWS_Error::isError($result)) {
return $result;
}
if (isset($indexby)) {
return PHPWS_DB::_indexBy($result, $indexby);
}
return $result;
break;
}
}
