How to count the number of rows in an SQL statement?

Question

How to count the number of rows in an SQL statement?

Navigation

#1 by (0 votes)

-1

I'm in this SQL, retrieving records (number of providers per city) from a table and would like to count how many records it has in each city . I want in front of the cities, put something like Tal (9) . I will put a print of the result of this SQL to help better visualize the question:

publicfunctiongetFornsRegion($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();
}

mysql query codeigniter contador

asked by anonymous 24.10.2016 / 18:17

1 answer

Find and delete different characters between strings Sending XML to webservice

score 0 · Answer 1

Then we can put a counter inside the SQL statement itself and in result_array() retrieve the result of the counter as shown 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();
}