How to count the number of records (Rows) in an SQL statement?

Question

I am in this SQL, recovering the records (number of vendors per city) of a table and I would like to count how many records have in each city. I want to put something like Tal City (9). I will put a print of the result of this SQL to help you better view the question:

public function getFornsRegion($code)
{
    $this->db->select("fe.*"); 
    $this->db->from("fornecedores_empresa AS fe"); 
    $this->db->where("fe.regiao", $code);
    $this->db->group_by("fe.cidade");

    return $this->db->get()->result_array();
}

Answer 1

So we can put a counter inside the SQL statement itself and in the result_array() recover the result of the accountant as can be demonstrated in the code below:

public function getFornsRegion($code)
{
    $this->db->select("fe.*, count(fe.cidade) AS contador, fo.*, ci.*");
    $this->db->from("fornecedores_empresa AS fe");
    $this->db->join("fornecedores AS fo", "fe.idusuario = fo.id");
    $this->db->join("cidade AS ci", "ci.ct_id = fe.cidade");
    $this->db->where("fe.regiao", $code);
    $this->db->where("fo.nivel", 5);
    $this->db->where("fo.status", 1);
    $this->db->group_by("fe.cidade");
    $this->db->order_by("ci.ct_nome", "asc");

    return $this->db->get()->result_array();
}