Coding help with Insert Statement

Hey there, I have the following insert statement which enters the search results of people searching a database. The problem I have with it is that it creates an entry for each search. This causes long lists that are hard to sort through. This list can be as long as 500 lines long.

Example:

Spotted Gum, Furniture Timber, Vicotira
Spotted Gum, Furniture Timber, Vicotira
Spotted Gum, Furniture Timber, Victoria
Spotted Gum, Framing Timber, Victoria
Merbau, Framing Timber, Queensland.

Is it possible to change or add something to the insert statement so that if it finds a duplicate search result, it just inserts a (1), so that it increases the value.

End result would look like this.

Spotted Gum, Furniture Timber, Vicotira 3
Spotted Gum, Framing Timber, Victoria 1
Merbau, Framing Timber, Queensland 1

I have inserted an extra row in the database called "searched".

Meaning, "searched" 3 times.

If I have to I am willing to get rid of the username insert.

So the code below would need something added to it saying, insert ("Keyword"), ("CategoryTable"), ("Location"). If you find these results, just add a 1 in the "searched" column.

<%
Keyword = request.form("Keyword")
CategoryTable = request.form("CategoryTable")
Location = request.form("Location")
User = Session("MM_UserName")
%>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_connSeek_STRING
SQL = "INSERT INTO tblSearchResults ([result], [category], [location], [user]) " _
& " VALUES('" & Keyword & "', '" & CategoryTable & "', '" & Location & "', '" & User & "')"
conn.Execute SQL
%>

Please ask if you need more info.

Mally.

 

I would imagine you need something to work fast? To that end I would set the search phrase as a primary key on the table, so when you attempt to add it again it fails if already in the DB, then you trap this error and run an update statement to increment that record.

There are better ways of doing this, like doing a lookup prior to the insertion, however that ads an overhead that you can do without.

Hope this helps.

Jay

 

What dbms are you using? If you're using SQL Server you could use a stored procedure that would easily do what jaymcc describes.

for example:

IF NOT EXISTS (SELECT KEYWORD FROM tblSearchResults WHERE result=@Keyword)
INSERT INTO tblSearchResults ([result], [category], [location], [user], [searchcount]) VALUES (@Keyword, @Category, @Location, @User, 1)
ELSE
UPDATE tblSearchResults SET searchcount=searchcount+1 WHERE result=@Keyword

(An int field named searchcount would need to be added to track the number of searches)

I suspect you're probably not using SQL Server, but something similar can probably be done in most dbmses.

 

You can simply use a stored procedure and add up one more query not to add already exisitng record. Simply an .eof check will work for this condition.