[eluser]Unknown[/eluser]
No offense, but I think advising people to go ahead and do the whole
and then just do a num_rows() is pretty poor advice. Unless you're doing database level caching and are looking to cache that full query frequently or something.
However, typically the only reason I need a count_where type thing in the first place is to evaluate search results lengths.
Also, using
is less efficient than something like
Code:
select count(id) as thingcount
.
All that being said, I went ahead and boneheaded together a method that seems to preserve previous db->where declarations, but I'm unsure about what file to create in my custom lib folder. I tried making a MY_DB class that extended DB... no good. Then I did one for the sql driver, also no good.
Any suggestions?
Code:
function countwhere($table = '', $where = null, $fieldname = '*')
{
if ($table != '')
{
$this->from($table);
}
if (!is_null($where))
{
$this->where($where);
}
$sql = $this->_compile_count($fieldname);
$result = $this->query($sql);
$this->_reset_select();
return $result;
}
function _compile_count($fieldname = '*')
{
$sql = 'SELECT count('.$fieldname.') ';
if (count($this->ar_from) > 0)
{
$sql .= "\nFROM ";
$sql .= implode(', ', $this->ar_from);
}
if (count($this->ar_join) > 0)
{
$sql .= "\n";
$sql .= implode("\n", $this->ar_join);
}
if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
{
$sql .= "\nWHERE ";
}
$sql .= implode("\n", $this->ar_where);
if (count($this->ar_like) > 0)
{
if (count($this->ar_where) > 0)
{
$sql .= " AND ";
}
$sql .= implode("\n", $this->ar_like);
}
if (count($this->ar_groupby) > 0)
{
$sql .= "\nGROUP BY ";
$sql .= implode(', ', $this->ar_groupby);
}
if (count($this->ar_having) > 0)
{
$sql .= "\nHAVING ";
$sql .= implode("\n", $this->ar_having);
}
return $sql;
}
If we were men, we'd make some kind of _compile_conditions() method that both _compile_select and _compile_count used, but we're not men, we're mice.
Mason