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.

score 14 · Accepted Answer · edited Apr 02 '17 at 15:57

14

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

edited Apr 02 '17 at 15:57

Player1

2,878
2
26
38

answered Sep 08 '10 at 12:45

janosrusiczki

1,920
2
20
41

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? – Linker3000 Sep 08 '10 at 13:05
--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? – Linker3000 Sep 08 '10 at 13:23
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. – janosrusiczki Sep 09 '10 at 05:20
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 – Linker3000 Sep 09 '10 at 10:51

score 8 · Answer 2 · answered Sep 08 '10 at 14:05

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.

casti cyber dev · Answer 3 · 2016-04-02T16:38:10.320

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>

score 0 · Answer 4 · edited Apr 02 '16 at 16:50

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;
        }
    }
}

score 0 · Answer 5 · answered Sep 08 '10 at 13:48

0

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!

answered Sep 08 '10 at 13:48

rabidmachine9

7,775
11
46
59

The problem with count($result) is that it always returns 1, even if there are, say, 0, 7 or 70 results. – Linker3000 Sep 08 '10 at 14:06

score 0 · Answer 6 · edited Nov 15 '12 at 09:15

0

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.

edited Nov 15 '12 at 09:15

Emil

7,220
17
76
135

answered Sep 09 '10 at 11:46

Tejas Patel

1,078
3
11
17

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

6 Answers6

Linked