There are subscriptions to the filter.
Table subscriptions subscriptions: id, email
Table with possible filters subscriptions_filters: id, code, name
1, brand, Brand car
2. model, Model
3. power_from, Power from
Table subscriptions_filters_values: id, subscription_id, filter_id, value
1, 1, 1, BMW
2, 1, 2, M5
If I had to find all subscriptions which car brand BMW model M5 and a capacity of 155, the query would be simple.
SELECT * FROM subscriptions as sb
LEFT JOIN subscriptions_filters_values as sfv ON sfv.subscription_id = sb.id
LEFT JOIN subscriptions_filters as sf ON sf.id = sfv.filter_id
WHERE (sf.code = 'brand' and sfv.value = 'BMW') AND (sf.code = 'model' and sfv.value = 'M5') AND (sf.code = 'power' and sfv.value = '155')
What to do if I need to find all subscriptions BMW M5 155 HP, but those who have some of the parameters not set? For example subscribe to a filter which lists only BMW's without model and capacity.
And even though the above query won't work because is not correct. You have the options? Do not know how to solve, but to select all filters from the database and in PHP to compare...