0

I have been having issues trying to write a proper SQL search query with PHP using the Like keyword to generate ordered results based on the keywords entered.

This is what I want to achieve

If I search for "Man thinking" , the result should come out like

Displaying results that match " Man thinking " --- 1st

Then Displaying results of " Man " --- 2nd

And then displaying results of " thinking " ---3rd

I used the Like keyword like this below

select * from tablename where description like % Man thinking %

but it only generated random results and not ordered by the keyword string I used

Grant
  • 2,413
  • 2
  • 30
  • 41
Tosin Onikute
  • 3,883
  • 6
  • 38
  • 61
  • 1
    https://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html – ladieu Mar 04 '14 at 19:01
  • http://use-the-index-luke.com - read this book. Wildard in front of query can seriously hinder your query speed. You might want to consider additional escaping of LIKE special characters: _ and % – Misiur Mar 04 '14 at 19:01
  • if you want ranking you should be using a full text search –  Mar 04 '14 at 19:01
  • how can using wildcards hinder my query ? – Tosin Onikute Mar 04 '14 at 19:02
  • To answer your last comment here, consider this -- when you have a wildcard in front of your query, it will look at all rows first to see if any of it has the value after the wildcard. If you have a value before the wildcard, it will match that first ( which will reduce the result set ) before doing a wildcard search. – Merioles Mar 04 '14 at 19:39
  • http://stackoverflow.com/a/9029354/243439 – ladieu Mar 04 '14 at 21:12

1 Answers1

0

The most basic answer to get you what you want is also using your conditions in the ORDER BY clause as follows:

SELECT
    *
FROM
    `tablename`
WHERE
    description LIKE '%man thinking%' OR
    description LIKE '%man%' OR
    description LIKE '%thinking%'
ORDER BY
    description LIKE '%man thinking%' DESC,
    description LIKE '%man%' DESC,
    description LIKE '%thinking' DESC

I'm not aware of the performance overhead for this kind of query though.

Merioles
  • 268
  • 2
  • 12