How to implement a Keyword Search in MySQL?

santanu_79902 0
Viewed 147104

I am new to SQL programming.

I have a table job where the fields are id, position, category, location, salary range, description, refno.

I want to implement a keyword search from the front end. The keyword can reside in any of the fields of the above table.

This is the query I have tried but it consist of so many duplicate rows:

SELECT
    a.*,
    b.catname
FROM
    job a,
    category b
WHERE
    a.catid = b.catid AND
    a.jobsalrange = '15001-20000' AND
    a.jobloc = 'Berkshire' AND
    a.jobpos LIKE '%sales%' OR
    a.jobloc LIKE '%sales%' OR
    a.jobsal LIKE '%sales%' OR
    a.jobref LIKE '%sales%' OR
    a.jobemail LIKE '%sales%' OR
    a.jobsalrange LIKE '%sales%' OR
    b.catname LIKE '%sales%'
search mysql sql
7 Answers

For a single keyword on VARCHAR fields you can use LIKE:

SELECT id, category, location
FROM table
WHERE
(
    category LIKE '%keyword%'
    OR location LIKE '%keyword%'
)

For a description you're usually better adding a full text index and doing a Full-Text Search (MyISAM only):

SELECT id, description
FROM table
WHERE MATCH (description) AGAINST('keyword1 keyword2')
user_241810 
SELECT 
    *
FROM 
    yourtable
WHERE 
    id LIKE '%keyword%' 
    OR position LIKE '%keyword%'
    OR category LIKE '%keyword%'
    OR location LIKE '%keyword%'
    OR description LIKE '%keyword%'
    OR refno LIKE '%keyword%';
Jon_Bright_18130

Ideally, have a keyword table containing the fields:

Keyword
Id
Count (possibly)

with an index on Keyword. Create an insert/update/delete trigger on the other table so that, when a row is changed, every keyword is extracted and put into (or replaced in) this table.

You'll also need a table of words to not count as keywords (if, and, so, but, ...).

In this way, you'll get the best speed for queries wanting to look for the keywords and you can implement (relatively easily) more complex queries such as "contains Java and RCA1802".

"LIKE" queries will work but they won't scale as well.

user_148600

Personally, I wouldn't use the LIKE string comparison on the ID field or any other numeric field. It doesn't make sense for a search for ID# "216" to return 16216, 21651, 3216087, 5321668..., and so on and so forth; likewise with salary.

Also, if you want to use prepared statements to prevent SQL injections, you would use a query string like:

SELECT * FROM job WHERE `position` LIKE CONCAT('%', ? ,'%') OR ...
user_860150

I know this is a bit late but what I did to our application is this. Hope this will help someone tho. But it works for me: 

SELECT * FROM `landmarks` WHERE `landmark_name` OR `landmark_description` OR `landmark_address` LIKE '%keyword'
OR `landmark_name` OR `landmark_description` OR `landmark_address` LIKE 'keyword%' 
OR `landmark_name` OR `landmark_description` OR `landmark_address` LIKE '%keyword%'
user_87988030
Related