Tolong pilih kategori sesuai, jenis posting (pertanyaan atau bukan) dan sertakan tag/topik yang sesuai misal komputer, php, mysql, dll.
Promosi atau posting tidak pada tempatnya akan kami hapus.
Mencantumkan kode program di posting Anda, tolong ikuti aturan yang sesuai, baca http://diskusiweb.com/discussion/39204/aturan-cara-menyisipkan-kode-program-di-diskusiweb

Baca cara posting gambar/image di post Anda: http://www.diskusiweb.com/discussion/47345/cara-menyisipkan-menyertakan-image-pada-posting/p1

Menanggulangi masalah Active Record WHERE dan OR WHERE pada Codeigniter

edited September 2011 in Artikel & Tutorial Komputer
Dalam Framework Codeigniter Reactor versi 2.0.2 (versi terbaru) saya sering mendapatkan masalah pada Active Record <b>WHERE</b>, <b>OR_WHERE</b>, <b>LIKE</b> dan <b>OR_LIKE</b>.

Seperti kasus dibawah ini yang menginginkan sort database dengan menggunakan class Active Record WHERE dan OR_WHERE.

Contoh pada model :<div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php"><span class="kw2">function</span> item<span class="br0">(</span><span class="re0">$cat_type</span><span class="sy0">,</span> <span class="re0">$cat_id</span><span class="br0">)</span><span class="br0">{</span>
<span class="co1">// Type</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st_h">'type'</span><span class="sy0">,</span> <span class="re0">$cat_type</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ID</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st_h">'cat1'</span><span class="sy0">,</span> <span class="re0">$cat_id</span><span class="br0">)</span><span class="sy0">;</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">or_where</span><span class="br0">(</span><span class="st_h">'cat2'</span><span class="sy0">,</span> <span class="re0">$cat_id</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ORDER</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">order_by</span><span class="br0">(</span><span class="st_h">'id_item'</span><span class="sy0">,</span> <span class="st_h">'desc'</span><span class="br0">)</span><span class="sy0">;</span>
 
<span class="re0">$query</span> <span class="sy0">=</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">get</span><span class="br0">(</span><span class="st_h">'item'</span><span class="br0">)</span><span class="sy0">;</span>
<span class="kw1">return</span> <span class="re0">$query</span><span class="sy0">-></span><span class="me1">result</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="br0">}</span></pre></div></td></tr></table></div>
Contoh diatas adalah fungsi yang menginginkan query seperti :<div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php">SELECT <span class="sy0">*</span>
FROM item
WHERE type <span class="sy0">=</span> <span class="st_h">'$cat_type'</span>
AND <span class="br0">(</span>cat1 <span class="sy0">=</span> <span class="st_h">'$cat_id'</span> OR cat2 <span class="sy0">=</span> <span class="st_h">'$cat_id'</span><span class="br0">)</span></pre></div></td></tr></table></div>
Akan tetapi Active Record tersebut tidak memberikan hasil seperti contoh query SQL yang dimaksud.
Codeigniter membaca fungsi tersebut seperti :<div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php">SELECT <span class="sy0">*</span>
FROM item
WHERE type <span class="sy0">=</span> <span class="st_h">'$cat_type'</span>
AND cat1 <span class="sy0">=</span> <span class="st_h">'$cat_id'</span>
AND cat2 <span class="sy0">=</span> <span class="st_h">'$cat_id'</span></pre></div></td></tr></table></div>

Sama halnya seperti ketika menggunakan LIKE dan OR_LIKE. Seperti contoh kasus dibawah ini<div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php"><span class="kw2">function</span> item<span class="br0">(</span><span class="re0">$search_type</span><span class="sy0">,</span> <span class="re0">$search_key</span><span class="br0">)</span><span class="br0">{</span>
<span class="co1">// Type</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st_h">'type'</span><span class="sy0">,</span> <span class="re0">$search_type</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ID</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">like</span><span class="br0">(</span><span class="st_h">'title'</span><span class="sy0">,</span> <span class="re0">$search_key</span><span class="br0">)</span><span class="sy0">;</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">or_like</span><span class="br0">(</span><span class="st_h">'content'</span><span class="sy0">,</span> <span class="re0">$search_key</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ORDER</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">order_by</span><span class="br0">(</span><span class="st_h">'id_item'</span><span class="sy0">,</span> <span class="st_h">'desc'</span><span class="br0">)</span><span class="sy0">;</span>
 
<span class="re0">$query</span> <span class="sy0">=</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">get</span><span class="br0">(</span><span class="st_h">'item'</span><span class="br0">)</span><span class="sy0">;</span>
<span class="kw1">return</span> <span class="re0">$query</span><span class="sy0">-></span><span class="me1">result</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="br0">}</span></pre></div></td></tr></table></div>
Contoh diatas adalah fungsi yang menginginkan query seperti :<div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php">SELECT <span class="sy0">*</span>
FROM item
WHERE type <span class="sy0">=</span> <span class="st_h">'$search_type'</span>
AND <span class="br0">(</span>title LIKE <span class="st_h">'%$cat_id%'</span> OR content LIKE <span class="st_h">'%$cat_id%'</span><span class="br0">)</span></pre></div></td></tr></table></div>
Akan tetapi Active Record tersebut tidak memberikan hasil seperti contoh query SQL yang dimaksud.
Codeigniter membaca fungsi tersebut seperti :<div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php">SELECT <span class="sy0">*</span>
FROM item
WHERE type <span class="sy0">=</span> <span class="st_h">'$search_type'</span>
AND title LIKE <span class="st_h">'%$cat_id%'</span>
AND content LIKE <span class="st_h">'%$cat_id%'</span></pre></div></td></tr></table></div>

