Member Avatar for hwoarang69

I need some help with creating a simple search engine for website. Basic idea is that user will enter a string in search bar, which will compare in database key_word and get the reuslts.

Lets say I have the following table on sql server database.

   |----|----------|----------------------|
    | ID | URL      | key_word             |
    |----|----------|----------------------|
    | 1  | url1.com | cat short red NYC    |
    | 2  | url2.com | tall blue LA         |
    | 3  | url3.com | skinny NYC green     |
    | 4  | url4.com | cat black get        |
    |----|----------|----------------------|

Now in search bar, lets say user want to search the below string "get red cat from NYC". I want to search this in database 'key_word'.

       String key = "get red cat from NYC"
What I have tried:

So far I have the following below query to search from database. This is good for if user want to search for only one word. but the string 'key' will not work here and it will return 0 result. I need some idea so I can make this better query.

        SELECT * FROM [SearchTable] WHERE [key_Word] LIKE % key %;
What I want:

I want to change this sql server query so that it return ID=1,3,4.

So in other words. I want to take this string:

    String key = "get red cat from NYC"

and first search in database the word "get". it doesn't show up so go to next word. Next word is "red", this shows up in ID=1. next word is "cat", this shows up in ID=1,4. Next word is "from", this doesn't show up in any rows. Next word is "NYC", this shows up in ID=1,3.

put all id's together and you get ID's=1,1,4,1,3.

than I want to sort it so that ID=1 shows up at top and ID=3,4 can be at button since they are tied.

I was hoping to do this by only one sql query, bc if I keep connecting to database than the speed will go down tooo. So I was think of some sql server funcctions?

  • 3 Contributors
  • 2 Replies
  • 1K Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for hericles

You can do one search but you'll need to add in WHERE clauses, one for each word in the search string. E.g.
SELECT * FROM [SearchTable] WHERE [key_Word] LIKE '%get%' OR [key_Word] LIKE '%red%' OR [key_Word] LIKE '%cat%';
That can get reasonably big when searching for a lot of words of course.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.