PHP Codeigniter + mysql query

Question

I’m new to the forum and I’d like a little help with a little problem that’s taken up a few hours already. I believe it may be some detail I’m not seeing..

Problem: Basically I need some information to compile a report, on Mysql Workbench I get exactly what I want, but when it comes to "translating" the query to the code I can’t get the same result (in vdd, it doesn’t return anything). The programming language is PHP in Codeigniter.

No Workbench:

select tec_payments.sale_id, tec_payments.paid_by as pagamento, 
tec_payments.date, tec_payments.amount, tec_sale_items.product_id, 
tec_sale_items.category_id  
from tec_payments
left join tec_sale_items on tec_sale_items.sale_id = tec_payments.sale_id
where tec_payments.date between '2019-07-03 00:00:00' and '2019-07-03 00:00:00'
and tec_sale_items.category_id = 14
and tec_payments.paid_by = 'cash';

Outcome of the Consultation:

sale_id | pagamento | date              |  amount     | product_id | category_id
   1        cash    2019-07-03 00:00:00 |   18.00        83           14
   1        cash    2019-07-03 00:00:00 |   18.00        83           14
   2        cash    2019-07-03 00:00:00 |   145.00       94           14
   2        cash    2019-07-03 00:00:00 |   145.00       95           14
   6        cash    2019-07-03 00:00:00 |   37.00        66           14
   6        cash    2019-07-03 00:00:00 |   37.00        43           14
   7        cash    2019-07-03 00:00:00 |   17.00        76           14

In the method I need to pass some parameters, but my model is getting everything right. Anyway, that’s it! From now on, thank you guys!

As suggested, here is the code in PHP (This is the model):

public function getItensDate($categoria = null, $start_date = null, $end_date = null) {

        $pagamento = 'cash';
        $whereCategoria = "";
        $whereData = "";
        $wherePagamento = "AND payments.paid_by = " . $pagamento;

        if ($start_date != null) {
        $whereData = "WHERE payments.date BETWEEN " . $this->db->escape($start_date) . " AND " . $this->db->escape($end_date);
        }
        if ($categoria != null) {
        $whereCategoria = "AND sale_items.category_id = " . $categoria;
        }

        $query =  "SELECT payments.sale_id, payments.paid_by as pagamento, payments.date, payments.amount, sale_items.product_id, sale_items.category_id 
                  FROM payments
                  LEFT JOIN sale_items ON sale_items.sale_id = payments.sale_id
                  $whereData $whereCategoria $wherePagamento";

        //print_r($query);
        //exit;

        return $this->db->query($query)->result();

    }

Answer 1

Try this:

public function getItensDate($categoria = null, $start_date = null, $end_date = null)
{

    $query = $this->db;
    $where = array();

    $query->select('payments.sale_id, payments.paid_by as pagamento, payments.date, payments.amount, sale_items.product_id, sale_items.category_id')
        ->from('payments')
        ->join('sale_items', 'sale_items.sale_id = payments.sale_id', 'left');

    if ($start_date && $end_date) {
        $where[] = "payments.date BETWEEN " . $query->escape($start_date) . " AND " . $query->escape($end_date);
    }
    if ($categoria) {
        $where[] = "sale_items.category_id = $categoria";
    }

    if (!empty($where)) {
        $whereRaw = implode(' AND ', $where);
        $query->where($whereRaw);
    }

    return $query->get()->result();    
}