Using %like% and JOIN together in CodeIgniter

I have three tables: 'users', 'products', and 'product_types'. The 'users' table stores all usual data about my site users (including an id column); the 'products' table lists the product_id of the product, the user_id of the user who added it, and the product_type; the 'product_types' table has an id column (which corresponds with the product_type int value in the 'products' table), and a varchar name column.

I want to perform a %like% search in the 'users' table so that when a user searches for product types, it shows all users who have products that match the searched product type. Here's what I'm currently using in my CodeIgniter model:

$string = urldecode($string);
$this->db->select('users.*');
$this->db->from('users');
$this->db->like('users.company_name',$string,'both');
$this->db->or_like('users.address_1',$string,'both');
$this->db->or_like('users.city',$string,'both');
$this->db->or_like('users.contact',$string,'both');
$this->db->or_like('product_types.name',$string,'both');
$this->db->join('products','users.id = products.client_id');
$this->db->join('product_types','products.product_type = product_types.id');
$this->db->distinct();
$users = $this->db->get();
foreach($users->result() as $user){
   // search result
}

I then feed this into a foreach statement... But it's only showing the first row most of the time - other times it comes back with no re开发者_运维技巧sults. Can anybody tell me where I'm going wrong?

I dont understand why you are making multiple queries, it can be accomplished only in one query

    SELECT a.*,b.*,C.* FROM users a LEFT JOIN products b ON b.user_id = a.id LEFT JOIN product_types c ON b.product_type = c.id WHERE a.company_name LIKE '%$string%' OR a.address_1 LIKE '%$string%' OR a.city LIKE '%$string%' a.contact LIKE '%$string%' b.name LIKE '%$string%' 

this will give you all the products of the user with there product type, try to run it on your db to see it in action. then use foreach statement to fetch the results

What I do to debug queries is to first start with the top query and then look at the results. Then I append one statement to it and look at the result again.

So for your case, you can start with the simple case of:

$this->db->select('users.*');
$this->db->from('users');

Look at the result and then add another statement:

$this->db->select('users.*');
$this->db->from('users');
$this->db->like('users.company_name',$string,'both');

Do this until you eventually form the whole query.

The point is somewhere along the way you will discover what statement is filtering your other expected results. This can also be done backwards.