Match MySQL result from keywords

[QeeR]

i am working on a business listing site ...
there are 2 tables .. business table (which contains the name and address descriptions etc) and keywords table (which will contain several keywords to define the business and each keywords will be separated by comma (,) )..
Suppose a user search data where the business name is not known i.e. entering just computer maintenance or vehicles, how do i display results which match the keywords?
And how to query both tables when user enter the business name and keywords like Softtech Solution (business name) and computer maintenance (keyword) and also display a business which might have the same name and also display other business which has the same keywords.

Thanx in advance!!!

[krishnandu.sarkar]

You can match the keywords of table 2 from the description column of table 1 using

select * from table1 where description like '%keywords%'

[vickybat]

^^" description" column is in the 2nd table. Don't you think there should be a join in order to fetch records from 1st and 2nd table?

@ QeeR

I think you will have to define a primary and foreign key relationship between table 1 and 2 in order to perform a join and fetch records from table 2 in the context of table 1.

Let foreign key of table 1 is emp_id and primary key of table 2 is dept_id- or anything applicable as per the requirement

In your case, the query will be:

SELECT name, address, description

FROM table1,table2

WHERE emp_id=dept_id

AND description LIKE '%keywords%';

[krishnandu.sarkar]

Yeah, I just answered his query about how to match keywords, he need to join both the tables in order to get matching from both the tables.

[asingh]

Originally Posted by QeeR

i am working on a business listing site ...
there are 2 tables .. business table (which contains the name and address descriptions etc) and keywords table (which will contain several keywords to define the business and each keywords will be separated by comma (,) )..
Suppose a user search data where the business name is not known i.e. entering just computer maintenance or vehicles, how do i display results which match the keywords?
And how to query both tables when user enter the business name and keywords like Softtech Solution (business name) and computer maintenance (keyword) and also display a business which might have the same name and also display other business which has the same keywords.

Thanx in advance!!!

What is the common field in both the tables..?

[QeeR]

Originally Posted by asingh

What is the common field in both the tables..?

the keyword table contain a foreign key (business_id).... which is the id of the business in the business table... will this be good for querying or is there a better approach... thanks for the replies..

[vickybat]

^^ You got the answer yourself mate. Link the business_id and id in a primary -foreign key relationship and write the query like i mentioned before. I,m repeating the query again:

SELECT name, address, (required column of keywords table) AS "Business description"

FROM business,keyword // table name

WHERE business_id=id

AND description LIKE '%keywords%'; // mention exact names in order to match or else everything will be displayed

[asingh]

^^
I doubt that is also going to work. Cause it will only return exact cases where the two business ID's are equal in the table. Also there in no full inner join so how can two table be compared..?

@OP:

Can you list out the structures of both the tables. Like this.


Table A
Field1
Field2
Field3
Field4


Table B
Field1
Field2
Field3
Field4

Color the fields which can be joined in same color. And then tell us which fields are to be compared for pattern match. Are they from both the tables, or will they be user inputted and pulled from one table.

Also are you on SQL or MS Access.

[vickybat]

@ asingh


But that's what join is supposed to do right? Records will be displayed if there is a match between the ids's so that the keyword data from the second table can be pulled. If inner join is specified then only the exact match is pulled and that may be more than once.

If op wants to retrieve all the records from 2nd table even if no match exists, then he has to specify outer join right?

Correct me if i'm wrong buddy.

[asingh]

^^
If he puts in a full inner join, he should not get duplicates. I guess the OP has to tell us the exact structures, and how he wants records pulled. It is quite vague.

[vickybat]

^^ Got it buddy. Lets wait for OP to reply and give us the exact layout and structure.

[QeeR]

Originally Posted by asingh

^^
If he puts in a full inner join, he should not get duplicates. I guess the OP has to tell us the exact structures, and how he wants records pulled. It is quite vague.

Its MySQL ...

business table
businessid PK
name
address
and some other details

keyword table
id PK
businessid FK
keywords


this is how i created the dB .. maybe its not the best approach and please suggestions are appreciated ..

Actually its for searching the database .. a user may not know the name of the companies but by entering some keywords for searching, find companies that (might) match their business practice e.g user input : software development result : companies that develop software, user input : tickets result : who can issue tickets for travelling, bus, flight etc., ..

