Can someone help me rewrite this query string to CodeIgniter active record

Question

Please help me:

SELECT * 
FROM  `product` 
WHERE  `vi_name` LIKE  '%product%'
OR  `en_name` LIKE  '%product%'
AND `parent_id` != 0

I wrote my own code but some how it still count rows have parent_id = 0

function get_product_by_search_slug($slug) {
        $this -> db -> from('product');
        $this -> db -> like('vi_name', $slug);
        $this -> db -> or_like('en_name', $slug);
        $this -> db -> where_not_in('parent_id', 0);
        $query = $this -> db -> get();
        return $query -> result();
}

I got a solution and It work fine but somehow I don't quite satisfy with it:

function get_product_by_search_slug($slug) {
        $this -> db -> select("* FROM `product` WHERE `vi_name` LIKE '%". $slug ."%' OR `en_name` LIKE '%". $slug ."%' AND `parent_id` != 0");
        $query = $this -> db -> get();
        return $query -> result();
}

Possible duplicate: http://stackoverflow.com/q/6156979/1618257 — David Starkey, Aug 21 '13 at 15:10
"AND `parent_id` != 0".. `!=` is incorrect for SQL. Use `<>` or `NOT IN` — David Starkey, Aug 21 '13 at 15:14
Once you used OR in your query you'll need braces, so it cannot be written purely in active records you'll need to so something like ->where('(vi_name like '%something%' OR en_name like '%something%)', NULL, FALSE)->where('parent_id !=', 0) — ahmad, Aug 21 '13 at 15:23
@ahmad I still don't get your mind completely. Can you write a demo from my case ? I will much more appreciate that. Thanks you. — user2657778, Aug 21 '13 at 15:35
Actually, Your final solution which works should not be working correctly, as you're still mixing ANDs & ORs where in fact you need braces, I will submit an answer now on this. — ahmad, Aug 23 '13 at 20:37

score 0 · Answer 1 · answered Aug 21 '13 at 15:17

0

you can try this

where('parent_id !=','0')

please let me know if you face any problem

answered Aug 21 '13 at 15:17

ABorty

2,512
1
13
16

score 0 · Answer 2 · answered Aug 21 '13 at 17:42

0

Try this one

$this->db->like('vi_name', $slug)
         ->or_like('en_name', $slug)
         ->where('parent_id <>', 0);

answered Aug 21 '13 at 17:42

DeiForm

701
9
31

score 0 · Answer 3 · answered Aug 23 '13 at 20:47

$slugQ = $this->db->escape_like_str($slug);
$this->db
    ->select('*')
    ->from('product')
    ->where("(vi_name like '%$slugQ%' OR en_name like '%$slugQ%')", NULL, FALSE)
    ->where('parent_id !=', 0)
    ->get()
    ->result();

In my example I have merly escaped the slug for proper mysql lookup, you'll have to do a few extra checks yourself.

Can someone help me rewrite this query string to CodeIgniter active record

3 Answers3