ASP.NET search terms to database and listing most popular

Jul 09 2007

ASP.NET search terms to database and listing most popular

Logging search terms your visitors are using at your site can be valuable for you as a webmaster, first of all just knowing what they are looking for at your site but also it can give you a hint if some of your content seems hard to reach by "regular" navigation making people always needing to search for it.

In this article I will give you rhe kind of search term logging mechanism I am using now here at ASPCode.net. We will use a single table for logging search terms and be able to extract both the top terms (most searched for) and latest terms.

1. Create a database table.

CREATE TABLE [dbo].[Search_Words] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[words] [varchar] (512) NULL ,
[cnt] [int] NULL ,
[lastsearch] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Search_Words] WITH NOCHECK ADD
CONSTRAINT [PK_Search_Words] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

CREATE INDEX [IX_Search_Words] ON [dbo].[Search_Words]([words]) ON [PRIMARY]
GO

2. Create some sp:s

create proc Search_WordAdd(@word varchar(512))
as
if (( select count(id) from search_words where words=@word) > 0 )
BEGIN
update search_words set cnt=cnt+1, lastsearch=getdate() where words=@word
END
ELSE
BEGIN
insert search_words select @word,1, getdate()
END

create proc Search_WordListLatest
as
select top 10 * from search_words order by lastsearch desc

create proc Search_WordListPopular
as
select top 10 * from search_words order by cnt desc

The download solution (Visual Studio 2005) is really simple when it comes to layout

You will need to change the static variable ConnectionString in datalayer.cs to point to your own database.

Lets see the code in default.aspx: When clicking on the "Search" button :



        protected void btnSearch_Click(object sender, EventArgs e)
        {
            //Log the search - i.e call Search_WordAdd
            DataLayer.Search_WordAdd(txtSearchFor.Text);
            Response.Redirect("default.aspx?word=" + txtSearchFor.Text, true);
        }

Here we log the search term to the database (and it will either add a new record or update the cnt column for an existing record). Also since the Search_WordAdd stored procedure sets the lastsearch datetime column to current timestamp it means it will be available as the latest search done.

Why do I Response.Redirect you might ask? Well, in a "real" website you will probaböy dop the search from any page (not the search result page) so a redirect to the search result page is what should be done in that case.

Listing latest and most popular search terms is done by simple repeaters, for example



    <h2>Most popular seaches</h2>    
    <asp:Repeater ID="repPopular" runat="server" OnItemDataBound="repPopular_ItemDataBound">
    <HeaderTemplate><ul></HeaderTemplate>
    <ItemTemplate><li><asp:HyperLink ID="hlLink" runat="server"></asp:HyperLink></li></ItemTemplate>
    <FooterTemplate></ul></FooterTemplate>
    </asp:Repeater>

and databound like this:



                repPopular.DataSource = DataLayer.Search_WordListPopular().Tables[0].Rows;
                repPopular.DataBind();

Just download the solution and have a go, it's really not hard at all to understand when you see all the code I'd say.

Attachments

SearchLogger.zip

ASPCode.net

Free asp.net and asp scripts

ASP.NET search terms to database and listing most popular

Attachments