July 13, 2011

Search functionality with pagination on a website

Question by acidpaul

I’m developing a search functionality with pagination in my site to search for the product name and the brand name. I’m using this query to get the user’s search request:

SELECT *
from products
WHERE name = 'optimum nutrition'
    OR brand = 'optimum nutrition' 
    OR name LIKE '%optimum%' 
    OR brand LIKE '%optimum%' 
    OR name LIKE '%nutrition%' 
    OR brand LIKE '%nutrition%'

I would like to display first the products that has the full ‘optimum nutrition’ in either the brand name and in the product name. How will I accomplish this?

Any suggestion would be greatly appreciated.

Answer by Shef

Try:

SELECT *, 
CASE WHEN (name = 'optimum nutrition' OR brand = 'optimum nutrition') THEN 1 ELSE 0 END AS full_match,
CASE WHEN (name LIKE '%optimum%' OR brand LIKE '%optimum%' OR name LIKE '%nutrition%' OR brand LIKE '%nutrition%') THEN 1 ELSE 0 END AS half_match
FROM products 
WHERE (name = 'optimum nutrition' OR brand = 'optimum nutrition')
OR (name LIKE '%optimum%' OR brand LIKE '%optimum%' OR name LIKE '%nutrition%' OR brand LIKE '%nutrition%')
ORDER BY full_match, half_match

Answer by Starx

I will suggest you look into
Zend Search Lucene, to put search functionality on your page.

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!