CODEWITHSUNDEEP
sqlcodeigniterwhere-clause
zazvorniki
zazvorniki

Reputation: 3602

codeigniter like clause overriding where clause

At least that is what seems to be happening. I'm trying to create a search bar for a website and it does work, except it's not reading a where clause which would only pull back approved content. You can kind of see why that would be an issue.

Anyway, this is what I have in my model

$match = $this->input->post('search');      
$this->db->where('approved', 'y');  
$this->db->like('description', $match);
$this->db->or_like('title', $match);
$this->db->or_like('body', $match);
$this->db->or_like('author', $match);

$query = $this->db->get('story_tbl');

return $query->result();

and when I print out the query, it seems like it's seeing the where clause, but when I get the stuff back it's pulling things that are not approved or under review.

Here is my printed query

SELECT * FROM (`story_tbl`) WHERE `approved` = 'y' AND `description` LIKE 
'%another%' OR `title` LIKE '%another%' OR `body` LIKE '%another%' OR 
`author` LIKE '%another%'

Upvotes: 5

Views: 7498

Answers (2)

coderSree
coderSree

Reputation: 329

You can use codeigniters's group_start() and group_end() for this. The code can be modified like this

$match = $this->input->post('search');      
$this->db->where('approved', 'y');

$this->db->group_start(); //start group
$this->db->like('description', $match);
$this->db->or_like('title', $match);
$this->db->or_like('body', $match);
$this->db->or_like('author', $match);
$this->db->group_end(); //close group

$query = $this->db->get('story_tbl');

return $query->result();

Upvotes: 1

sakibmoon
sakibmoon

Reputation: 2032

Your query should be

SELECT * FROM (`story_tbl`) WHERE `approved` = 'y' AND (`description` LIKE
'%another%' OR `title` LIKE '%another%' OR `body` LIKE '%another%' OR
`author` LIKE '%another%')

Check those brackets. So, your best option is to use plain $this->db->query(). If you insist on using active records, you have to do it like this for those brackets -

$match = $this->input->post('search');
$this->db->where('approved', 'y');
$this->db->where("(`description` LIKE '%$match%'");
$this->db->or_where("`title` LIKE '%$match%'");
$this->db->or_where("`body` LIKE '%$match%'");
$this->db->or_where("`author` LIKE '%$match%')");
$query = $this->db->get('story_tbl');

EDIT:

true AND true OR true OR true    //true
false AND true OR true OR true   //true just like your where clause is ignored
false AND false OR false OR true //Still true
false AND true OR false OR false //false
false AND false OR false OR false //false

So this query will return all the rows where approved = 'y' OR where title, body, author matches 'another'

In my posted query

true AND (true OR true OR true)    //true
false AND (true OR true OR true)   //false, where clause is checked
true AND (false OR false OR false) //false
true AND (true OR false OR false)  //true

Which will return the rows where approved = 'y' AND also either title or body or author or description matches 'another'. I believe this is what you want to achieve.

Upvotes: 3

Related Questions