<b>SOLUSI</b>
Belum jelas kenapa masalah tersebut ada pada Active Record.
Active Record tidak mengenali WHERE dan OR_WHERE ketika ada 1 atau lebih statement WHERE diatasnya sebelum WHERE dan OR_WHERE tersebut digunakan. Begitupun halnya seperti LIKE dan OR_LIKE ketika statement WHERE ditempatkan diatasnya.

Tetapi masalah tersebut dapat diatasi dengan menggunakan fungsi seperti ini :
<b>Jika menggunakan WHERE dan OR_WHERE, maka gunakan fungsi OR_WHERE seperti berikut :</b><div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php"><span class="kw2">function</span> item<span class="br0">(</span><span class="re0">$cat_type</span><span class="sy0">,</span> <span class="re0">$cat_id</span><span class="br0">)</span><span class="br0">{</span>
<span class="co1">// Type</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st_h">'type'</span><span class="sy0">,</span> <span class="re0">$cat_type</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ID</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st0">"(cat1 = '"</span><span class="sy0">.</span><span class="re0">$cat_id</span><span class="sy0">.</span><span class="st0">"' OR cat2 = '"</span><span class="sy0">.</span><span class="re0">$cat_id</span><span class="sy0">.</span><span class="st0">"')"</span><span class="sy0">,</span> <span class="kw4">NULL</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ORDER</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">order_by</span><span class="br0">(</span><span class="st_h">'id_item'</span><span class="sy0">,</span> <span class="st_h">'desc'</span><span class="br0">)</span><span class="sy0">;</span>
 
<span class="re0">$query</span> <span class="sy0">=</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">get</span><span class="br0">(</span><span class="st_h">'item'</span><span class="br0">)</span><span class="sy0">;</span>
<span class="kw1">return</span> <span class="re0">$query</span><span class="sy0">-></span><span class="me1">result</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="br0">}</span></pre></div></td></tr></table></div>

<b>Jika menggunakan LIKE dan OR_LIKE, maka gunakan fungsi OR_LIKE seperti berikut :</b><div class='geshi_syntax'><table><tr><td><div class='code'><pre class="php"><span class="kw2">function</span> item<span class="br0">(</span><span class="re0">$search_type</span><span class="sy0">,</span> <span class="re0">$search_key</span><span class="br0">)</span><span class="br0">{</span>
<span class="co1">// Type</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st_h">'type'</span><span class="sy0">,</span> <span class="re0">$search_type</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ID</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">where</span><span class="br0">(</span><span class="st0">"(title LIKE \'%'"</span><span class="sy0">.</span><span class="re0">$search_key</span><span class="sy0">.</span><span class="st0">"'%\' OR content LIKE \'%'"</span><span class="sy0">.</span><span class="re0">$search_key</span><span class="sy0">.</span><span class="st0">"'%')"</span><span class="sy0">,</span> <span class="kw4">NULL</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">// ORDER</span>
<span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">order_by</span><span class="br0">(</span><span class="st_h">'id_item'</span><span class="sy0">,</span> <span class="st_h">'desc'</span><span class="br0">)</span><span class="sy0">;</span>
 
<span class="re0">$query</span> <span class="sy0">=</span> <span class="re0">$this</span><span class="sy0">-></span><span class="me1">db</span><span class="sy0">-></span><span class="me1">get</span><span class="br0">(</span><span class="st_h">'item'</span><span class="br0">)</span><span class="sy0">;</span>
<span class="kw1">return</span> <span class="re0">$query</span><span class="sy0">-></span><span class="me1">result</span><span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="br0">}</span></pre></div></td></tr></table></div>

Semoga jika ada yang mempunyai masalah seperti ini dapat terbantu dan jika dalam waktu dekat ini saya dapat melakukan perubahan terhadap system Active Record aslinya (agar tidak memerlukan mengunakan fungsi diatas) saya akan post lagi disini.

Tanggapan

  • jadi aneh ya bos :D

    malah kita nulis manual sendiri hehe
  • Iya hehe.. Mungkin di CI 2.1.0 yang terbaru ini bisa..
    Saya belum pernah coba soalnya sudah keseringan pake MY_Model sendiri hehe..
  • Lebih baik pakai query biasa sajalah. Where, join, order by dan limit ditulis manual saja. Tambah bingung nanti :D
  • kalau ad query kayak gini converting ke query biasanya gimana sih?
    $this->db->like('fullname',$key);
    $this->db->or_like('username',$key);
    if($num)
    {
       return $this->db->count_all_results();
    }
    $this->db->order_by('user.level_id','asc');
    $this->db->limit($perpage,$offset);
    return $this->db->get();
  • Apa enaknya pake CI, kyknya lebih enjoy tulis SQL langsung...

  • ya kalo sudah pake framework CI utk yg lainnya, masa utk DB mesti tulis manual ?
  • @muslih coba gunakan metode
    $this->db->last_query() untuk menampilkan query terakhir,
    Atau hidupkan profiler.
    $this->output->enable_profiler()

    Nanti bakal keliatan semua dah querynya
  • aku ngikutin ajalah. rumangsa wong bodo aku melu maca bae. hehe... :-)
  • edited July 2012
    @muslih
    SELECT *
    FROM TABLE
    WHERE fullname LIKE 's%' OR username LIKE '% s%'
Sign In or Register to comment.