$this->db->like() on multiple tables

[eluser]gerben[/eluser]
I'm trying to create a search-function that searches through 3 tables at once. I'm not sure I'm approaching this in the right way:

Code:

$this->db->like('name', $search); // users table
$this->db->like('company', $search); // companies table
$this->db->like('company_adress', $search); // companies table
$this->db->like('interests', $search); // interests table        
$this->db->from('users');
$this->db->join('companies', 'companies.user_id = users.id');
$this->db->join('interests', 'interests.user_id = users.id');
$q=$this->db->get();

One user can have multiple companies and multiple interests.

[eluser]sszynrae[/eluser]
With chained ->like() statements, you're intersecting your matches. Try ->or_like() on the 3 last like statements.

Also, unless im mistaken, that join will only give you at most one company and interest if it even works at all. Try ->from('companies c, interests i, user u') and
->where('c.user_id = u.id AND i.user_id = u.id') instead of the two joins

[eluser]gerben[/eluser]
Thanx for the reply! I changed the code so it looks like:

Code:

$this->db->like('name', $search); // users table
$this->db->or_like('company', $search); // companies table
$this->db->or_like('company_adress', $search); // companies table
$this->db->or_like('interests', $search); // interests table        
$this->db->('companies c, interests i, users u');
$this->db->where('c.user_id = u.id AND i.user_id = u.id');
$this->db->group_by(u.id'); // added a group_by
$q=$this->db->get();

The code works great when a match is found in the users table, but when a match is found in any of the other two tables, a list containing ALL users is returned. I don't really understand why it behaves this way.

[eluser]David Johansson[/eluser]
I would try something like this:

Code:

$this->db->like('name', $search); // users table
$this->db->or_like('company', $search); // companies table
$this->db->or_like('company_adress', $search); // companies table
$this->db->or_like('interests', $search); // interests table        
$this->db->from('users u');
$this->db->join('companies c', 'c.user_id = u.id', 'left');
$this->db->join('interests i', 'i.user_id = u.id', 'left');
$this->db->group_by('u.id'); // added a group_by
$q=$this->db->get();

the third parameter 'left' in the join argument makes sure that you can search for a user with no interests or companies.

[eluser]gerben[/eluser]
Wow David, this works like a charm! Thanks so much!

As I understand it now, the "left" statement makes sure the table "users" is the main table in this query, no matter what's in the other tables.

[eluser]David Johansson[/eluser]
NP, you welcome!
[quote author="gerben" date="1248793849"]As I understand it now, the "left" statement makes sure the table "users" is the main table in this query, no matter what's in the other tables.[/quote]
That's a way of puttin' it