DB Active Record Class: Combining of where and like statements

[eluser]mb2007[/eluser]
Hello,

I would like to combine where and like statements with the active record class. So I tried:

Code:

$this->db->where('status', 'angenommen');
$this->db->where('event_id', $event_id);
        
$this->db->like('firstname', $search[0]);
$this->db->orlike('lastname', $search[0]);
        
$this->db->orwhere('crew', '1');
$this->db->where('event_id', $event_id);
        
$this->db->like('firstname', $search[0]);
$this->db->orlike('lastname', $search[0]);

And I want to produce:

Code:

WHERE STATUS = 'angenommen'
AND event_id = '1'
AND firstname LIKE '&#xse;archstring%'
OR lastname LIKE '&#xse;archstring%'
OR crew = '1'
AND event_id = '1'
AND firstname LIKE '&#xse;archstring%'
OR lastname LIKE '&#xse;archstring%'

But the active record class produces:

Code:

WHERE status = 'angenommen'
AND event_id = '1' OR crew = '1' AND event_id = '1'
AND firstname LIKE '&#xse;archstring%' OR lastname
LIKE '&#xse;archstring%' AND firstname LIKE '&#xse;archstring%'
OR lastname LIKE '&#xse;archstring%'

Is there a way to influence the sequence of the statements?

Matthias.

[eluser]B3ll4triX[/eluser]
http://ellislab.com/forums/viewthread/45307/ :lol:

[eluser]kgill[/eluser]
Way to go! Where were you two years ago when he needed your help... Heh look at the date of his post.

[eluser]B3ll4triX[/eluser]
ya... I do not see a date... :cheese:

[eluser]Unknown[/eluser]
lol

[eluser]Rai Ehtisham[/eluser]
i have the same problem please help

[eluser]leela[/eluser]
If you want to write complex query. Its better if you write the whole query.. Codeigniter active database supports actively for the smaller queries with simpler conditions. it doesn't supports for the complex query and the multiple nested conditions in the query. I would prefer to write the query rather than using the codeigniter helper

[eluser]Rai Ehtisham[/eluser]
Thanks for the reply.
I am using pagination to display results from a complex query which contains one WHERE and 6 OR LIKE conditions. The query works fine if written manually but when written with CI functions like $this->db->where();
or $this->db->like/or_like
it displays results from all rows instead of fetching only the where clause records.
I am doing a huge project and i am stuck at this point.
When i write a custom query and pass the pagination $num and $offset it gives me an error on the first offset because the first page offset is always 0. It works fine with the CI db functions though.
Please give me an example code if anyone here have it.
Give me a search results paginated code. I need it on this weekend.
Help PLZ

[eluser]CroNiX[/eluser]
Don't waste time trying to get complex queries to work in active record. Just use db::query(); I believe there are improvements coming to CI3 that will let you do subqueries and complex like statements, but that doesn't help you here right now.

Code:

$blah = $this->db->escape($this->input->post('blah'));  //manually escape all input
$sql = "SELECT `blah` FROM `blah` WHERE `another_blah` = '$blah' OR WHERE (`blah2` = 'something' AND `blah3` = 'something_else')");
return $this->db->query($sql)->result();

[eluser]Rai Ehtisham[/eluser]
Thanks for the help but i need a search result pagination example.
A good one and a standard one which includes WHERE and LIKE both
Please put in a good one