SELECT question

[dcotter]

There was an article posted about a month ago about many-to-many relationships. I have the following Question:

I need to create a many-to-many relationship where members can select interests, like follows:

members:

member_id, lots_more_stuff

interests:

interest_id, name_of_interest

member_interests:

member_id, interest_id

Question is this: I need to select member info WHERE a member has two different interests. How/can I do this in one query?

I.E.,

SELECT first_name, last_name, email FROM members WHERE (member has skiing, and boating as interests)...

Anyone?

[rlisboa]

Daren,

I'm not sure if it's the best way, but I think it works:

assuming:

member_interests:

member_id | interest_id
1 | skiing
2 | boating

1st idea:

$interest1 = "select member_id from member_interests where interest_id = '1'";

$interest2 = "select member_id from member_interests where member_id = '$interest1' AND interest_id = '2'";

//$interest2 will return the ID of the user
//that likes skiing and boating. Now you can
//use this ID in another select:

$user_info = select first_name, last_name, email FROM members WHERE member_id = '$interest2';

Well, that's it. But I'd love to find a unique SELECT to do all this job.

Hope it helps!

Ricardo

[rlisboa]

Sorry bud, the right table example is

member_interests:

member_id | interest_id
1 | 1
1 | 2

Ricardo

[[deleted]]

Assuming you don't allow people to select the same interest twice, you can get the ids of members with exactly 2 interests like this:

SELECT member_id FROM member_interests GROUP BY member_id having count(*)=2;

[dcotter]

Vincent,

What I need to do, however, is be able to select all members' info that have certain 2 or 3 interests selected. There may be 40 total interests, and I need to grab all the members for 2, 3, or even 4 certain interests. I've pretty much given up on trying to find one query to do the job. The only way I think I can do this is to have one query grab the member IDs for each interest, and then programmically remove the duplicates, but with lists as large as 1,000,000 members, I don't know how well this will work. If anyone comes up with anything, please let me know!

[rlisboa]

What about storing the interest_id in one entry only? Then you can have something like this...

user_id | interest_id
1____|1,2,5,7,14
2____|1,2,8,9,10

doing this you can easely select users by multiple interests, but each time a user updates his profile, you'll have to check all values in his field, compare to the new entry and then update the field with the actual values + the new ones...

[[deleted]]

You could Join the members and the member_interests and count how many hits you get in the member_interests table:

members:
id,name
1,John
2,peter

Member_interests:
mid,iid
1,1
1,2
2,2
3,4
1,3

SELECT ,count()
FROM members,member_interests
WHERE members.id=member_interests.mid
AND (iid=1 or iid=2)
GROUP BY mid;

will give you a count of how many times a (iid=1 or iid=2) matched in themember_interests table.

you can expand the (iid=1 or iid=2) to include as many interests as you like.

ofcourse you could join the interests tale itself to be able to search for interest names, but that would make it far too complex.

[rlisboa]

great. : )

[cdherold]

Regarding your post from February 15, 2001 ... did you ever find out how to do that.

I'm dealing with a similar issue and can't seem to get any clear cut info on how to go about solving what I'm told is a many-to-many issue.

My deal is that I've got users who enter skills (e.g. C++, PHP, Java) and then employers looking for people with a certain skill (e.g. PHP) can search through the database to see who has it and get all of those individuals contact information.

Let me know if you've got any tips on that. It didn't seem like such a problem until i started trying to solve it.

thanks, chris

[[deleted]]

What's your database structure?

[cdherold]

dude,

I wish I knew. I'm dealing with that right now. The way it looks it's gonna be a little complicated. I haven't done this before and it's looking like a complicated many-to-many relationship.

If you're interested ...

User's enter e-mail (email), select any number of skills they have from a list of 20 (skills), they select any number of highest degree(s) earned from a list of 8 (degree), they select any number of degree concentrations from a list of 20 (concentration), they select any number of job categories they are searching for from 20 (job_category).

They also select one desired salary (salary).

Right now i'm looking at setting up table user with user_id, email,salary. And then setting up another table with skills_id,skills,rel_user_id. And then other simlilar tables for degree, etc.

I'm worried that the skills table is poorly formed as for each user there could be total of 20 rows. not cool. that would be similar with degree,concentration and job_category.

unsure whether i need to set up some other tables to help store this data.

still a little confused. let me know if you have any input on this structure or if it's not clear.

thanks, chris

[[deleted]]

Many-to-many relatioinships are easy.

All you do is create one table with 'people' and another table with 'skills'

Then you put the relations between the people and the skills in a third table.

You can search the data by searching the third table. You may have to run multiple queries though.
The idea is that the third table contains the people_ids and the skill_ids.
If you are looking for a person who has skill 1 and skill 2, you first look for all the people who have skill 1.
Then in the next query you put that you want all the people who have skill 2, but only if the people_id is in the range or people_ids you got from the first query.
For the third parameter you'd search in the people that you got from the second query, etc. untill you either run out of parameters to search for, or the query doesn't find any people anymore.

