From SQL query to Query in Codeigniter

Question

From SQL query to Query in Codeigniter

Navigation

#1 by (4 votes)
#2 by (3 votes)

1

I have the following query in SQL that works fine

SELECT MONTH(NOW()) AS mes, SUM(importe) as monto FROM pago_cliente WHERE MONTH(fecha_del_deposito) = MONTH(NOW()) AND YEAR(fecha_del_deposito) = YEAR(NOW()) GROUP BY 1

And I try to transform it in such a way so that it is a successful query in PHP, I use codeigniter for this and I have the following:

public function rowCountPagos($tabla)
{
    $this->db->select("MONTH(fecha_del_deposito) as mes, SUM(importe) as monto");
    $this->db->from($tabla);
    $this->db->where("fecha_del_deposito = MONTH(NOW()) AND YEAR(fecha_del_deposito) = YEAR(NOW())");
    $this->db->group_by("1");


    $resultados = $this->db->get();
    return $resultados->result();
}

But I can not get it to work and I find the following message

"Message: Undefined offset: 0"

any help with this? I thank you in advance.

sql php codeigniter

asked by González 27.03.2018 в 21:16

source

2 answers

3

What happens is that Codeigniter escapes the string with the quotes, then to avoid that and to consider MONTH(fecha_del_deposito) as a function of SQL what should do is pass the second parameter to $this->db->select() as false , same as in where but in the third parameter. You would have something like this:

public function rowCountPagos($tabla)
{
    $this->db->select("MONTH(fecha_del_deposito) as mes, SUM(importe) as monto", false);
    $this->db->from($tabla);
    $this->db->where("fecha_del_deposito", "MONTH(NOW())", false);
    $this->db->where("YEAR(fecha_del_deposito)", "YEAR(NOW())", false);
    $this->db->group_by("1");


    $resultados = $this->db->get();
    return $resultados->result();
}

More info

answered by 27.03.2018 в 21:39

How to do the following SQL query? Validate data and display bootstrap alerts

score 4 · Accepted Answer

Codeigniter has an option to make direct inquiries. Here is an example of how it would look.

public function rowCountPagos($tabla){ 
    $query = 'SELECT MONTH(NOW()) AS mes, SUM(importe) as monto 
        FROM '.$tabla.' 
        WHERE MONTH(fecha_del_deposito) = MONTH(NOW()) AND YEAR(fecha_del_deposito) = YEAR(NOW()) 
        GROUP BY 1';
    $resultados = $this->db->query($query);
    return $resultados->result();
}