I have a newsletter component and users can subscribe to multiple newsletters. It is stored in the table of the subscribers like this: 4,8,11 (these are the id's of the newsletters the subscriber is subscribed to)
To select the subscribers of a newsletter I use:
"SELECT * FROM #__newsl_subscribers WHERE newslids LIKE '%" . (int) $id."%'";
When I want to select all users that rec开发者_运维知识库eive newsletter with id 1, the user that receives newsletter 11 (or 12, 10 etc..) is also selected. And that's the problem.
Is there a select-statement for this? Otherwise I have to store the id's with brackets around them [1],[11], etc...
You really should avoid solutions where several ids are stored in one field. Instead you should use foreign keys and, like in your case for n:m relationships, relationship tables. n:m-relationships are these where a can have multiple b and vice versa.
I would do the following:
Table "subscriber": No information about newsletters
Table "newsletter": No information about subscribers
New table "newsletter_subscriber":
Field: subscriber_id
Field: newsletter_id
Both fields are foreign keys (references to the primary key of another table).
To select all subscribers of a specific newsletter, you can use this statement:
SELECT s.* FROM subscriber s
INNER JOIN newsletter_subscriber ns ON ns.subscriber_id = s.id
WHERE ns.newsletter_id = <newsletter id>
And vice versa, to select all newsletters of a specific subscriber:
SELECT n.* FROM newsletter n
INNER JOIN newsletter_subscriber ns ON ns.newsletter_id = n.id
WHERE ns.subscriber_id = <subscriber id>
BTW I should appologize for not answering your original question. Maybe you know everything about database normalization, having other reasons for these ids stored in one field?
精彩评论