Need help in ActiveRecord queries...

[eluser]hugle[/eluser]
Hello everyone

I'm trying to do the search over two mysql tables:
table1 > columnt1 and column2

and table2 > columnt1 and column2

Let's take a searchword - "codeigniter"

so, when I search the word "Codeigniter",
I want the search firstly do smth like that:

Code:

$keyword = trim("CodeIgniter");

$this->db->select('id');
$this->db->like('table1.columnt1 AND table1.columnt2 LIKE %$keyword%);
$this->db->or_like('table2.columnt1 AND table2.columnt2 LIKE %$keyword%);

$this->db->or_like('table1.columnt1 LIKE %$keyword% AND table1.columnt2 NOT LIKE %$keyword%);
$this->db->or_like('table2.columnt1 LIKE %$keyword% AND table2.columnt2 NOT LIKE %$keyword%);


$this->db->or_like('table1.columnt2 LIKE %$keyword% AND table1.columnt1 NOT LIKE %$keyword%);
$this->db->or_like('table2.columnt2 LIKE %$keyword% AND table2.columnt1 NOT LIKE %$keyword%);

$this->db->get('table1 and table2');

I wanted to demonstrate, what I want to achieve...
in method I described, I would have smth like 'priority'
so if the text matches in col1 and col2 = it's position 1
if only col1 = position2
and if only in col2 = position3

this all should be done agains 2 tables...
thanks you very much for any help
since I'm very poor at writng such difficult queries

thanks CI community !

[eluser]kgill[/eluser]
Your example query isn't very helpful, you talk about position 1, position 2, but only select id. What columns are you looking to see in the result set, just id? Secondly how are the two tables related, is there a foreign key relationship between them or are they just two separate tables with similar info. That detail makes a huge difference in how the query is written.