Solved

How to store multiple values of a single field in a mysql db

Posted on 2006-06-25
3
150 Views
Last Modified: 2010-04-07
I am using php/mysql.

I am trying to figure out what is the proper way organize multiple values for one field. On my website people play mixes and can choose any mix as one of there favorites.

I can store and view one favorite without issue but I dont know how to deal with multiple "favorites".

Should there be just one field called favorites which has multiple records in it? If this is the case how do I seperate the different "favorites" from one another.

Or should I have several fields such as favorite1,favorite2,favorite3 and have the dynamic table just pull these records.

Or am I completely off base and looking at it in the wrong way? I also have to do  this with the friends that users choose. Im really lost on how to set this up. Im pretty good with basic mysql/php but I have never had to store multiple values in this manner before.

Thank you in advance for any help, I really appreciate this site alot!

M




0
Comment
Question by:hep516
  • 2
3 Comments
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 500 total points
ID: 16980727
Hi hep516,

Your first two options are not good.  Even if you wanted to store multiple values in one column on one row, it's just not good design.

Similarly, you do not want to store fav1, fav2, fav3, etc on the same row of the table because you then have to write code that handles a guy who only has one favorite versus a guy that has X favorites.  Again, not good design.

The best way to do is to allow users to enter their favorites as new rows in the table.  So you will have more than one table in the overall design...you will have at least two: users and favorites.  In all probability, you will have a third table that holds the different mixes, unless I misunderstand your design.  In users, there needs to be a unique primary key that identifies the user.  In mixes, you need a unique key that identifies the mix and a fields that holds the userID from users.  In favorites, you need a unique key for that, plus fields for userID and mixID.

Now, when a user selects a mix as a favorite, your site Inserts that into the favorites table.  To pull out a list of favorites per user, all you need to do write a select query that limits the favorites table by the current userID and returns the results in a repeat region.  So now your users can have unlmited favorites.
0
 

Author Comment

by:hep516
ID: 16980891
Thank you very much for your help.


0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 16980910
No problem.  This is all easily accomplished with the built-in behaviors, so if you get stuck, post back and I'll give you some more pointers.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Insert multiple row form with checkboxes 9 1,020
website template 12 324
Summary to Detail Page using a Button 3 387
Adobe Indesign CS6 4 99
For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now