I using PHP & MySql. Here's the situation:
1. I have a website which is a meeting place for employees & employers. Employees can sign up for an account & select the skills they have. These skills list will be shown on their resume. I have an array of about 100 skills & I am storing the values directly in DB as comma separated values.
For example, I have skills like PHP, ASP, .NET etc. & they have values of 1, 2, 3 etc. Say employee checks 1,2,4,6,10 skills (out of 100), this will be stored under 'skills' field in 'Resume' table as 1, 2, 4, 6, 10.
When the Employee visits his resume, the corresponding skills will be shown instead of the skill numbers (which is right). i.e on his resume, Employee sees PHP, ASP, C#, Java, Cobol (These are the respective skill names for the skills selected). So I have no problem so far.
2. Now Employers can search for users with specific skill set. Employer can go to a search page which shows all the 100 skills. Employers can tick the appropriate checkboxes & press the submit button to get relevant results. For example, now say an Employer wants to search for all Employees who has PHP, C# and Cobol skills in their resume (Employees may have more skills but PHP & Java skills are mandatory & must exist in Employee resume). So the Employer is going to ticks the corresponding checkboxes that refer to each of those mentioned skills & press the submit button. Now this should show all results of Employees who MUST have those ticked skills in their resume. Here is where the problem starts.
I have written SQL query to get the results but it produces results only for perfect matches i.e if Employer selected PHP, C# and Cobol skills for search, then I get the array values & store them as comma separated values in a string (example: 1, 2, 5) and send it to query. Then only Employees who has selected only 1, 2 and 5 as their skills are shown. But I want to display all users who have even selected 1, 2, 5,10 and 1, 2, 3, 4, 5, 10, 13, 100 and 1, 2, 4, 5, 22, 98. because all these Employees have the skills (1, 2 and 5) that the Employer has originally searched for.
I tried different versions of the same query using AND and OR but of no use. I did come across another way of storing the Employee skills in database & that would be as:
EMPLOYEE ID EMPLOYEE SKILL ID EMPLOYEE SKILL NAME 1 1 PHP 1 2 ASP 1 5 C#
But the above method would mean that if I have 100 skills & if an Employee selects at least 50 skills, this would create 50 rows of information, all for the same person. Additionally, I have the Employee resume as one of the fields in the same table which I would like to procure in 1 shot. So creating multiple rows for the same Employee is useless. And talk about the number of rows for, say, 100 Employees who have selected at least 50 skills, that would be 100 (Employees) x 50 (skills) = 5000 rows of information just for 100 people! A very inefficient way, I feel. So I thought comma separated values might help. So in this way, I can get skills, resume & other fields & I have to just maintain only 1 row for every user ( that would be only 100 rows in case of 100 Employees, think about the time save !)
I hope that I have been clear with my question. I just need to get all the Employees that match at least the skills selected by the Employer & not just Exact matches. I would consider Exact match search at a later stage but right now, the former is top priority.
Any help is highly appreciated.
Thanks very much & look forward to your replies.