1

I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. Take a look on the example what I exactly want to do is

I have a main table name tbl_main in SQL

AS_ID   KWD
1   Man,Businessman,Business,Office,confidence,arms crossed
2   Man,Businessman,Business,Office,laptop,corridor,waiting
3   man,business,mobile phone,mobile,phone
4   Welcome,Greeting,beautiful,bride,celebration,wedding,woman,happiness
5   beautiful,bride,wedding,woman,happiness,mobile phone,talking
6   woman,girl,Digital Tablet,working,sitting,online
7   woman,girl,Digital Tablet,working,smiling,happiness,hand on chin

If search text is = Man,Businessman then result AS_ID is =1,2

If search text is = Man,-Businessman then result AS_ID is =3

If search text is = woman,girl,-Working then result AS_ID is =4,5

What is the best why to do this, Help is much appreciated.Thanks in advance

Ionic
  • 3,884
  • 1
  • 12
  • 33
Gitz
  • 810
  • 1
  • 17
  • 48

1 Answers1

1

Well just to mention. This isn't a good data structure at all. You should split all categories into separate rows which will improve your database in speed and even in the size.

Here an example how I would do this.

Table AS:

AS_ID (PK)
... your additional field...

Table Categories:

CAT_ID (PK)
CAT_NAME (example: Man)

Table AS_Cats

AS_ID (PK) (FK -> Table AS)
CAT_ID (PK) (FK -> Table Categories)

Table AS_Cats holds a combined primary key to avoid duplicate categories for one AS.

If you still really want this data structure, you can take a look at the code for splitting strings into rows. This way you can JOIN and filter for the AS_ID which holds all keys. See an example here.

Community
  • 1
  • 1
Ionic
  • 3,884
  • 1
  • 12
  • 33
  • I already know about the data structure sir but it's an old data I can not replace it :( millions of rows added into the table already. @lonic – Gitz Jun 20 '15 at 07:32
  • Well it can be replaced. Just due to an refactoring process. ;-) But well I've also give you an answer for the fact that you need to stuck on this solution. See the link above. – Ionic Jun 20 '15 at 07:34