php - What is the best way to match a field with multiple IDs stored in mySQL -

i have table column called "owners", contains ids users connected particular record.

i delimit data ",". example

id | name | owners 1 | bob | 1,4,5 

when doing select on intending use following sql:

select * table owner='$profile' or owner '%,$profile%' or owner '%$profile,%' 

but realise flawed (searching 5 match 5, 15, 25, 50).

what correct way this?

@amarnasan correct: don't store multiple values in single field separated commas!

in bill karwin's sql antipatterns book called jaywalking antipattern.

the correct way create intersection table joins owners first table. have multiple rows in intersection table representing multiple owners each record:

record_id | owners_id 1 | 1 1 | 4 1 | 5 

your query like:

select * table join intersection_table intersection_table.record_id = table.id , intersection_table.owners_id = '$profile'