How to write mysql query to search database?

Question

I own a website and I'm trying to write a search feature that will search the database for the terms the user is searching for. I have two fields in the database I'm searching against TAGS and NAME

Currently, the way I'm doing it is I am taking the search term divide it up into multiple words and then search the database using those terms. So if a user is searching for "New York" my query will look like this

SELECT * FROM wallpapers 
WHERE tags LIKE '%New%' OR name LIKE '%new%' 
or tags LIKE '%York%' OR name LIKE '%York%'

The problem with that of course is that anything with the term new in that will be pulled up also like say "new car" etc. If I am going to replace the query above with the below code then it will be too vague and only like two wallpapers shows up .

SELECT * FROM wallpapers 

WHERE tags LIKE '%New York%' OR name LIKE '%New York%'

Anyone having any better way to write the search query? 

Answer 1

Seems like you require to introduce the concept of relevance.

Try:

select * from (
SELECT 1 as relevance, * FROM wallpapers 
WHERE tags LIKE '%New York%' OR name LIKE '%New York%'
union
select 10 as relevance, * FROM wallpapers 
WHERE (tags LIKE '%New%' OR name LIKE '%new%') 
and (tags LIKE '%York%' OR name LIKE '%York%')
union
select 100 as relevance, * FROM wallpapers 
WHERE tags LIKE '%New%' OR name LIKE '%new%' 
union
select 100 as relevance, * FROM wallpapers 
WHERE tags LIKE '%York%' OR name LIKE '%York%'
)
order by relevance asc

By the way, this will perform very, very poorly if your database grows too large - you need to be formatting your columns consistently so they're all upper case (or all lower case), and you need to be avoiding wildcards in your where clauses if you probably can.

When you face this problem, you can look at the full text searching. 

Want to be a SQL expert? Come and join this SQL Certification by Intellipaat.

Do check out the video below