MySQL, arrays, joins

[Pig]

In my database, I have 2 tables of interest, users and content.

"Users" has a field called "subscriptions" which holds a list if id's that correspond to articles in "content". That is: users.subscriptions is a list that you would find in content.id

The items in users.subscriptions are seperated by : for easy implode/explode operations.

Here's my question:

I want to pull all of the information out of "content", where the list of id's in users.subscriptions matches content.id. I figured out how to do this by exploding the list, and doing a foreach for the array, and sending a query for each part of the array.

However, I would like to be able to do this without sending 20-50 queries everytime the page loads. Is there a way to create a query, and match the array data vs. the content.id's in just one query?

Thanks for your help.

[tha_mink]

Bad database design. When you need to index stuff like you do, a delimited array is poor design. A better idea would be to create a table for subscriptions with coresponding fields in content and users. So if a user has 6 subscriptions, he has 6 entries in the subscriptions table that hold one each. The table may look something like this....

CREATE TABLE subscriptions (
id int(9) not null auto_increment PRIMARY KEY,
user_id int(9),
content_id int(9)
);

You'll find this approach much easier to work with. Some join action and you're all set.

Of course you could migrate to a DB that allows for storage of arrays...but....

[Pig]

I see what you are saying. That is much better. The original design called for only a couple of entries, but when it got changed to unlimited, I bastardized the structure to that array method.

It didn't cross my mind to just seperate them out.

Thanks for the help!