For a search facility on my website I want to get a bunch of rows where any words match, then I want to go through the record set and assign relevance values according to which/how many fields match search terms.
At present I've done the assigning of relevance values with PHP, but am having trouble sorting the records. Here's a link to a php forum which will describe what I've done.
http://forums.devnetwork.net/viewto...p=171353#171353
Here's the Select I've got.
SELECT p.productID, p.prodCode, p.prodName, p.nPrice, p.soPrice, p.desc,
prod_subCats.relSubCatId, sub.subCatName, cat.catName, 0 AS 'ranking', 0
AS 'sortPrice' FROM tProducts p INNER JOIN prod_subCats ON p.productID =
prod_subCats.relProdId INNER JOIN subcategories sub ON
prod_subCats.relSubCatId = sub.subCatId INNER JOIN cat_subCat ON
cat_subCat.relSubCatId = sub.subCatId INNER JOIN categories cat ON
cat_subCat.relCatId = cat.catId WHERE stocklevel > -3
I've got the column 'ranking' for assigning relevance ranks. Then I've been going through the rows saying: if this field matches the search, that's 10 ranking points. If that one matches, thats another 5. etc.
Is there a way I can do it all in the query?