CODEWITHSUNDEEP
phpmysqlcodeigniter
user1494854
user1494854

Reputation: 171

group by and count is not counting correct in codeigniter

I am having problem to display correct numbers of count(field). I am trying to group by field and want to return order by group and count of them.

controller is 

    $this->load->library('pagination');

    $query = "SELECT usercode,count(usercode) AS co FROM tabs
    GROUP BY usercode ORDER BY co desc";

    $config['total_rows'] = $this->db->query($query)->num_rows();
    $config['num_links'] = '25';
    $config['uri_segment'] = 3;
    $config['base_url'] = base_url() . "/user/topCreators/";
    $config['per_page'] = '25';
    $config['anchor_class'] = " class=\"number\" ";
    $config['cur_tag_open'] = "<a href=\"#\" class=\"number 
    current\" title=\"Current Page\">";
    $config['cur_tag_close'] = "</a>";

    $this->pagination->initialize($config);
    if ($this->uri->segment(3) == FALSE){
        $offset = 0;
    }
    else
    {
        $offset = $this->uri->segment(4);
    }

    $limit = $config['per_page'];
    $data["total_records"] = $config['total_rows'];
    $data["page_links"] = $config["per_page"];


    $data["query"] = $this->db->query($query . " LIMIT $limit OFFSET $offset");

    $this->load->view("top_creators", $data);

my view file is

   <?php foreach($query->result() as $me) {?>
   <?= $me->co?>  

   <?php }?>

Upvotes: 0

Views: 422

Answers (2)

Robin Castlin
Robin Castlin

Reputation: 10996

You should explain in more details what doesn't work in your case.

The best practice when dealing with pagination in my opinion is to use SQL_CALC_FOUND_ROWS.

Basicly this lets you run one query and fetch the amount of total rows through a

// Main query with SQL_CALC_FOUND_ROWS

$row = $this->db->query("SELECT FOUND_ROWS() AS found")->row();
$config['total_rows'] = $row->found;

The SQL_CALC_FOUND_ROWS counts all rows but returns only the ones within LIMIT. This lets you do less queries towards your system (less lag) and will always give the correct total_rows since you get the values from the same query.

Upvotes: 0

SeanCannon
SeanCannon

Reputation: 77956

co will be the same number for each result since you're doing a count of all usercodes in the table tabs, therefore your ordering won't work. It also makes it redundant to execute $this->db->query($query)->num_rows(); because it's the same value as co

Upvotes: 1

Related Questions