Count database rows using where clues [duplicate]

Question

I have a MySQL table and a HTML search form. so according to a user request he can search the database using that HTML form.

I use CodeIgniter and this is the model function i'm using to query the database

public function fetch_categories($limit, $start){

    $user_info = $this->session->userdata('search_info');

    $iam            =   $user_info['iam'];
    $searching_for  =   $user_info['searching_for'];
    $age_from       =   $user_info['age_from'];
    $age_to         =   $user_info['age_to'];
    $country        =   $user_info['country'];
    $Province       =   $user_info['Province'];

    $this->db->where('sex !=', $iam);
    $this->db->where('sex', $searching_for);
    $this->db->where('Age >=' , $age_from);
    $this->db->where('Age <=' , $age_to);
    if($Province != 1){
        $this->db->where('Province' , $Province);
    }

    $this->db->limit($limit, $start);
    $query = $this->db->get("members");
    return $query->result_array();
            }

This is working properly but I need to add pagination to the result so again I use CodeIgniter's built-in pagination library.

So I want to count the result before fetch_categories() function execute so I use search_count() function as this.

public function search(){
     $user_info = $this->session->userdata('search_info');

        $iam            =   $user_info['iam'];
        $searching_for  =   $user_info['searching_for'];
        $age_from       =   $user_info['age_from'];
        $age_to         =   $user_info['age_to'];
        $country        =   $user_info['country'];
        $Province       =   $user_info['Province'];

        $this->db->where('sex !=', $iam);
        $this->db->where('sex', $searching_for);
        $this->db->where('Age >=' , $age_from);
        $this->db->where('Age <=' , $age_to);

        if($Province != 1){
            $this->db->where('Province' , $Province);
        }
            $query = $this->db->count_all('members');
            return $query;       
    }

But this thing always return the whole row count as result. so there is unwanted page numbers in the page. I know it is happening because of this.

$query = $this->db->count_all('members');

But I don't know how to count only the related things using this type of a function.

possible duplicate of Counting the number of results returned by a database query in Codeigniter — RandomSeed
– RandomSeed, Commented Jun 15, 2013 at 7:06
@YaK- yes i also think i'm doing a duplicate thing using this two functions. but any how i have to send row count to create pagination. and then i have to send the values. i don't know is there is a way that i can return those two things in one function. if you need i can post here my controller codes also . — Yasitha
– Yasitha, Commented Jun 15, 2013 at 8:59

umefarooq · Accepted Answer · 2013-06-15 07:19:57Z

1

hi if you want to use where clause with active records count then use this method count_all_results()

 $this->db->where('sex !=', $iam);
 $this->db->where('sex', $searching_for);
 $this->db->where('Age >=' , $age_from);
    $this->db->where('Age <=' , $age_to);

    if($Province != 1){
        $this->db->where('Province' , $Province);
    }
$this->db->count_all_results()

answered Jun 15, 2013 at 7:19

umefarooq

4,5941 gold badge31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MJ X · Accepted Answer · 2013-06-15 08:17:33Z

0

Try this method:

 $this->db->where('sex !=', $iam);
 $this->db->where('sex', $searching_for);
 $this->db->where('Age >=' , $age_from);
 $this->db->where('Age <=' , $age_to);

 if($Province != 1)
 {
    $this->db->where('Province' , $Province);
 }
 $this->db->from('members');
 $query = $this->db->get();
 if($query)
 {
    return $query->num_rows();
 }
 else
 {
    return FALSE;
 }

answered Jun 15, 2013 at 8:17

MJ X

9,12413 gold badges85 silver badges124 bronze badges

Collectives™ on Stack Overflow

Count database rows using where clues [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related