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.
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:
$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?