Storing an unknown number of variable

Getting array functionality out of a database

[zulu_dude]

The application I'm currently developing requires a list of items in which the user is interested to be stored in a database. It's similar to a shopping basket, in that users can add and remove items from the list.

What is the best way of storing this in a database?

There are too many items to create a new column in the 'user' table for each item. Items are added and removed from the site on a regular basis as well, so maintaining the columns would become a nightmare.

It would be fairly easy to do it with an array in PHP, but that doesn't really solve my problem, as I want to store it in the database, not in a cookie or session variable.

Can anyone help me out? How do the shopping carts do it? Any help is much appreciated!

[zulu_dude]

Haha, literally 5 minutes after posting this topic, I've found out about the serialize() and unserialize() functions in PHP!

That looks like exactly what I'm looking for...

[arran]

Hi zulu_dude,

This can be done with one extra table. Assuming we already have a

user

table and

product

table:

interested(userID, productID)

For example, if user 1 is interested in products 3,6, 7 and user 2 is interested in products 3,4,5, the table would look like:

userID productID
------ ---------
1 3
1 6
1 7
2 3
2 4
2 5

arran.

<added>Please don't use serialize() and unserialize() - let the database do as much work as possible!</added>

[zulu_dude]

Hi Arran,

Thanks for the tip... I hadn't thought of doing it that way. I'll have to mull it over, as that method may be simpler than using serialize. Although if there are hundreds of users adding favourites on a regular basis, that table could become rather large rather quickly!

But then again, I suppose I could set a cronjob to clear out the users and favourites tables if the user hasn't logged on for, say, 6 months. That would prevent antiquated entries from clogging up the table.

[arran]

...that table could become rather large rather quickly!

Shouldn't be a problem. In terms of space, even if you have 1 millions rows, this will only take up 8Mb (assuming userID and productID are both 32 bit fields). In terms of access speed, as long as you create an index on the itemID column you will be fine.

arran.