filter dynamic tables structure

Question

I have the following tables structure :

this table for main categories

for each category we can add values

for each category we can add filters for search

for each filter we can add values

finally this table is used for saving data to each category value this

I want to create search query to find results based on user chosen filter value

for example find a university that has sector : public and degree : bachelor?

the search will be done on the last table

for example find a university that has sector : public and degree : bachelor?

the query should handle filter_id = 1 and filter_id_value = 1 also filter_id =2 and filter_id_value =3

the final result should be university of jordan

NOTE : I program using php and mysql

Answer 1

please be more concrete about your issue. What is your current state? Did you do some approaches by yourself? We can't create the complete filter query for you. Please take a look at database relations, especially JOIN statements.

But i want to give you an approach. First, you have to determine in which kind your tables are related (1->1, 1->n, n->n ...). In your query, you have to connect your tables via JOINS and their ON statements (maybe you also could use USING statements, but this rely on your db concept). You can check in the WHERE condition if the user input match your records.

I hope you can continue your work with my help!