Simba Simba - 12 days ago 7
MySQL Question

Adding brackets to OR_LIKE query on CodeIgniter

I have difficulty to adding brackets to my query. My query consists of

OR
,
AND
and
LIKE
. This is my MySQL query:

SELECT * FROM (`vendor`) JOIN `vendor_detail` ON `vendor`.`id_vendor`
= `vendor_detail`.`id_vendor` JOIN `klasifikasi` ON `vendor_detail`.`id_klasifikasi` = `klasifikasi`.`id_klasifikasi`
WHERE `vendor_detail`.`id_klasifikasi` = 'K0002' AND
`vendor`.`st_aktif` = 1 AND (`vendor`.`id_vendor` LIKE '%eka%' OR
`vendor`.`nama_vendor` LIKE '%eka%' OR `vendor`.`alamat` LIKE '%eka%'
OR `vendor`.`telp` LIKE '%eka%' OR `vendor`.`mobile` LIKE '%eka%' OR
`vendor`.`fax` LIKE '%eka%' OR `vendor`.`cp` LIKE '%eka%' OR
`vendor`.`telp_cp` LIKE '%eka%' OR `vendor`.`catatan` LIKE '%eka%' OR
`vendor`.`email` LIKE '%eka%')


As you can see I am adding brackets after
vendor.st_aktif = 1 AND
and in the last line of my query.

How can I implement above query to Code Igniter Active Records?

This is my current CI AR:

function src_vendor_by_name($src_nama,$src_kls){
$this->db->select('*');
$this->db->from('vendor');
$this->db->join('vendor_detail', 'vendor.id_vendor = vendor_detail.id_vendor');
$this->db->join('klasifikasi', 'vendor_detail.id_klasifikasi = klasifikasi.id_klasifikasi');
$this->db->where('vendor_detail.id_dklasifikasi', $src_kls);
$this->db->where('vendor.st_aktif', 1);

if($src_nama != "")
{
$this->db->or_like('vendor.id_vendor',$src_nama);
$this->db->or_like('vendor.nama_vendor',$src_nama);
$this->db->or_like('vendor.alamat',$src_nama);
$this->db->or_like('vendor.telp',$src_nama);
$this->db->or_like('vendor.mobile',$src_nama);
$this->db->or_like('vendor.fax',$src_nama);
$this->db->or_like('vendor.cp',$src_nama);
$this->db->or_like('vendor.telp_cp',$src_nama);
$this->db->or_like('vendor.catatan',$src_nama);
$this->db->or_like('vendor.email',$src_nama);
}

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

Answer

I don't think pure CodeIgniter will allow you to do a nested WHERE clause the way you want to do it. But one option is to use a custom string:

$where  = "(`vendor`.`id_vendor` LIKE '%eka%' OR ";
$where .= "`vendor`.`nama_vendor` LIKE '%eka%' OR";
$where .= "`vendor`.`alamat` LIKE '%eka%' OR ";
$where .= "`vendor`.`telp` LIKE '%eka%' OR ";
$where .= "`vendor`.`mobile` LIKE '%eka%' OR ";
$where .= "`vendor`.`fax` LIKE '%eka%' OR ";
$where .= "`vendor`.`cp` LIKE '%eka%' OR ";
$where .= "`vendor`.`telp_cp` LIKE '%eka%' OR ";
$where .= "`vendor`.`catatan` LIKE '%eka%' OR ";
$where .= "`vendor`.`email` LIKE '%eka%')";
$this->db->where($where);

There is something called IgnitedQuery which appears to supports nested WHERE statements.

Comments