在PHP中需要有关优先搜索（按相关性排序）的帮助

So i picked up how to sort by priority but right now i'm having trouble with it.

My codes look like this :

$qry = "SELECT p.ProductID, p.ProductTitle, p.ProductImage, p.ProductDesc, p.Price, p.Quantity, p.Offered, p.OfferedPrice, p.OfferStartDate, p.OfferEndDate, NOW() AS 'Today' 
,(case when p.ProductTitle LIKE '%$SearchText%' then 1 else 0 end) +
(case when p.ProductDesc LIKE '%$SearchText%' then 1 else 0 end) +
(case when cp.CategoryID = $catID then 1 else 0 end) +
(case when p.Offered = $productStatus AND p.OfferStartDate < NOW() AND p.OfferEndDate > NOW() then 1 else 0 end) +
(case when p.Offered = $productStatus AND p.OfferStartDate IS NULL AND p.OfferedPrice IS NULL then 1 else 0 end) +
(case when p.Price > $priceMin AND p.Price < $priceMax then 1 else 0 end) +
(case when p.OfferedPrice > $priceMin AND p.OfferedPrice < $priceMax then 1 else 0 end) as priority
FROM Product p INNER JOIN CatProduct cp ON p.ProductID = cp.ProductID 
WHERE p.ProductTitle LIKE '%$SearchText%' OR p.ProductDesc LIKE '%$SearchText%' OR cp.CategoryID = $catID
OR ( (p.Price > $priceMin) AND (p.Price < $priceMax) ) OR ( (p.OfferedPrice > $priceMin) AND (p.OfferedPrice < $priceMax) ) ";

if($productStatus == '0')
{
    $qry .= "AND p.Offered = $productStatus AND p.OfferStartDate IS NULL AND p.OfferedPrice IS NULL";
}
else if($productStatus == '1')
{
    $qry .=" AND p.Offered =$productStatus AND p.OfferStartDate < NOW() AND p.OfferEndDate > NOW() ";
}

$qry .= " ORDER BY priority desc";

What i'm trying to accomplish here is to make a search function that would prioritize and display as the top few results based on relevance. Right now i'm having trouble sorting by the product status (on offer or not on offer). The "on offer" one works but i cant seem to get "not on offer" to work.

To determine a product to be on offer or not on offer, there is a p.Offered = 0 (not on offer) or p.Offered = 1 (on offer). But we also have a offer period so even if the product is suppose to be on offer, it still has to be within the offer period.

Any ideas how i can get it to work? I still want to keep it to display it and sort it based on relevance