Oh... sorry i didn't mention how i
somehow solve the problem
and i also didn't make the keywords separated by delimiter..
id keywords
1 Computer Sales
2 Computer Service etc..
let me try to explain this more in details,
when a user search for an item, the results will not be retrieved from the business table, instead from the keywords table [
tbl_keyword (from now)] which will contain the business name and other words that might match the nature of business..., e.g .. the content of tbl_keyword : S.D Computers (which is the business name) , Computer Sales, Computer Service, Computer Accessories, etc. and any other words ... (which will be entered by the business, and please note that choosing the keywords depends on them, which words a user
may use to search for a business) ..
So, a user (if the business name is known) would enter S.D Computer and search the database (the tbl_keyword may also SD Computer in case a user might not type the
dot(.) or dot may be replace with php) so the results would come out perfectly...
And when a business name is not known , a user might use Computer Service / Computer Accessories or other words, to search a business, which will again be retrieved from the tbl_keyword and display the name of the business by joining the three tables..
It can even search for multiple words if the words are separated by comma in the search form e.g. computer, books, comic books etc.. and will also display the result which has the most match using the search words. like in the example ... the business name of books would come before the computers ...coz it has more words matched.. (i hope i had explained it very well) .
here is the sql code
if the search words is found in the tbl_keyword
SELECT b.*, count(b.id) AS wordCount".
" FROM tbl_business b".
" INNER JOIN tbl_business_keyword bk ON b.id = bk.businessid".
" INNER JOIN tbl_keyword k ON bk.keywordid = k.id".
" WHERE k.keywords in (".implode(', ', $wordsToSearch).")".
" GROUP BY b.id, b.name".
" ORDER BY wordCount DESC";
}else{
if the words does not match any of the content of tbl_keyword.keywords then i used the php
similar_text function to search for a similar words from tbl_keyword.keywords (in which the accuracy can also be changed)..
this is how my code is right now... but then i don't know if i have missed anything yet ... so if you find anything missing please do point it out for me...
QeeR...