Really dumb Stored Proc Question. But I need help and Im lost.

Right now, I'm working on the database of a new project. The project is basically a keyword tracking and referral tracking website directory. My plan is to give the website some java code that will allow me to grab the http_referer and request_uri per page load. (everytime they get traffic)

Right now, I've got some tables I think will work fine. One is the storage table, where I'll store what the javascript grabs.

table.http_ref (columns will be)
uid (Unique ID)
http_referer
request_uri

Then I got the results page I want to parse the http_ref table into. For example.

Here we have a google results page which is a search for baby shoes. This would be the http_referer

http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
And if someone clicks on a link get this page, which let's say I'm tracking in my project.
http://www.thebabymarketplace.com/securestore/c54581.2.html
in my http_ref table I will have
uid
(whatever)
http_referer
http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
request_uri
http://www.thebabymarketplace.com/securestore/c54581.2.html
so I want a stored proc to grab the http_referer column and parse it for the refering search engine
http://www.google.com
and the key words
search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
Then grab the uri_request, which should be the web page visited,
and INSERT into another table where I will hold
keywords
referer
refered page
Any suggestions? I've been going nuts over this.

Then the key point is to parse the http_referer. You should gurantee that all http_referer strings are in a same format, for example the search string begins with '/search?' and the keywords start with '&q=' and locate the end of the string. Then you can use such procedure:

CREATE PROC sp_ParseHttpRef
AS
INSERT INTO Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM Tbl_SourceHttp
go

|||

Thank you very much.

So, can I take this proc and use it as a template to track other search engine referer strings?

For instance:

MSN

http://search.msn.com/results.aspx?q=baby+shoes&FORM=SSRE

or Yahoo

http://search.yahoo.com/search?p=baby+shoes&sm=Yahoo%21+Search&fr=FP-tab-web-t&toggle=1&cop=&ei=UTF-8

and create a Proc for each search engine I'm tracking?

Seems as if I would then have to Schedule the proc's to run, our just call them when I grab the http_referer and request_uri?

|||

Sure you can use the sample as a template, just keep in mind to parse the http_referer correctly. You do not need to schedule the proc to run, you can call it when you grab the http_refer and request_uri in your application, or you can make the proc as an insert trigger on the table which stores the http_refer and request_uri. For example:

create trigger trg_ParseHttp on Tbl_SourceHttp for insert
as
insert into Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM inserted
go