MYSQL Query Help - Comma Seperated Values

[ArmoVanBuuren]

Just wondering if any of you can help me out with my query to find employees expertise, employees expertise is a text field and I have a search box where you can search for employees with relevant expertise. The problem I am having is employee can have more than one type of expertise either separated by a space, comma, full stop etc. I would like to be able to search for more than one term no matter how it is split up in the expertise e.g. by comma, full stop, space etc

Would like it to catch all of these if the terms shooting passing were entered

1. Shooting Passing
2. Shooting, Passing
3. Shooting and Passing
4. Shooting. Passing.
5. Shooting; Passing

I think I need a regexp to catch all of these



The code I currently have is below, this is ok for when i enter "shooting, passing" but not "shooting passing".

 $sqlExpertise=mysql_query("SELECT * FROM staff WHERE FIND_IN_SET('$searchValue',expertise)")or die(mysql_error());

Just wondered if anybody could help me redefine my search?

 

[GravyMonster]

TBH if you can I'd redesign your database to accomodate the 1-to-many relationship with expertise.

You'd need another 2 tables...

Employees
---------------------
EmployeeID
Name (e.t.c)

EmployeeExpertise
---------------------
EmployeeExpertiseID
EmployeeID
ExpertiseID

Expertise
--------------------
ExpertiseID
Type

Your search would then just involve a couple of joins and a 'WHERE EmployeeExpertise.ExpertiseID IN (1,2,3,4)'

IMO it's far easier, and faster - don't know if your hands are tied with the DB schema though.

Also, to choose the expertise required on a form, I'd suggest a drop down with multiple selections available rather than a free text field.

 

[LazyManc]

What you've got is a many-to-many relationship, so ideally you'd re-design (normalise) your database so that you had 3 tables:

• employee [id, name, etc]
• expertise [id, type(1 of shooting / passing / running, etc)]
• employee_expertise [employee_id, expertise_id]

This is true for any field where you find yourself adding multiple values seperated by some delimiter like a comma / semi-colon etc.

If you can't do that, then you can use more than one LIKE in the WHERE clause.

i.e.

SELECT * 
FROM employee
WHERE employee.expertise LIKE '%shooting%'
OR employee.exepertise LIKE '%passing%'

**EDIT** Beat me to it

 

[ArmoVanBuuren]

Thanks for your replies unfortunately the expertise field is a text field and I cant change this, an example is below of the data. It is the responsibility of each engineer to enter his own expertise areas in the field which could be anything so would struggle to use drop downs.

Sample Expertise Text For An Employee

Product information on model planes, Product and drawing issues on Tyco units, Secretary of Tyco Long Service Association

What I would like to be able to do is say enter "Tyco, Planes, Association" and then the above record would be found and any other records containing one of the terms "Tyco, Planes, Association" found aswell?

 

[LazyManc]

Thanks for your replies unfortunately the expertise field is a text field and I cant change this, an example is below of the data. It is the responsibility of each engineer to enter his own expertise areas in the field which could be anything so would struggle to use drop downs.

Sample Expertise Text For An Employee


What I would like to be able to do is say enter "Tyco, Planes, Association" and then the above record would be found and any other records containing one of the terms "Tyco, Planes, Association" found aswell?

Do you have control of the database design (i.e. are you developing a new system or is there an existing app)? Just because a field on a web page is a single text-field, it doesn't mean you can't break that down into individual "expertise" records:

Table expertise:
id | detail
-----------------------------------------------
1 | Product information on model planes
2 | Product and drawing issues on Tyco units
3 | ecretary of Tyco Long Service Association
4 | etc....

Seriously, if you can do it this way, you'll save yourself heaps of trouble later on. For searching you would just make the "detail" field a fulltext index, which will improve the speed of your LIKE searches, you could even use SoundEx for getting around typos.