For that site I am focusing on, I would like a person to be capable of checkmark multiple boxes that represent things he/she may be interested, much like StumbleUpon. A person would check 'web development' and 'web design' then click 'Submit', which may then store his preferences inside a database.
Later, if somebody produced a task which was labeled and among the preferences he selected, that user would have an update. Therefore if I designed a new project nevertheless "Creating a websiteInch and checked the course "web designInch, all customers who had "web designInch selected on their own personal profiles would acquire some type of message or email notifying these to the recently produced subject.
What's the easiest method to implement this in MySQL format? I checked out some pages on controlling hierarchical data (you will see generalized groups like "Computer systems" or "Music" as well as an admin will have the ability to add/remove/edit groups), but no techniques appeared to become things i needed - a minimum of, not when it comes to thinking I am stuck in. Possibly there's an simpler answer available that I have been looking over?
Produce a table that contains the different interests. Say
Interests :- id, interest
A table which stores all of the interests selected with a user as
UserInterests :- user_id, interest_id
Along with a project interest relation as
ProjectInterest :- project_id, interest_id
Now whenever a new project is added you are able to operate a query like the following onw to obtain the customers the project wil attract
SELECT DISTINCT user_id FROM UserInterests ui, ProjectInterests pi WHERE ui.interest_id = pi.interest_id AND pi.project_id = <new project id>
Or, while using explicit join syntax:
SELECT DISTINCT user_id FROM UserInterests ui INNER JOIN ProjectInterests pi ON ui.interest_id = pi.interest_id WHERE pi.project_id = <new project id>
What you're asking - I believe - is how you can implement a many-to-many relationship. However , you cannot provide a user a listing of interests without securing yourself into a precise quantity of interests they are able to choose.
The answer is really a Junction Table. Similarly, you've your listing of customers, and however, you've your listing of interests. The junction table is really a third table that lists the connection between both of these groups.
CREATE TABLE `user_interest` ( userid UNSIGNED INT REFERENCES `user` (userid), interestid UNSIGNED INT REFERENCES `interests` (interestid), PRIMARY KEY (interestid, userid) )
Now you must a listing of UNIQUE mixtures of customers and interests. Let us if you have a listing of news articles, each having a single subject ("interestid") designated into it. You can now make a move like,
SELECT * FROM `article` WHERE `article`.`interestid` IN ( SELECT `interestid` FROM `user_interest` WHERE `userid` = X )
That will retrieve their email list of articles associated with user X's selected interests. First, you receive their email list of subjects which were associated with your specified user, then you definitely obtain the listing of articles with matching subjects.