Sign in to follow this  

[web] Using comma seperated string in MySQL database field

This topic is 3315 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I'm trying to think of the best way to do this. Right now what I have works but I don't think it's the best way to do it. Here's the best example I could come up with that is similar to my situation. A user logs in to a website to purchase items. The user adds stuff to their shopping cart. As the individual decides to purchase an item, it's added to their shopping cart. There are 1000's of items the individual could purchase and they could purchase 1 item or 100 items. So what I do is I currently store the items in a comma separated string so I would have something similar to user1 has a shopping cart of "apples,banana's,oranges" user2 has a shopping cart of "banana's,oranges" Now searching isn't too bad as I can always search every string for the word "apple" or "banana" using LIKE. However, it seems to me like there should be a better way to store and search the data. For example, if there were additional properties that I'd like to pull, then I would like to store the key within the comma separated string. That way I could get the string and then for each key, pull out additional information from an additional table. But that would still require me to do two separate looks, the first to get the string then I would have to break the string into parts and do a second search for each item within the string into the appropriate table. I looked into using a SET but that limits what I have to only 64 items and that also requires a SET to be built while the table is created which I can't say will always happen. Curious if anyone has any idea's of what I may be able to do to help out my situation or if what I have is what will have to do...

Share this post


Link to post
Share on other sites
Why are you storing that into your database?

Most shopping websites or applications use a session object for that. It's a lot faster because it doesn't require a database lookup and you can easily store things in an array.

On a side note: it might also be easier to store such data serialized into your database and convert it back to an array. You won't need to manually modify your string and search for the "," sign. It has a lot of advantages. For example, using PHP the following would occur:
$items = array();
$items[] = "apple";
$items[] = "banana";

$data = serialize($items);
// Now $data will be a string as follows:
// a:2:{s:5:"apple";s:6:"banana";}
Using the unserialize() function you can convert the string back to an array.

Share this post


Link to post
Share on other sites
Databases are usually "normalized" such that you don't have a comma separated string. What you should have is a table that stores the purchased items related to the customer. This bridge table would have the user_id, the item_id and the quantity.

What you have done is to create what is essentially a flat file. What happens if a user purchases more items than can fit in your CSV string? Think about googling fourth normal form, or database design, before you dig yourself any deeper in this hole.

Share this post


Link to post
Share on other sites


I had a whole description for which i lost somewhere and now i have to head out to an engagement party, but here is a relationship diagram for what you could do to the database to allow the data to be stored seperatly. PM me for any questions.

Share this post


Link to post
Share on other sites
Quote:
Original post by Sparks


I had a whole description for which i lost somewhere and now i have to head out to an engagement party, but here is a relationship diagram for what you could do to the database to allow the data to be stored seperatly. PM me for any questions.


After reading a bit more on the web I understand and this is what I'm looking to do now. Thanks for the help.

Share this post


Link to post
Share on other sites

This topic is 3315 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this