0

I would love to search strings from database but i want it to order by relevance

I have picked up this example and is believed to work just fine.

Before search

  1. The quick brown dog jumped fox-like.
  2. The quick brown fox jumped over the lazy dog.
  3. The quick fox jumped over the Sears Tower.

After search

  1. The quick brown fox jumped over the lazy dog.
  2. The quick brown dog jumped fox-like.
  3. The quick fox jumped over the Sears Tower.

Code used

SELECT *
FROM `items`
WHERE `description` LIKE 'quick'
AND (
  `description` LIKE 'brown'
  OR `description` LIKE 'fox'
  OR `description` LIKE 'lazy'
  OR `description` LIKE 'dog'
)
LIMIT 0 , 30
ORDER BY (
  (
    CASE WHEN `description` LIKE 'brown'
    THEN 1
    ELSE 0
    END
  ) + (
    CASE WHEN `description` LIKE 'fox'
    THEN 1
    ELSE 0
    END
  ) + (
    CASE WHEN `description` LIKE 'lazy'
    THEN 1
    ELSE 0
    END
  ) + (
    CASE WHEN `description` LIKE 'dog'
    THEN 1
    ELSE 0
    END
  )
) DESC

My question is. When users do the search, the search terms are dynamic. How do i add a for each in between the order by For the search to work.

What i have tried but still i know it wont work. First I don't know how to implode the + sign 

$sql .= " ORDER BY ";

foreach ($words as $word) {
        $sql .= " ( CASE WHEN `pd.name` LIKE  '%".$word."%'
    THEN 1
    ELSE 0
    END) + "; 
       }
$sql .= " ASC";

This is an Opencart Search Query example.

The code below works but how do i make it dynamic

$sql .= " ORDER BY 
( CASE WHEN pd.name like '%".$word1."%' THEN 1 ELSE 0 END) 
+ 
( CASE WHEN pd.name like '%".$word2."%' THEN 1 ELSE 0 END) DESC";
Omari Victor Omosa
  • 2,814
  • 2
  • 24
  • 46
  • 2
    Not sure about the specifics of OpenCart - but it might be worth looking at a [Natural Language Search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html); that will require a `FULLTEXT` index though which may, or may not, be in place already. You *might* also be able to offload it onto something like [Apache Solr](http://lucene.apache.org/solr/). – CD001 Jul 17 '18 at 08:48
  • Is [Fulltext search](https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html) an option? – brombeer Jul 17 '18 at 08:48
  • @kerbholz Yes .Any findings i appreciate. – Omari Victor Omosa Jul 17 '18 at 08:50
  • @CD001 The solution doesn't have to be in relation to opencart. If i get a general SQL answer it will work for me – Omari Victor Omosa Jul 17 '18 at 08:55
  • @kerbholz correction. Fulltext search is not an option. the words are already split – Omari Victor Omosa Jul 17 '18 at 09:00
  • This one works but how do i make it dynamic `$sql .= " ORDER BY ( CASE WHEN pd.name like '%".$word1."%' THEN 1 ELSE 0 END) + ( CASE WHEN pd.name like '%".$word2."%' THEN 1 ELSE 0 END) DESC";` – Omari Victor Omosa Jul 17 '18 at 09:18
  • Unfortunately I don't know enough about the specifics of the OpenCart database to give a definitive answer; you could still use a `FULLTEXT` search by just gluing your search terms back together with `implode()` ... but if the table collation is case sensitive you may hit problems. – CD001 Jul 17 '18 at 09:18

0 Answers0