/    Sign up×
Community /Pin to ProfileBookmark

MySQL LIKE Search

Copy linkTweet thisAlerts:
Aug 12.2006

How can I modify this, so it uses LIKE, and then oreder it by score?

This is for a site search.

[code=php]$query = “SELECT urltitle,description,keywords,cat,subcat,url,id,body,
MATCH(urltitle,description,keywords,body) AGAINST (‘$var’ IN BOOLEAN MODE)
AS score FROM web_search WHERE MATCH(urltitle,description,keywords,body)
AGAINST (‘$var’ IN BOOLEAN MODE) ORDER BY score”;[/code]

to post a comment
PHP

7 Comments(s) ↴

Copy linkTweet thisAlerts:
@bokehAug 12.2006 — How can I modify this, so it uses LIKE, and then oreder it by score?[/QUOTE]Why would you want to. The fulltext query you are using will produce much more predictable results.
Copy linkTweet thisAlerts:
@The_Little_GuyauthorAug 12.2006 — url, urltitle, description, keywords, body, cat, subcat, id

My table has those as what it searches in, is there a way for me to make certain ones rank higher than others?

If the search has something in the url column, I would like that to be ranked the highest, Here is my order of importance:

- url (ranked first if is part of search)

- urltitle (ranked inbetween if is part of search)

- description/keywords/body (ranked lowest if is part of search)
Copy linkTweet thisAlerts:
@bokehAug 12.2006 — You are not even searching the [I]url[/I] column with that query!

If you want to weight a column create a second fulltext index on that column and use it in your ORDER BY or GROUP BY clause. Maybe like this:[CODE]SELECT [I]col1[/I], [I]col2[/I], [I]col3[/I], [I]col4[/I],
MATCH([I]col2[/I], [I]col3[/I]) AGAINST ('[I]$search_text[/I]' IN BOOLEAN MODE)
AS [I]score[/I]
FROM [I]tablename[/I]
WHERE MATCH([I]col2[/I], [I]col3[/I]) AGAINST ('[I]$search_text[/I]' IN BOOLEAN MODE)
ORDER BY MATCH([I]col2[/I]) AGAINST ('[I]$search_text[/I]' IN BOOLEAN MODE), [I]score[/I] DESC[/CODE]Also consider parsing the search string and adding boolean modifiers to add weight. For example adding a plus sign in front of important patterns.
Copy linkTweet thisAlerts:
@The_Little_GuyauthorAug 14.2006 — when it searchs, it does't search for additions.

I have this in the in the database: [B]1-877-789-BEAR[/B]

and if my search was [B]bear[/B], it skips over that. I would like it to include that in the search, since that was there.
Copy linkTweet thisAlerts:
@bokehAug 14.2006 — [CODE]SELECT [I]col1[/I], [I]col2[/I], [I]col3[/I], [I]col4[/I],
MATCH([I]col2[/I], [I]col3[/I]) AGAINST ('[I]$search_text[/I]' IN BOOLEAN MODE)
AS [I]score[/I]
FROM [I]tablename[/I]
WHERE [I]colx[/I] LIKE '%[I]$whatever[/I]%'
AND MATCH([I]col2[/I], [I]col3[/I]) AGAINST ('[I]$search_text[/I]' IN BOOLEAN MODE)
ORDER BY [I]score[/I] DESC[/CODE]
Copy linkTweet thisAlerts:
@aussie_girlAug 14.2006 — when it searchs, it does't search for additions.

I have this in the in the database: [B]1-877-789-BEAR[/B]

and if my search was [B]bear[/B], it skips over that. I would like it to include that in the search, since that was there.[/QUOTE]

I think you might need to check the manual again of how to use the FULLTEXT IN BOOLEAN MODE properly if you are going to use it..also read some of the restrictions

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Copy linkTweet thisAlerts:
@bokehAug 14.2006 — I think you might need to check the manual again of how to use the FULLTEXT IN BOOLEAN MODE properly[/url][/QUOTE]I agree. There is nothing wrong with the first query. If you want to emphasise an item use a boolean operator. If you want to weight a column work on your order by clause.
×

Success!

Help @The_Little_Guy spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...