Welcome Guest, Not a member yet? Register   Sign In
A confirm about a SQL query
#1

Hi there.
I have a sql query perfectly working, however I'd like to know if it could be write in a better way or in a more efficient way.

I have this scenario: in a database I have 4 tables, brands, categories, products and another table called categories_to_product which is a lookup table composed by two field, product_id and category_id, which let have to products, more than one category.

brands
-------------
id
brand

categories
-------------
id
category

products
-------------
id
brand_id (fk of brands)

categories_to_product
-------------
product_id
category_id

In the products visualization, I have three fields for an advanced search (pic_01)
The problem I've tryied to solve for some days, it was the category field. At final I solved with this code:

Code:
if(isset($_POST['for_product'])):
$for_product = $_POST['for_product'];
else:
$for_product = '';
endif;

if(isset($_POST['for_brand'])):
$for_brand = $_POST['for_brand'];
else:
$for_brand = '';
endif;

if(isset($_POST['for_category'])):
$for_category = $_POST['for_category'];
else:
$for_category = '';
endif;

?>

<form action="" method="POST">
<label>Search for product
<input type="text" name="for_product" value="">
</label>
<label>Search for brand
<input type="text" name="for_brand" value="">
</label>
<label>Search for category
<input type="text" name="for_category" value="">
</label>
<input type="submit" value="Search">
</form>

$sql = 'SELECT products.product,
  brands.brand
FROM products
JOIN brands
ON brands.id = products.brand_id
AND product LIKE "%'.$for_product.'%"
AND brand LIKE "%'.$for_brand.'%"';

if( ! empty($for_category)):
$sql .= ' JOIN categories_to_product
ON categories_to_product.product_id = products.id
JOIN categories
ON categories_to_product.category_id = categories.id
WHERE category IN (SELECT category
  FROM categories
  WHERE category
  LIKE "%'.$for_category.'%")';
endif;
The sql query is working, but I am not so good in sql and in subquery... So, I have the doubt that my code could be better or more efficient... Also I'd like to transform it for query builder...

Someone can be give me an opinion about this sql code?

Thank you and sorry for my prolixity...

G

Attached Files Thumbnail(s)
   
Reply
#2

The Query Builder options in CI offer a good method to create sub-queries:
PHP Code:
if ($for_category) {
 
 $this->db
  
->select('category')
 
 ->from('categories')
 
 ->like('category',$for_category);
 
 $subquery $this->db->get_compiled_select();
}

$this->db->select('p.product,b.brand');
$this->db->from('products p');
$this->db->join('brands b','b.id = p.brand_id');
if (
$for_category) {
 
  $this->db->join('categories_to_product ctp','ctp.category_id = p.id');
 
  $this->db->join('categories c','ctp.category_id = c.id');
 
  $this->db->where("ctp.category IN ($subquery)",NULL,FALSE);
 
  //not tested: $this->db->where_in('ctp.category',$subquery);
}
$this->db->like('p.product',$for_product);
$this->db->like('b.brand',$for_brand);
$query $this->db->get();
if (
$query->num_rows() > 0) {
 
 $records $query->result_array();

Reply
#3

Thank you a lot for your reply! I will test it right now!
G
Reply




Theme © iAndrew 2016 - Forum software by © MyBB