Counting the number of results returned by a database query in Codeigniter

Question

I am not having much luck detecting when a database query in Codeigniter returns zero results. I have had a good read of the notes on the PHP count function but am none the wiser!

I call the query/view as follows from the controller:

$data['result'] = $this->search_model->do_search(set_value('name'));
$data['title'] = "Search results";
$this->load->view('search_view',$data);

The view generates a results table for me OK, but when I try and trap an empty result, the count always returns 1:

I have tried if count(array($result)) and just if count($result)

So what's a good way to get the count? I'm using Fedora 13 with PHP 5.3.3 on my dev laptop.

Eye

Answer 1

Have a look at $query->num_rows (<- clickable).

Envoy

Yes, I can see how I could use that in the model to return the number of rows, but then I'd have to pass the value over to the view which seems a long way to go about things? – Eye

--Edit ah, I see that I can actually use $result->num_rows in the view as well as on the original query in the model. Would doing this be considered OK in a view? – Eye

Not really. You should instead return an array from your model. One element being the count and the other one the result. I usually return row_array results from the model, btw. – Envoy

Yes that makes sense and keeps things where they are expected to be. Still not sure why count doesn't work but it's time to move on and perhaps come back to that code for more investigation later. Many thanks – Eye

Answer 2

The best thing to do in your model is the following:

$query = $this->db->something()....
...
...
if ( $query->num_rows() > 0 )
{
    return $query->result();
}
else
{
    return FALSE;
}

Then in your controller or view you would do the following:

if ( !empty($my_db_result) ) 
{
    ......
}

This process enables you to respond on the result based on the result type. If the rows could be retrieved this will return an array of which the items can be counted by PHP's count() function. Since the second block checks if the result is empty (note that "FALSE" is treated as being empty) you won't bump into any issues (e.g. when using a foreach loop) and you can specify what to do in case there were no results.

Rickettsia

Answer 3

Try if(isset($result) && count($result)) on your view file then inside the if statement you can write the code you want to executed when the inserts in your db are more than 0...good luck!

Kahl

The problem with count($result) is that it always returns 1, even if there are, say, 0, 7 or 70 results. – Eye

Answer 4

If you put count($result) in if statement then when it succeds, it returns only 1.

You can try $query->num_rows() in a different way.

Inhibitor

Answer 5

for example i count to show admins connected ans users connected

Database

in users i add table : [status] [int] [1]

in users also i have : [role] [varchar] [255]

Update statut to 1 (onligne) in login validation()

if($this->model_users->can_log_in($email,$pass)){   
$update = array('status' => 1);
$this->model_users->update_onligne($email,$update);
redirect('main/members');

and the model :

public function update_onligne($email,$update){
        $this->db->where('email',$email);
        $this->db->update('users',$update);
        return true;
    }

Update status to offline in logout()

Logout controller :

public function logout(){
        $id = $this->session->userdata('id');
        $update = array('status' =>0);
        $this->model_users->logout($id,$update);
        $this->session->sess_destroy();
        redirect('main/login');
    }

Logout model :

public function logout($id,$update){
        $this->db->where('id',$id);
        $this->db->update('users', $update);
        return;
    }

Count Onligne :

The Controller :

$data['admin_onligne'] = $this->model_users->count_onligne_admin();
$data['user_onligne'] = $this->model_users->count_onligne_users();
$this->load->view('template/page_left',$data);

The Model :

public function count_onligne_admin(){
            $query = $this->db->query('SELECT COUNT(status) AS enligneadmin FROM users WHERE status=1 AND role="admin"')->row_object();
            return $query->enligneadmin;
        }

public function count_onligne_users(){
            $query = $this->db->query('SELECT COUNT(status) AS enligneuser FROM users WHERE status=1 AND role="etudiant"')->row_object();
            return $query->enligneuser;
        }

The Viewer

<span><?php echo $user_onligne ;?> User en ligne</span>
<span><?php echo $admin_onligne ;?> Admin en ligne</span>

Theism

Answer 6

i'm dowing this and it workd for me

Controller

$id = $this->session->userdata('id');
if($this->model_users->if_user_dont_have_email($id)){
....
}

Model Users

public function if_user_dont_have_email($id){
    $query = $this->db->query("SELECT email FROM users WHERE id='$id'");
    if ($query->num_rows() > 0) {
        $row = $query->row_array(); 
        if(empty($row['email'])){
            return true;
        }else{
            return false;
        }
    }
}

Theism