Cached Search of Top Terms - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cached Search of Top Terms

Author

Topic

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 07:29:24

Hi, I have a search and I want to create a hyperlinked list of the top 5 search terms below it, what's the most efficient way to go about this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 07:33:27

SELECT TOP 5 Links
FROM MagicTable
order by SomeOtherColumn

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 07:42:22

Yes but what about inserting the search terms into the DB how do I insert a term while checking its not there and increment a count of how many times its been searched for.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 07:44:07

Insert into magictable (searchword, timeofuse)
vales(@searchword, getdate())

E 12°55'05.25"
N 56°04'39.16"

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 07:47:13

what does vales(@searchword, getdate()) do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 07:53:38

There was a typo. he meant:-
Insert into magictable (searchword, timeofuse)
values(@searchword, getdate())

values clause states you are directly inputing the values (rather than from another table or SP).@searchword will be the parameter through which you pass the search word to be inserted. getdate() is a date function whichreturns current datetime.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 07:57:21

Ok but how about incrementing a count of the number of times a word has been searched for or do I just put a row for every word in the table? and what does the @ symbol do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:04:12

I think you better should insert svery time a search is made.
Then you can make some statistics about how often certain words are used.

But if you don't care about a timeline, just add a counter

UPDATE MagicTable
SET TimesUsed = TimesUsed + 1
WHERE SearchWord = @SearchWord

IF @@ROWCOUNT = 0
INSERT MagicTable (SearchWord, TimesUsed) VALUES (@SearchWord, 1)

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-11 : 08:08:45

quote:
Originally posted by Cowboy

Ok but how about incrementing a count of the number of times a word has been searched for or do I just put a row for every word in the table? and what does the @ symbol do?

Keep a count field in db and increment it each time you search

May be like:-

IF (SELECT COUNT(*) FROM magictable WHERE field=@searchword)>0
BEGIN
Insert into magictable (searchword, timeofuse,searchcount)
values(@searchword, getdate(),1)
END
ELSE
BEGIN
SELECT * FROM magictable  
WHERE field=@searchword

UPDATE magictable 
SET searchcount=searchcount+1
WHERE field=@searchword
END

Also @ signifies its a parameter or a vraible.

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 08:12:32

Aha ok so you recommend a timeline? the only reason I was against it was the DB could get big but I think I will try it first. So what about those @ symbols? I have trying to find out on google but it doesn't accept them

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 08:13:48

And what about a double @@?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:30:47

You should read Books Online first of all!

@ is used to denote a variable or a parameter.
@@ is used to denote a system function.

E 12°55'05.25"
N 56°04'39.16"

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 08:35:01

Don't have time google usually provides the answer! is that for php, I am using VB and I just do an & varname & when I want to insert one.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:42:00

If you do so, you are exposed to SQL Injection, which means someone can erase your whole database with a careful choosen text in your searchword textbox.

What will you do if you "don't have" time?

E 12°55'05.25"
N 56°04'39.16"

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 08:44:59

But will they work with VB and ASP I haven't tried them before

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 08:57:52

Yes, they will work in VB and ASP.

But if you can get them to work?
My gut feeling is no, but that is not because I haven't seen the code, but because of your lack of interest.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-11 : 08:59:20

@ symbol is used to define variables at SQL Server level. They have nothing to do with your front-end technology - may it be PHP, VB or ASP.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-11 : 09:07:42

Cowboy -- What response would you get if you walked into a room of construction works and announced "I am going to build a house today, I just need one little bit of help: Does anyone know what a 'hammer' is?"

That is where you are at right now. You really need to sit down and get a book and learn the absolute basics of SQL Server before trying to even think about writing an application. Just as the construction workers could not "talk you through" building a house if you had no knowledge or experience on that topic.

You would get a much better response if instead you had asked the construction workers: "How would I go about attaching two pieces of wood together?" In other words, start simple, start with the basics.

Does this make sense?

- Jeff
http://weblogs.sqlteam.com/JeffS

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-03-11 : 09:07:47

quote:
Originally posted by Cowboy

Don't have time google usually provides the answer! is that for php, I am using VB and I just do an & varname & when I want to insert one.

You don't have time to learn, but you expect other people to spend their time spoon-feeding you information you could easily look up yourself?

e4 d5 xd5 Nf6

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-03-11 : 09:51:22

I doubt that is the attitude people have, when I answer forum posts on a subject I am knowledgeable on I don't have a problem with people who lack knowledge or just want to get it working, I said what I said with an exclamation mark implying a certain amount of sarcasm, obviously I wouldn't just use google for everything. Anyway thank you to the guys that answered my quetions I am very grateful and I think I will go and read some books not because of what the last two smart alec comments said but because I feel compelled to learn something I don't know.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-11 : 10:11:15

quote:
Originally posted by Cowboy

I doubt that is the attitude people have, when I answer forum posts on a subject I am knowledgeable on I don't have a problem with people who lack knowledge or just want to get it working, I said what I said with an exclamation mark implying a certain amount of sarcasm, obviously I wouldn't just use google for everything. Anyway thank you to the guys that answered my quetions I am very grateful and I think I will go and read some books not because of what the last two smart alec comments said but because I feel compelled to learn something I don't know.

They weren't smart alec comments, they were practical suggestions. You recieved an answer, but don't understand the context, syntax, or symbols. What did you expect them to suggest?

Poor planning on your part does not constitute an emergency on my part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources