Partial keyword searching in MySQL

Question

Context: I am trying to create a search function for my website where a user can type in full sentences and receive results back based on the matching of keywords in the sentence with words stored in a MySQL database:

**ID | Skill**
1    | Painting
2    | Carpenter
3    | Builder

For example a user may search "I want some painting to be done" and using the following MySQL query (along with a foreach and explode function) it will return ID 1 from the database:

$stmt = $mysqli->prepare ("SELECT username FROM users WHERE users.id IN (SELECT
skills.userid FROM skills WHERE skills.skill LIKE CONCAT('%',?,'%') GROUP BY 
skills.skill ORDER BY CASE WHEN skills.skill LIKE CONCAT(?,'%') THEN 0 WHEN
skills.skill LIKE CONCAT('% %',?,'% %') THEN 1 WHEN skills.skill LIKE CONCAT('%',?)
THEN 2 ELSE 3 END, skills.skill)");

Exam question: The issue I have is that if a user was to type "I want a painter" then ID 1 would not be returned. How can the query be modified to account for the fact that painting and painter are similar and so should be returned?

You would use a synonyms table to include synonyms for all the skills. — Gordon Linoff, Mar 29 '14 at 23:07
@CarlMarkham PHP and MySQL for homework? What kind of educator teaches with those tools? Don't be silly. — Chris Wesseling, Mar 29 '14 at 23:22
@CarlMarkham It's a descriptor used in structured problem solving to say 'just what the hell is the problem' i.e. what is the exam question I'm being asked — noobmaster69, Mar 29 '14 at 23:52

score 0 · Answer 1 · answered Mar 29 '14 at 23:18

You can add to skills table a column called synonymous with some keywords for that skill.

For example, the "Painting" row will have a "paint painting paintor" in synonumous column.

Then you change your query to check for synonymous column insted of skill column.

This is the simples way, but requires that you put a synonymous to each skills table row.

Partial keyword searching in MySQL

1 Answers1