Reputation: 523
We need to create a program in PHP/MySQL that will display all the 'people', who have certain skill/s that may be needed during an event. Each person has at least a skill and with a rating of 1 - 5 depending on how skilled is that person for a specific skill. For example, John have 2 skills - painting and drawing. He is expert in painting so his rating for painting is 5 while rating of only 2 for drawing, for he can draw but not very good.
There are events that the admin can add, which contains the needed skills for that specific event. Each event will contain skills from only one to eight different skills. For example, the user will create an event called Art Class Tutorial and will then select from the pre-defined skills (provided by the program) which skills are needed for it. In this case for example, the admin selects Sculpting, Drawing and Painting. Next, the admin will need to find all the 'people' with the skills Sculpting OR Drawing OR Painting (so that they can participate in teaching in the event), in which ratings should not be below 3.
In this case, John should be displayed because he has a 5-star rating for painting even if his drawing is just 2-star. If for example, Bob has sculpting, drawing and painting but all of them have a rating of 2-star, Bob should not be displayed.
So my problem is this: how can I create a query for this since the program does not know how many skills an event needs and which skills are chosen? The combination of possibilities are too many for me to create if's for each possible combination.
By the way, in my table for each 'person', there exist the eight fields (one for each skill) which should contain each skill rating per skill. Rating 0 means not skill in that area.
Here is my current code on this:
function search_results($keywords){
$returned_results = array();
$where = "";
$keywords = preg_split('/[\s]+/', $keywords);
$total_keywords = count($keywords);
foreach($keywords as $key=>$keyword){
$where .= "`keywords` LIKE '%$keywords%'";
if($key != ($total_keywords - 1)){
$where .= " AND ";
}
}
$results = "SELECT `title`, LEFT(`description`, 70) as `description` , `url` FROM `articles` WHERE $where";
$results_num = ($results = mysql_query($results)) ? mysql_num_rows($results): 0;
if($results_num === 0){
return false;
}else{
while($results_row = mysql_fetch_assoc($results)){
$returned_results[] = array(
'title' => $results_row['title'],
'description' => $results_row['description'],
'url' => $results_row['url']
);
}
return $returned_results;
Upvotes: 2
Views: 218
Reputation: 2148
Updated according to the updated question:
I would create a DB with 5 tables:
The admin can create an Event into the Event table. He then creates EventSkillsRequired records with the event id and one of the skill id's needed for the event.
You need to create a php page that accepts an eventid. The following query return all people which have at least one skill with a skill score > 3 required for the event.
SELECT People.firstname, People.lastname, Skills.name, Skills.score FROM People
INNER JOIN PeopleSkill ON PeopleSkill.peopleid = People.peopleid
INNER JOIN Skills ON Skills.skillsid = PeopleSkill.skillsid
INNER JOIN EventSkillsRequired ON EventSkillsRequired.skillid = Skills.skillsid
WHERE PeopleSkill.score > 3 AND EventSkillsRequired.eventid = ${php evenid parameter}
Upvotes: 0
Reputation: 4601
Hope it helps
People's table
People_id People_name
skill's table
skill_id skill
listing table
people_id skill_id rating
then for each event
select people_name from people where people_id in
(SELECT people_id FROM listing WHERE
skill_id in (8,9,10) and (rating >=3 or rating =5) order by rating desc)
limit `NOpersonRequired
Upvotes: 1
Reputation: 1
Assuming you have 3 entities;
• 'people' with basic info such as name, contact details, etc
• 'skill' which lists the 8 different skills
• 'peopleskill' which acts as a link between the two, with an extra attribute 'Rating' showing their proficiency in associated skill.
So you could have in peopleskill, for example:
PERSON SKILL RATING
1 1 5
1 2 2
and so on. The code I imagine would then resemble something like this.
$result = mysql_query=("SELECT * FROM peopleskill WHERE Skill_ID = $optionone OR $optiontwo OR $optionthree");
while($row=mysql_fetch_array($result)){
$personid = $row['Person_ID'];
$rating = $row['Rating'];
if($rating=>3){
$result2 = mysql_query("SELECT * FROM people WHERE Person_ID = $personid");
while($row2=mysql_fetch_array($result2)){
echo $row2['First_Name'];
}
}
}
Apologies for the layout of this post, I have no idea how to get this formatting sorted, but hopefully you get what I mean.
Upvotes: 0
Reputation: 29932
Simply do that:
WHERE
conditionHope it is clear and will help you
Edit (under question's author request)
Intermediate entities are "special" entities that have to be created when you have a relationship of n/m between two entities (or table, if you prefer refer to those in that way). So, if 'event' and 'skills' have a m/n cardinality (and they have!) you have to create a table (say r_event_skills) where the PK of 'r_event_skills' is composed by combination of two foreign keys (one that is constrained to event's PK, the other constrained with skill's PK). Now, you have to repeat the same for skill/people and the trick is done.
Upvotes: 1