How to perform a LIKE across two fields in the same table?

grimblefritz

Let me say up front, I'm not at all familiar with datatables. This is my first exposure to it. I'm just trying to quickly resolve this one issue for someone else. That said...

There is a form that takes as inputs various fields - product, user, status, etc.

The following sets up the tables:

 $this->datatables
->select("{$this->db->dbprefix('sales')}.id as id, date, company_name, reference_no, CONCAT({$this->db->dbprefix('users')}.first_name, ' ', {$this->db->dbprefix('users')}.last_name) as user, customer_name, grand_total, paid, grand_total-paid as balance, due_date, status, {$this->db->dbprefix('sales')}.customer_id as cid, {$this->db->dbprefix('sale_items')}.product_name as product_name, details as details", FALSE)
->from('sales')
->join('sale_items', 'sale_items.sale_id=sales.id', 'left')
->group_by('sales.id');

After some additional formatting code, adding % brackets to the search terms, etc, there is the following:

if($product) { $this->datatables->like('sale_items.product_name', $product, 'both'); }
if($user) { $this->datatables->where('sales.user_id', $user); }
if($status) { $this->datatables->where('sales.status', $status); }
if($start_date) { $this->datatables->where('sales.date >=', $start_date); }
if($end_date) { $this->datatables->where('sales.date <=', $end_date.' 23:59:59'); }

echo $this->datatables->generate();

Most important to this question, the $product var (input field) is used to LIKE match against the product_name database field.

I've verified that I can change that to the details field and search in that instead of product_name.

Mostly that verifies the addition of the details field in the datatables->select.

I've also verified that simply adding a second line to add another ->like does not work.

I also tried building a CONCAT similar to user, for the product_name & detail fields, then using that in the like. Also failed.

My question: How do I get this to search in BOTH the product_name and details fields?

allan

$this->datatables->generate();

I'm not sure I recognise this method call. Can you link to the library you are using here please?

Thanks,
Allan