Match MySQL result from keywords
I'll also post my code and what i had done, as soon as i think that it had somehow met my requirements that i need and also would like a suggestion whether there's a better way to query it ... as i have not that much experience is MySQL.. to tell you the truth i dont even know what PL/SQL is ,.. I learned php mostly from the internet and bought a PHP cookbook .. and MySQL i had done only small queries like grabbing the result , editing... those simple stuffs .. some small projects that no one knows .. 
but this is my first real projects which i intend to launch on the internet ... Online Business Listing for a small states ...
So, i hope you will be able to stay fix with me whenever i have questions so that i may find a better answer ..
Regards
And i also hope I'll find a good answers from thinkdigit forum ... used to buy the mags too .... sorry used to ..
but this is my first real projects which i intend to launch on the internet ... Online Business Listing for a small states ... So, i hope you will be able to stay fix with me whenever i have questions so that i may find a better answer ..
Regards
And i also hope I'll find a good answers from thinkdigit forum ... used to buy the mags too .... sorry used to ..
asingh
Aspiring Novelist
^^
Cause the original design has it such, that there be two tables. Meaning the underlying data is received in that format. And the original requirement is that the first table is queried, and if no result found, then second table queried. If any match, then the cross join done.
Cause the original design has it such, that there be two tables. Meaning the underlying data is received in that format. And the original requirement is that the first table is queried, and if no result found, then second table queried. If any match, then the cross join done.
Its been a while since the last post in this thread but i have found a solution that match exactly what i need ... and wouldn't want to leave this post without an answer: 
the tables:
business table
businessid PK
name
address
and some other details ...
keyword table
id PK
keywords
also i have added another table
business_keyword
businessid PK
keywordsid PK
At first i thought of first querying the business table to search for any match business name but now i let the keyword table contain the business name and other keywords to match the business so that i would have one query to join the three tables and look in the keywords table to look for the search keywords using the MySQL IN command .....
Thanks to all who gave me ideas in solving this problem.
Regards,,, QeeR
the tables:
business table
businessid PK
name
address
and some other details ...
keyword table
id PK
keywords
also i have added another table
business_keyword
businessid PK
keywordsid PK
At first i thought of first querying the business table to search for any match business name but now i let the keyword table contain the business name and other keywords to match the business so that i would have one query to join the three tables and look in the keywords table to look for the search keywords using the MySQL IN command .....
Thanks to all who gave me ideas in solving this problem.
Regards,,, QeeR
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...
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...
Last edited: