Hi all,
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:

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.

Have a look at this, it may get you started:

http://forums.mysql.com/read.php?52,...8684#msg-48684
http://dev.mysql.com/doc/refman/5.0/...on_find-in-set

Hey,

Why don't you have an employee table, a skills table and an employee_skill table. Hence:



Then when you list all the entries in the skills table for an employee to select, when entering their choices in the DB, you'd just need to create an entry in the employee_skill table. And as it'll be simply 3 numerical values, you could have millions of rows in this table without any issues, so long as you add the correct indexes. E.g. an id index, employee id index and a skill id index, and perhaps an employee id/skill id index.


Then when an employer is searching for people with specific skills, find the skill id values and do a query such as:


I think the above query will work. And to add more complexity, you could count the number of matches each employee gets to the employers skills and order them by relevancy, hence the most suitable employee is listed first.

R.

Thanks to both of you guys pritaeas & robothy for taking time to post your views/findings.

To pritaeas:
I tried using FIND_IN_SET syntax & it did not produce me the right result. I selected 1, 3, 5 options from the skills checkboxes & performed searched but the query returned 0 values, while an Employee exists whose skills are stored as 1, 3, 5, 7, 9. So I think this method did not work. Additionally, it only worked if I selected only the 1st checkbox (value 1) & it returned me the row in DB. But for multiple checkboxes, it did not return me any values. It's not even working if I select the checkbox with value 3 & perform the search nor any other value as to that matter.

I am hoping that there might be some other way to get around this. I will try digging into this more, but please do let me know if you have any more ideas. I will be waiting.

To robothy:
I haven't tried your method yet, but will give it a shot. Additionally, don't you think that since there will be numerous number of rows, the query speed will be much slower? I am thinking about 1000 Employees with about 50 skills roughly, i.e 50,000 rows of information! Is there anything at all which can help us reduce the time taken to execute & retrieve?
Look forward to your reply as well.

Thank you all once again. I will be awaiting further replies.

Using indexes will speed your queries up massively. At my last job, we used tables with over a million entries, and this query took at most 10 seconds to run.

So if you apply an index to the employee table on the ID, and likewise the skills table on the ID and to the fields in the employee_skill table as I said in my last post, that should be enough.

Best,
R.

Hi robothy,
Thank you once again for a fast reply. I will follow likewise for the index. But I am confused here:

SELECT `skillId` FROM `employer_skills`WHERE `employerId` = '1221'

Where is it coming from. I do not have a table called Employer skills (at least for now..maybe later). Right now, all the Employers would just use a simple form, which will have Skills checkboxes & other criteria. When the information is POST'ed from the form, say we capture the info in an array called $skills, how do I search using this array?

Please let me know. Thank you.

Right, sorry. I thought it may be that an employer has registered and saved the skills they desire in an employee.

Selecting the results from a form would work too. Just have the skills id as the value that is submitted if a checkbox is checked. Therefore, you'd have a list of skill id values to pass straight into your query.

R.

Hi,
But I think I might have some problems trying to pass the array values. Could you please give me the query to do that? Much appreciate if you could please do so. (I had much trouble trying to do it in a for loop previously.)
Thank you.

If you do as I said and have the value of each checkbox the skill id value from the skills table, then when you submit the form you'd get an array of skill values.

Implode the array into a comma separated string, and pass that to an SQL query such as that below:


R.

Thanks for the query, robothy. I will try that & let you know how it goes.

Thanks much.