Hi all,
The site I'm building is an online learning resource with 10 courses for members to take and complete
in any order they choose.
I have a table "myMembers" which holds member details like first and last name, email, p'word,
date joined and last log on date.
I need a way of recording which members have signed up to which courses and can't
quite get my head around how to approach it.
Do I make a new table called "courses" with ten fields listing each course and then enter the data
against each course - or - put a new field in the members table to list which courses they've
signed up to ? (preferred but I have no idea how to hold a list in a field in the db)
So I guess my question is about how to hold lists on the db - it's always the way, writing it out helps
to clarify the question I actually need to ask!
Could a field in the members table hold a list such as "1,5,9,6" to indicate that the member had signed
up to those courses in that order?
Or could the table "courses" hold a list of members (who are identified by number) who have signed up
to that course ?
i.e.
course1 = 22,99,108,4,66
course2 = 33,44,6,1,987
Any pointers / links / lessons on this very gratefully received !
Best wishes
Monty
Newb question about lists
Moderator: General Moderators
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Newb question about lists
What you have here is a many-to-many relationship. You have many members that can have many courses and many courses that can have many members. You'll need three tables: members, courses and a join table. Here's an example:
members
member_id
firstname
lastname
etc...
courses_members
member_id
course_id
courses
course_id
coursename
etc...
-Shawn
members
member_id
firstname
lastname
etc...
courses_members
member_id
course_id
courses
course_id
coursename
etc...
-Shawn
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Newb question about lists
That sounds like perfect logic and a good approach.
Care to expand a little or pooint me at a good tutorial ?
Best wishes
Monty
Care to expand a little or pooint me at a good tutorial ?
Best wishes
Monty
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Newb question about lists
You join the tables when you do a query, so you can get all courses someone is assigned to or get all people assigned to a course etc...MiniMonty wrote:That sounds like perfect logic and a good approach.
Care to expand a little or pooint me at a good tutorial ?
Best wishes
Monty
This is a simple one: http://www.tonymarston.net/php-mysql/many-to-many.html
I use the CakePHP framework for many of these types of things. If you follow the conventions and get the db schema right then its a breeze.
-Shawn
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Re: Newb question about lists
Looks like some good reading for this evening !
Thanks
Monty
Thanks
Monty
Re: Newb question about lists
Hmmmm....
well I read it (three times) and I see the concept but can't help feeling a bit out of my depth !
Just installing Cake now so see how that goes.
One question:
Three tables:
1) myMembers
2) courses
3) myMembers_courses
But in which do I record which member has actually signed up for which course ?
Or vice versa ?
If in members I still see the need to record a list like "1,4,6,2,3" (being the course number id)
and if in courses I see the same problem.
Or does the courses table grow and grow to hold a new record every time any member signs
up for any course ?
Am I missing something basic and fundamental ?
I could do with a good old talking to about this so if anyone is in the mood to write a blurb
on helping newbs understand "many-to-many" please speak freely.
If your example were to include a student / tutor relationship where six courses were on offer
to an unlimited number of students you won't find me complaining to the headmaster.
Best wishes
Monty
well I read it (three times) and I see the concept but can't help feeling a bit out of my depth !
Just installing Cake now so see how that goes.
One question:
Three tables:
1) myMembers
2) courses
3) myMembers_courses
But in which do I record which member has actually signed up for which course ?
Or vice versa ?
If in members I still see the need to record a list like "1,4,6,2,3" (being the course number id)
and if in courses I see the same problem.
Or does the courses table grow and grow to hold a new record every time any member signs
up for any course ?
Am I missing something basic and fundamental ?
I could do with a good old talking to about this so if anyone is in the mood to write a blurb
on helping newbs understand "many-to-many" please speak freely.
If your example were to include a student / tutor relationship where six courses were on offer
to an unlimited number of students you won't find me complaining to the headmaster.
Best wishes
Monty