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.