[cdherold]

ok ... that makes sense.

If you've got a little more time for tutoring a budding database designer ...

I have another basic conceptual problem. How would that information be written to the database using multiple-entry forms.

For instance, the user enters e-mail and then goes to the skills selection dialog box and selects three options from a list of 20.

The user e-mail is written to the user table and automatically given an id using auto_increment. I can do that

And i've got the skills table already set-up with each skill given an ID.

But then how do I write to the skills_id,user_id relational table?

I get an array of values from the multiple entry dialog box. What do i do with it to get the user id's aligned with the selected skills?

chris

[[deleted]]

Hold on, I'm confused now.

Is this the part where you search of or the part where you are entering new people?

Oh what the hey,
send me a list of all the information you want to store, like name, email, skills, and what you want to be able to do with it (both for inserting and for searching), and I'll see what I can do to make a database design. Heck, I can probably use it as an article on my Yapf! site anyway.

[cdherold]

you rock! Go Yapf!

Ok, here it is.

This whole thing is for a Resume database. People will enter a number of specifics on the type(s) of job(s) they are looking for and then submit their resume. you can see this submit form at:
http://www.omniomix.com/resume_post.php

So, the reason the people (submitting the resumes) enter all this information when submitting the resume is so that the employers will have some basis by which to search for resumes that are relevant to the jobs they are trying to fill.

So for the SEARCHES ... i need to have a form, similar to that which i have for resume submission, that will allow the employers to select skills, etc that they want to see in the people they want to hire. this search will then go through the database and select the users that have these skills, are looking for this salary, or whatever, and then post this to the webpage.

the set-up i have now allows resume submissions ... and you can view the recently submitted resumes (resume_list.php), but this is all flat file stuff ... apparently very poor database design (so i was told ... and i rapidly began to see as i tried to develop some sort of algorithim to search the whole mess of data).

I think that's most of what you would need to know. let me know if you there's any more information that you might need.

thanks,

chris

[[deleted]]

http://www.hvt-automation.nl/yapf/database/design.php

[cdherold]

wow, thanks vincent. that's pretty comprehensive. It looks like i still have a little work to do to get the thing implemented, but your help has definitely gotten me over MANY conceptual hurdles.

I'll let you know how it goes.

muchos gracias,

chris

[[deleted]]

you're welcome.

[cdherold]

hey vincent,

I got the database done and it works ... the forms are written from the database and it all gets stored and it's all very cool ... but the search thing is killing me. I can't seem to weasel it.

So what I'm doing is taking the array that is entered in on the select page which ...an option select where the user can pick any number of numbers (that represent skills, for instance) between 1 and 12. so the array could contain 1,2,5,6. So I implode the array to get it 1,2,5,6 with
<code>
if ($skills)
{
$skills = implode($skills, ",");
} {$skills = "";}
</code>

and then I have the following to select the person_id that have all the selected skills. (this is pretty much from you)

<code>
$query = "SELECT person_id, count(*) AS cnt
FROM r_people_skills_link
WHERE skills_id IN ($skills)
GROUP BY person_id";

$skills_person_id = mysql_query ($query,$db);
</code>

But what if no skills are selected and $skills is empty? is this going to work and select all the person_id's in that group?

And after I get a number all my sub-selects (i.e. $skills_person_id, $concentration_person_id, $degree_person_id and $category_person_id), which should all have person_id lists which correspond to what was selected, how do i compare them to see which person_id's are included in each set?

hope this makes sense.

thanks again for all your help,

chris

[[deleted]]

"But what if no skills are selected and $skills is empty? is this going to work and select all the person_id's in that group?"

If a person selects no items, then there is no point in looking for people who match those items, is there? :-) You can just skip the query and forget about it.

"how do i compare them to see which person_id's are included in each set?"

Right now your query returns all people who have at least one match in the skills.

What you want to end up with is a list of people who have at least one match in all the selected items (not just for skills)
and you'll want to order them by the number of matches. (most matches first, then the people with fewer matches)

So in the end you just want a list of people and the total number of mathes per person.
So you can simply take the results from the first query, put them into an array like this:

$aPeople = array();
$aPeople['person_id']=$cnt;

so you get an array that contains the score, indexed by person_id. If a person has person_id=10 and cnt=4, then the 10th element will contain 4.

For the next query, you add the results to the same array. If person_id=10 gets a score of 7, then you add 7 to the previous score of 4, giving a total of 11.
If a person did not score in the first query but did score in the second, then just add the person to the array and enter his score for the second query.

After you ran all the queries, you will have a list of people and the total score per person.
You can calculate the total number of selected items from the POST data from the form, so you can determine which people have a 100% score.