1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Coding help with Insert Statement

Discussion in 'C#' started by MALHYP, Jun 5, 2006.

  1. #1
    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.
    SEMrush
    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.
     
    MALHYP, Jun 5, 2006 IP
    SEMrush
  2. jaymcc

    jaymcc Peon

    Messages:
    139
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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
     
    jaymcc, Jun 6, 2006 IP
  3. johngalt192

    johngalt192 Guest

    Messages:
    43
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    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.
     
    johngalt192, Jun 6, 2006 IP
  4. Postingpays

    Postingpays Well-Known Member

    Messages:
    1,072
    Likes Received:
    32
    Best Answers:
    0
    Trophy Points:
    185
    #4
    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.
     
    Postingpays, Aug 15, 2006 IP