i hope i am making myself clear..

[QeeR]

ohh.... and also the keywords column contains data that are separated by commas (, ) and when the process of searching starts it will first look for the business table and if no exact business name is found it will search the keyword table for that might contains the term and if found get the businessid from keyword table and match it with the businessid from the business table .... this is what i am trying to achieved ...

And again i'm not sure whether creating queries like this will be good for searching database ...

[asingh]

Okay...let us get the logic clear first:

1. You have two distinct tables. Business Table (BT) and Keyword Table (KT).
2. A user inputs suppose "computers".
3. We search the BT field 'name' for "computers". If found it will pull out the fields from the respective PK and cross join it to the KT FK.
4. If not found in BT, it will search in KT field 'keywords'. If found it will again give the cross-join resultant of the PK on FK.
5. The 'keywords' in the KT are in long string format delimited by a ",".

If this is what you want, you will need to use stored procedures. Let me know if this is correct, can probably try to set it up for you..!

[QeeR]

Originally Posted by asingh

Okay...let us get the logic clear first:

1. You have two distinct tables. Business Table (BT) and Keyword Table (KT).
2. A user inputs suppose "computers".
3. We search the BT field 'name' for "computers". If found it will pull out the fields from the respective PK and cross join it to the KT FK.
4. If not found in BT, it will search in KT field 'keywords'. If found it will again give the cross-join resultant of the PK on FK.
5. The 'keywords' in the KT are in long string format delimited by a ",".

If this is what you want, you will need to use stored procedures. Let me know if this is correct, can probably try to set it up for you..!

YES!! Exactly .... I would really appreciate it ... thanks a lot...

[asingh]

^^
Okay, will give this a shot tomorrow at office, on my SQL server.

Till then others might chip in too...!

By the way:
Is this home work...?

[QeeR]

Originally Posted by asingh

^^
Okay, will give this a shot tomorrow at office, on my SQL server.

Till then others might chip in too...!

By the way:
Is this home work...?

Thank you very much for the help @asingh

this is not a home work ... Its like i'm looking for more knowledge and hope that this might come in handy sometimes in the future .. i usually create some projects on my own at home and while creating this small projects it helps me to learn more, and i could not find queries like this in books (that i have read so far) all i can i do is look for help in forums.. even though i googled it i could not find the right answer or maybe my serach terms were not accurate ... thanks a lot by the way...

[vickybat]

I think creating a cross join will lead to cartesian product of both tables. That would mean all the column values of table 1 will get match as many times as the values in table 2. I don't know if this is the requirement but i figure creating a normal (inner join) will do.

Yes executing queries for searching is time consuming. So creating a procedure is ideal and if a search is needed, simply the procedure is called.

CREATE PROCEDURE sp_business
AS
SELECT b.name,b.address,b.others,k.keywords

FROM business b , keyword k

WHERE b.business_id=k.business_id

AND k.keywords LIKE %abc%;

GO

You can call the stored procedure name by simply typing:

sp_business

Now running this will give you the matched names and addresses from first table with all matched keywords from second table. Using the LIKE keyword will allow you to filter the keywords and give you the exact match of keywords even after satisfying the join condition.

This is my understanding and may be wrong. Asingh is far more experienced in this matter so wait for his post.

@ asingh

This is what i could figure buddy. If wrong ,please give the correct solution.

[asingh]

^^
It is not that easy.

First the column 'name' has to be pattern matched. If you get a match, then you run the cross join.

IF....

There is no match, then you run the pattern match on the keywords column from the second table. If matched, again the cross joined product...!

It will be P-SQL here. Slightly complex. But do-able. Plus the SP will receive a search parameter too..!

[vickybat]

^^ You mean PL/SQL right? Allright buddy i'll wait for your reply.

It will be fun learning from my side as well.

[asingh]

Else if the queries are called from a program the logic can be broken into to stored procedures.

If the resultant of the first SP is null, then the second can be called from the program.

[QeeR]

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 ..

[asingh]

^^
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.

[QeeR]

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

[asingh]

^^
But how would it do the second option, like you wanted before, if the level 1 pattern match fails.

Can you post the SQL here.

[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...