Select Count(*) problem

[eluser]jacobson[/eluser]
Hello I have a problem with "passing" the number of records (count(*))

I have a model:

Code:

$this->db->select('value1');
        $this->db->group_by('value1');
        $q = $this->db->get(TBL_1);
        $result = $q->result();
        if(!$result || ($q->num_rows() < 0)){
            echo "Error";
            return;
        }
        if($q->num_rows() == 0){
            return;
        }
        return $result;

My controller passes the informations to the view which looks like:

Code:

<table border="1px">
    &lt;?php
    foreach($records as $row) {
        echo "<tr><td>" . $row->value1 . "</td><td>" .  $this->db->count_results($row->value1) . "</td></tr>";
    }
    ?&gt;
    </table>

In the place where I "try" to count results in the view i want to have the number of records where the field is equal to "value1".
I'm quite sure that I'm doing something wrong and that I should cound those values in my model. But my controller :

Code:

$this->load->model('data_selection_model');
        $data['records'] = $this->data_selection_model->display_data_number();

assigns to $records the value of $result from the model which stores the value1. I tried at first making in model select ('value1', 'count(*)') but in the view there appears the problem. i can do $row->value1 but I cant do $row->count(*).

I hope someone could help me with this problem

[eluser]Ngulo[/eluser]
$this->db->select('count(*) as value1,value2');

tryed?

[eluser]Aken[/eluser]
The count_all_results() function is designed to be used with other active record database methods. It is an alternate to methods such as get(). It can not be used on a result set returned from other DB methods. You will want to use the COUNT() function as suggested by Ngulo.

Model:

Code:

$q = $this->db->select('value1, COUNT(1) valuetotal')->group_by('value1')->get(TBL_1);
$result = $q->result();
$numrows = $q->num_rows();

if ( ! $result || $numrows < 0) {
    echo 'Error'; return;
}
else if ($numrows === 0) {
    return;
}
else {
    return $result;
}

View:

Code:

<table border="1px">
    &lt;?php foreach ($records as $row) {
        echo '<tr><td>' . $row->value1 . '</td><td>' . $row->valuetotal . '</td></tr>';
    }
    ?&gt;
</table>

Note: Code is untested, you may need to modify it to suit. But that's the basic theory you need.

[eluser]jacobson[/eluser]
Ngulo solution works great, thanks

[eluser]Chillahan[/eluser]
The problem in getting count with the main query is there's no way to access the count separately, without peeling off one row of the returned object collection, right? (like if one needs a count for pagination) So I still do a separate count query (although it's just COUNT(id), so it should be quicker than selecting * and actually returning data).

Any thoughts on whether I can improve this?