php - MySQL rows with multiple ids as conditions -

i have table meant assignments. these assignments have multiple requirements (things users need have in order allowed take assignment). have these requirements id's requirements table.

i want users assignments meet requirements for. have set of ids user has in php, , need query assignments ask ids that user has, or less.

i thought create json array requirements , put in column in assignments table. create json array requirements user has, , use in query.

but found out wouldn't work.

for example, have ["6","9","11"] requirements , 6 7 9 11 12 user has.

the requirements themself fit ones user has (using %"6"% etc), not other way around.

so question is, how can search in assignments table, somehow supplying ids user has, , rows meet ids user has, or less.

i make table requirements, instead of column, each requirement row, don't know how search in 1 query well.

you should create associative tables. can read more associative tables on website.

in case, you'll end following tables:

• assignments (assignment_id)
• requirements (requirement_id)
• assignment_requirements (assignment_id, requirement_id)
• users (user_id)
• user_met_requirements (user_id, requirement_id)

assignment_requirements , user_met_requirements associative tables afterwards can use join find assignments satisfy requirements:

select a.id `assignments` join `assignment_requirements` ar     on (a.`assignment_id` = ar.`assignment_id`) join `user_met_requirements` umr     on (umr.`requirement_id` = ar.`requirement_id`) umr.user_id = "$your_user_id" group a.id 

this give list of available assignments. pretty untested right answer might different, way go.