2

I have a table which contains a userID (candidate_id) and a skill

enter image description here

On a seperate table (required_skills) I have column with skills split by a comma (,) for example Administrator,office

I am trying to create a SELECT statement to find where a candidate has all skills listed in the 'required_skills' table.

I have tried using an array but not knowledgeable enough in them...

$myString = "$skills";
$myArray = explode(',', $myString);
foreach($myArray as $my_Array){

$result=mysql_query("SELECT * FROM candidate_skills WHERE skill LIKE '%$my_Array%'");

This obviously isn't giving me what I want as it creates a select statement for each row...

Can anyone advise?

Designer
  • 477
  • 2
  • 12
  • **WARNING**: Do not use the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface which was removed in PHP 7. A replacement like [PDO is not hard to learn](https://phpdelusions.net/pdo) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Here parameters are **NOT** [properly escaped](http://bobby-tables.com/php) and this has severe [SQL injection bugs](http://bobby-tables.com/) in this code. Escape **any** and all user data, especially from `$_POST` or `$_GET`. – tadman Feb 22 '19 at 17:21
  • @PatrickQ - would you be able to provide an example select statement if I had 2 comma separated values? as the 2 values could be in a different order and not identical. – Designer Feb 22 '19 at 17:24
  • 1
    @PatrickQ that would only work if the candidate has no extraneous skills. If "a,c" is required, and the candidate has "a,b,c", that candidate would be ruled out. – Uueerdo Feb 22 '19 at 17:27
  • @PatrickQ - therefore that would not work. One field could be `office,administrator` and the other `administrator,office` – Designer Feb 22 '19 at 17:28
  • Exactly right @Uueerdo! – Designer Feb 22 '19 at 17:28

2 Answers2

2

Putting comma-separated lists in a database is almost always the wrong way to go about things. You should have separate tables for skills, candidates, and whatever the skills are required for; and two more tables: one linking candidates to skills, the other linking the things skills are required for to the skills they require. You can then use these intermediate tables to measure how well a candidates skill set fulfills the set required.

This will get you a list of all candidates that have at least one of the skills required, ordered by how many of the skills they fulfill from best to worst:

SELECT vs.vacancy_id, cs.candidate_id
   , COUNT(DISTINCT vs.skill_id) AS requiredSkillCount
   , COUNT(DISITCT cs.skill_id) AS candidateSkillCount
FROM vacancy_skills AS vs
LEFT JOIN candidate_skills AS cs ON vs.skill_id = cs.skill_id
WHERE vs.vacancy_id = something
GROUP BY vs.vacancy_id, cs.candidate_id
ORDER BY candidateSkillCount DESC, cs.candidate_id ASC
;
  • you can use a HAVING candidateSkillCount = requiredSkillCount clause to only get the fully qualified ones (or < to get the partially qualified ones),
  • you can join to the candidates table to get the candidates' name,
  • you can join to the skills table to use GROUP_CONCAT(skills.name ORDER BY skills) AS relevantSkillsList to get a comma-separated list of the skills in the results as well (you'll need to join to it twice if you want name lists for the skills required and the skills possessed)
Uueerdo
  • 15,723
  • 1
  • 16
  • 21
  • The query will depend on what exactly you are looking for. Are you trying to find if a specific candidate has the skills or a... whatever the skills are required for, all the candidates that have all the required skills, all the "things" a specific candidate fulfills all the requirements for, etc...? – Uueerdo Feb 22 '19 at 17:26
  • I am ideally wanting to run a SELECT query to find candidates that have ALL the skills and a seperate SELECT query to find candidates that have SOME of the skills... – Designer Feb 22 '19 at 17:27
  • Ok, I added a general query that should get you started. – Uueerdo Feb 22 '19 at 17:34
  • This is exactly what I am looking for. So, I have created another table called `vacancy_skills` with the columns `skill` and `vacancy_id`. I need to somehow understand your select statement to work with this..... – Designer Feb 22 '19 at 17:40
  • 1
    Are you able to edit your code @Uueerdo with his new tables? – Shane Feb 22 '19 at 17:45
  • @Designer keep in mind that if vacancy_skill.skill is a string type (varchar, char, etc..) the table will take more space, the join operations can be slower, and the corresponding candidate_skills.skill I would presume exists would need to have it's own strings coordinated; a single shared and referenced skills table standardizes the skill representations (names), saves space, and improves performance. – Uueerdo Feb 22 '19 at 17:59
  • @Uueerdo - the skills are populated by the user, not a pre defined list so I cannot use an ID to identify them – Designer Feb 22 '19 at 18:00
  • @Uueerdo - is there anyway we can move this to a chat? – Designer Feb 22 '19 at 18:01
  • The query will work with strings, just not as well; just take the `_id` off the `skill_id` columns. Sorry don't have time to chat. – Uueerdo Feb 22 '19 at 18:03
  • Okay so adding your code above, and this - `$Cands = mysql_num_rows($result); ` shows 0? but there is one candidate with both skills? – Designer Feb 22 '19 at 18:08
  • You should update the question with your attempt; are you specifying the "something" value for vacancy_id (vacancy_id should be a reference to a vacancy, not a row identifier for a required skill). – Uueerdo Feb 22 '19 at 18:28
-1

It will help out here

  $skills='Administrator,office';
  $result=mysql_query("SELECT * FROM candidate_skills 
  WHERE skill IN ($skills)) ;
Nitin Sawake
  • 1
  • 2