CodeIgniter : Get value from single column from table

Question

I have a table that contains personal data, such as name, gender, address, etc. my question is, how to get the total number of men and women from my table?

This is my SQL query :

SELECT gender, COUNT(*) AS total
FROM personaldata
GROUP BY gender

This is my code so far in my model :

public function gender_count()
{
    $this->db->select('gender, count(*) as total');
    $this->db->from('personaldata');
    $this->db->group_by('gender');

    $query = $this->db->get()->row()->total;
    return $query->result();
}

So how to write that SQL query to CI model and controller? I just want to show the total gender in my site.

Thank you

Answer 1

There are two ways to do that, you can choose which one you feel ease

Model:

public function gender_count()
{
    $response = array();
//for female
    $this->db->select('count(id) as ftotal');//id should be primary key
    $this->db->from('personaldata');
    $this->db->where('gender','f');
    $this->db->group_by('gender');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        $response['f'] = $query->row('ftotal');
    }else{
        $response['f'] = 0;
    }
//for male
    $this->db->select('count(id) as mtotal');//id should be primary key
    $this->db->from('personaldata');
    $this->db->where('gender','m');
    $this->db->group_by('gender');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        $response['m'] = $query->row('mtotal');
    }else{
        $response['m'] = 0;
    }
    return $response;//assosiative array
}

OR

public function gender_count()
{
    $response = array();
    $this->db->select('gender,count(id) as total');//id should be primary key
    $this->db->from('personaldata');
    $this->db->group_by('gender');
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        foreach($query->result() as $row){
          $response[$row->gender] = $row->total;
        }
    }else{
        $response['f'] = 0;
        $response['m'] = 0;
    }
    return $response;//assosiative array
}

Controller:

$data = array();
$genderCount = $this->Gender_model->gender_count();
$data['fcount'] = $genderCount['f'];
$data['mcount'] = $genderCount['m'];
$this->load->view('genderCount',$data);

View:

<span>Female:</span><b><?php echo $fcount;?></b>
<span>Male:</span><b><?php echo $mcount;?></b>

Answer 2

Your can get total gender value by Count(*) and send that value from @OutputValue

Declare  @OutputValue int
select @OutputValue=COUNT(*) from personaldata

Answer 3

You can use below function in your model and call it from controller.

Model

public function gender_count()
{
    $this->db->select('gender, COUNT(*) as total');
    $this->db->group_by('gender'); 
    $query = $this->db->get('users', 10)->result();

    return $query;
}

Controller

public function index()
{
    $this->load->database();
    $this->load->model('User');

    $result = $this->User->gender_count();

    echo '<pre>';
    print_r($result);
    die;
}

Note : I use my model name and table name as example so you need to use your model name and table name.

I hope this will work for you.