Hello everyone. This is my first post and is already urging for help...

I'm trying to build a filters system for an online shopping cart. For example, let's say i'm in "Shirts" category. The available filters are: "Country", "Color" and "Team". Each filter has multiple values, for example the "Country" filter has "England", "Belgium", "Austria" values, "Color" filter has "Red","Green","Blue" values and "Team" filter has "Local" and "International" values

More graphical:

[Country]
|____ England
|____ Belgium
|____ Austria

[Color]
|____ Red
|____ Green
|____ Blue

[Team]
|____ Local
|____ International

When i check the "Local" value i'm getting all products that are local.
When i check and the "Green" value, i'm getting all products that are local and green.
When i check and the "England" i'm getting all products that are local, green and ONLY england (the selecting order is random... the country can be selected first and after that the colour and local).

I tried in two ways, both failures.

Option 1.
I have two tables:

t.products and t.filters

[t_products]
idProduct
some_other_field
some_other_field
some_other_field

[t_products] data:
1 | some_data | some_data | some_data

[t_filters_values]
idValue
idProduct
value -- which is type varchar

[t_filters_values] data:

1 | 1 | Red
2 | 1 | Blue
3 | 1 | Local

The query i'm trying is:

select * from t_products 
inner join t_filters_values on (t_products.idProduct = t_filters_values.idProduct)
where (t_filters_values.value like '%Local%' and t_filters_values.value like '%Red%' and t_filters_values.value like '%Blue%')

and i expect to get one row. But it does not happening. Why ?

If i have only one value to match, it works. For example

select * from t_products 
inner join t_filters_values on (t_products.idProduct = t_filters_values.idProduct)
where (t_filters_values.value like '%Local%')

Option 2.

I have just one table:

[t_products]
idProduct
filters
some_other_field
some_other_field

[t_products] data:
1 | Red,Blue,Local,Austria | some_data | some_data

If i try

select * from t_products
where t_products.filters like '%Red%' and t_products.filters like '%Local%' and t_products.filters like '%Austria%'

it works. But if i try

select * from t_products
where t_products.filters like '%Red%' and t_products.filters like '%Blue%' and t_products.filters like '%Local%' and t_products.filters like '%Austria%'

it fails. Why ?

I thinking that maybe my logic is all wrong. In this case, how should i think the system ? What is the best way to make a filters system ? I'm opened for any tables structure.

The number of filters (and values) is variable and unknown at any given time. A product can have one filter or five or seven or 2 and so on... That's why i did not chose to add extra columns in the product table...

Thank you in advance and thank you for your time spent to read this.

You have to create Product table with each property in separate table columt:
product_id | color | country | team | other...

If product may be multicolor, then you need additioanl table for colors. Then product table will like:
product_id | color_id | country | team | other...
and color table
color_id | color name

Use such rule for other product properties.

To retrive necessary products you have to use JOIN statement to merge number of tables in one query.

In such case WHERE parte will be simple (color_name='red' and country='England' and team='Local')

Yes MapMaster, but what do you do when a product has 20 or more filters ? Let's say a notbook, for example...

Mapmaster is wrong on that one. You need to do the contrary.
Put each type of attribute in a separate table.

You did it right at the beginning of the post

[Country]
|____ England
|____ Belgium
|____ Austria

[Color]
|____ Red
|____ Green
|____ Blue

[Team]
|____ Local
|____ International

Then, you need a table that map each attributes to a product:

table productAttribs:
  productId
  attribId
  attribType

and put 1 line per attribute for each product in that table.

After that, your filter is a simple

select * 
from products p
  inner join productAttribs a
    on p.productId=a.productId
  left outer join country c
    on c.countryId=a.attribId
    and a.attribType='country'
  left outer join color co
    on co.colorId=a.attribId
    and a.attribType='color'
  left outer join team t
    on a.attribId=t.teamId
    and a.attribType='team'
-- Up this mark, is the "base" of the query. Under this mark is the filtering 
where p.productId={your product id}
and co.value={your color value}
and c.value={your country}
.....

You will have to include each attribute tables in the "outer joins" in the base query, and add the where clause elements as far as you have them.

You may need to fiddle a bit with this, but I think this is the base of your solution.

Thank you for your answer tripy, but in your way isn't that means that i have to create a separate table for each filter ? For a notebook, for example, i will have 20 or more tables... What happens when a new filter shows up ? I create an extra table for it ?

That's the idea, yes.

Or, but it's usually not recommended, put every attributes in one table, with a value and a group.
It's not recommended mostly for readability and ease of extention , but if you want to go that way, I'd make a table like this:

table attrib:
  attribId
  productId
  group varchar(50)
  intVal integer
  numVal float
  strVal varchar(100)
  bolVal boolean

and for each group, put the value in the field that correspond to your data type.
then the query shoudl be

select * 
from products p
  inner join attribs a
    on a.productId=p.productId
where p.productId={your product id}
and a.group='color' and a.strVal='green'
and a.group='size' and a.intVal=38