CODEWITHSUNDEEP
phpmysql
Brandon Young
Brandon Young

Reputation: 523

Best Approach in Creating MySQL Query for this

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

Answers (4)

JohanB
JohanB

Reputation: 2148

Updated according to the updated question:

I would create a DB with 5 tables:

  • People (containing the peoples)
  • Skills (containing all possible skills)
  • PeopleSkill (containing for each people, for each skill, a score)
  • Event (containing the events created by the admin)
  • EventSkillsRequired (contains an evenid and a skill id)

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

Naveen Kumar
Naveen Kumar

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

Vikolai
Vikolai

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

DonCallisto
DonCallisto

Reputation: 29932

Simply do that:

  • Create onto your database some entities like: event,skill,people.
  • Previous tables have to be related with foreign key(s); So you'll probably have to build some "intermediate" entities (think to event/skill cardinality and skill/people).
  • Now you can, for every event, query the DB an dinamically extract how many skills have to considered for that particular event
  • At this point, you can create dinamically a string for your WHERE condition
  • The trick is done

Hope 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

Related Questions