Google Answers Logo
View Question
 
Q: collect stats based on category url...cookies + asp? ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: collect stats based on category url...cookies + asp?
Category: Computers > Programming
Asked by: binarian-ga
List Price: $40.00
Posted: 29 Sep 2003 07:31 PDT
Expires: 29 Oct 2003 06:31 PST
Question ID: 261179
i need to track some user stats but i gotta do it weird.
its a dynamic page... products.asp

now...the client wants to know stats for various CATEGORIES within
products.asp
for example the client wants to know...hits, unique vistors, and
referalls when people go to lets say

products.asp?id=93&cat=Some+Dumb+Category

so basically i need a simple page that would list 

1) the category name
2)stats for that category (hits, unique vistors, referrring urls)

how do i do this? i am assuming something with cookies and store info
in the databse. i need code for this.

Request for Question Clarification by joseleon-ga on 29 Sep 2003 07:53 PDT
Hello, binarian:
  Do you want us to modify your code or just give you some hints and
guidelines for you to do it?

Regards.

Clarification of Question by binarian-ga on 29 Sep 2003 09:04 PDT
i should be able to do it based on some good hints but i would like
the code.. how about we start with hints and go from there.

Request for Question Clarification by joseleon-ga on 29 Sep 2003 23:34 PDT
Hello, binarian:
  I requested such clarification because I can do what you want but in
PHP code, not in ASP, so I can give the hints, but not to fully answer
this question by providing the ASP code. Sorry.

Regards.

Clarification of Question by binarian-ga on 30 Sep 2003 09:21 PDT
i certainly would appreciate any hints. i dont know where to start.

Request for Question Clarification by joseleon-ga on 01 Oct 2003 01:29 PDT
Hello, binarian:
  
  Ok, in that case I will start pointing you what you have to do, I
can help you with the database design and how to extract the info from
the visitors.
  
As far as I know, you don't need cookies to track visitors, unless you
want to track more specific information, these are the steps you need
to follow:

-Create a database table, called, for example, stats

-The table structure must look similar to this:
 category_name
 hits
 unique_visitors

-Create another table, called, for example, referers
 category_name
 referer_url
 
-These tables will allow you to get:

 Hits for an specific category
 Unique visitors for an specific category
 A list of referers for each category
 
-After that, on the beginning of the product page, you must create a
routine that will:

 Get the category the user it's trying to view
 Add 1 to the hits field
 Extract the IP of the visitor to see if it's unique (more below)
 Get the referer and add it to the referers table (more below)
 
Roughly, this is the process, if you are interested on it, I will post
how to get the IP of the visitor, check if it's unique or not and get
the referer.

Regards.

Clarification of Question by binarian-ga on 01 Oct 2003 11:29 PDT
i like this approach... lets get further into how this routing might
actually be written....the code is what i really need.. anyone who
knows ASP might help with this ...

Clarification of Question by binarian-ga on 01 Oct 2003 14:01 PDT
also can we figure out the actual DOMAIN NAME referal URL rather than
just the IP so in the admin view stats page u would just see a repeat
region with information like..except in a better format table of
course...

Category - CDs
Hits - 80
Uniques - 20
Referring URL - www.something.com

Request for Question Clarification by joseleon-ga on 02 Oct 2003 02:22 PDT
Hello, binarian:
  No problem on getting the referer, I will try to develop this system
in ASP, I know a bit of it, but I'm not proficient, this could be my
oportunity ;-)

I need you to tell me which database server are you using.

Regards.

Clarification of Question by binarian-ga on 03 Oct 2003 16:58 PDT
i am using a Windows 2000 Advanced Server with IIS and Microsoft Access 2000.

Request for Question Clarification by joseleon-ga on 04 Oct 2003 10:22 PDT
Hello, binarian:

  I have found a software that it's going to be useful as a base for
what you want, please, take a look:

http://www.ixtreme.com/ixtremestats/default.asp?title=ixtreme%20stats

I will modify it to fit your needs.

Regards.

Clarification of Question by binarian-ga on 04 Oct 2003 22:43 PDT
sounds good! i look forward to seeing it!
Answer  
Subject: Re: collect stats based on category url...cookies + asp?
Answered By: joseleon-ga on 06 Oct 2003 06:43 PDT
Rated:5 out of 5 stars
 
Hello, binarian:
  
  I have finished the script to store all the data you need, you can
download it here:
  
  http://www.xpde.com/stats.zip
  
  The zip contains three files:
  
  siteLog.mdb - Access Database to store the data
  products.asp - A test page to show you how to include the stats
  stats.asp - The stats page itself
  
To use it in your products page, you must include this line in the top
of the code:

<!--#include file="stats.asp"-->

This is the stats.asp file:

<%
Sub AddInfo
	Const adOpenForwardOnly = 0
	Const adOpenKeyset = 1
	Const adOpenDynamic = 2
	Const adOpenStatic = 3

	Const adLockReadOnly = 1
	Const adLockPessimistic = 2
	Const adLockOptimistic = 3
	Const adLockBatchOptimistic = 4		 

	Referer = Trim(Request.ServerVariables("HTTP_REFERER"))

	UserIP = Trim(Request.ServerVariables("REMOTE_HOST"))

	If Referer = "" Then
		Referer = "None"
	End If

	Set oSRS = Server.CreateObject("ADODB.RecordSet")
	
	strSConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
	& "c:\siteLog.mdb"
	
	Set objConn = CreateObject("ADODB.Connection")
	objConn.Mode = 2
	objConn.Open strSConn

	SQL = "INSERT INTO stats (h_category, h_ip, h_datetime, h_referer)
VALUES ('" & Trim(Request.QueryString("cat")) & "', '" &
Trim(Request.ServerVariables("REMOTE_HOST")) & "', '" & Date & " " &
Time & "', '" & Referer & "')"
	objConn.Execute SQL

	IF Err > 0 Then
		Response.Write Err.Number & " " & Err.Description
	End IF
 
End Sub

  AddInfo
  

%>

And to change where you will store your MDB, check this line:

	strSConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
	& "c:\siteLog.mdb"
	
And change c:\siteLog.mdb to the path where you are going to store the
MDB file on your webserver.
	
This script just stores the data, please, test it and once you are
happy with it I will design a basic page for you to see the gathered
data. As you can see this is an open answer, that means, request as
many clarifications you want until you get the desired results.

Regards.

Request for Answer Clarification by binarian-ga on 06 Oct 2003 11:52 PDT
well i tried it out but it didnt really do anything for me..probably
because u dont know the format usedin the products page. so i have put
the products page up here http://www.binarian.com/products.txt . hope
this helps in narrowing it down. i need to keep a record of the
category > visitors to that category > unique visitors to that
catetory > referrring url to that category.

Clarification of Answer by joseleon-ga on 06 Oct 2003 12:08 PDT
Hello, binarian:

  This answer it's divided in two parts, gather data and show data.
Today I have posted the part that gathers the data and tomorrow I will
post the part that shows that data. I will post here tomorrow a script
which will extract and show:

Category > Visitors
         > Unique visitors
         > Referers

As this is a development answer, we will need to run through a series
of clarification until the results fit your needs, don't worry.

Regards.

Request for Answer Clarification by binarian-ga on 06 Oct 2003 13:27 PDT
alright.. did u see the code that i posted before..cuz just putting
stats.asp in as include doesnt record data to the database. id like to
get that working.

Clarification of Answer by joseleon-ga on 06 Oct 2003 13:42 PDT
Hello, binarian:
  Just some questions:

-Have you uploaded all the files to your server?
-Have you changed the path to the MDB to fit your system?
-Do you get any error message?

Regards.

Request for Answer Clarification by binarian-ga on 06 Oct 2003 15:41 PDT
yes
yes
and no

Clarification of Answer by joseleon-ga on 07 Oct 2003 02:21 PDT
Hello, binarian:
  Ok, no problem, just send me the URL where your shop is to test the
stats script. Also, tell me which server is running.

Regards.

Clarification of Answer by joseleon-ga on 07 Oct 2003 13:05 PDT
Hello, binarian:
  Unfortunately, Google Answers TOS prevent us to contact privately
with customers, but you can do the following:

-Create a document with the private information I need, that is, the
URL to check the stats
-Don't include any contact information, we are not allowed to contact
customers outside Google Answers
-Visit this page
 
  http://www.xpde.com/upload.php

-Upload the document

I'm the only person who can see that document, don't worry. I know
it's a bit tricky, but we are not allowed to do it other way.

Regards.

Request for Answer Clarification by binarian-ga on 07 Oct 2003 14:21 PDT
ok it has been sent. so when u hit lets say the BIRTHDAYS category..
or the weddings category .. i would need tracking for THOSE
categories.. uniques, hits, referrals to thsoe categories.

thanks!

Clarification of Answer by joseleon-ga on 07 Oct 2003 14:36 PDT
Hello, binarian:
  Great, I have it, but according to the products.asp source you
published in this file:
  http://www.binarian.com/products.txt

The stats.asp script on your server can be accessed this way:

http://www.yourdomain.com/stats/stats.asp?id=100&Cat=Birthdays&SubCat=Birthday+Baskets&SubCatID=39

But I get a 404, can you post an URL to the stats.asp script? Of
course, use http://www.yourdomain.com instead the real one.

Regards.

Request for Answer Clarification by binarian-ga on 07 Oct 2003 15:31 PDT
check your upload directory again. i put it in there.

Clarification of Answer by joseleon-ga on 07 Oct 2003 23:27 PDT
Hello, binarian:
  This is what I get accessing the stats.asp script directly, it seems
the Request.QueryString it's not working correctly:

Microsoft JET Database Engine error '80004005'

Field 'stats.h_category' cannot be a zero-length string.

......./stats.asp, line 40

Is there any way I can access your server to upload the script and be
able to debug it?

Post any connection information using the upload page.

Regards.

Clarification of Answer by joseleon-ga on 08 Oct 2003 00:59 PDT
Hello, binarian:

  I know you can't gather stats yet, but here is the script to show them:

http://www.xpde.com/showstats.zip

Regards.

Request for Answer Clarification by binarian-ga on 08 Oct 2003 20:53 PDT
im sorry but i cant give u ftp. i hope this isnt too much of a
problem! talk me thru it if u can.

Clarification of Answer by joseleon-ga on 09 Oct 2003 00:12 PDT
Hello, binarian:

  The problem is that the script, in your server, it's not getting
properly the value of the category the user wants to view, so when I
build the SQL sentence to write the info into the database, there is
an error. That's why I need to debug the script in your server. This
is the fastest way, but if there is no possibility you give me FTP
access, I can set some debug points in the script and I can send it to
you so you can upload it. What do you think?

Regards.

Request for Answer Clarification by binarian-ga on 09 Oct 2003 06:42 PDT
lets try that first! thanks!

Clarification of Answer by joseleon-ga on 10 Oct 2003 09:44 PDT
Hello, binarian:

  Download the stats.asp script with debug info from here:

http://www.qadram.com/stats_debug.zip

Overwrite your existing one and tell me when it's ready so I can test it.

Regards.

Request for Answer Clarification by binarian-ga on 13 Oct 2003 18:09 PDT
the zip is not there for some reason?

Clarification of Answer by joseleon-ga on 13 Oct 2003 23:08 PDT
Hello, binarian:
  Please, try again, maybe the server was down in the time you tried.

Regards.

Request for Answer Clarification by binarian-ga on 14 Oct 2003 06:57 PDT
in hopes of wrapping this up once and for all..i have set up ftp for
you . chekc your text file note upload area to get username and
password. i did forget to mention that your root directory is "stats"

let me know if you get in ok.

Clarification of Answer by joseleon-ga on 14 Oct 2003 08:14 PDT
Hello, binarian:

  I have been checking the script and it works ok, for example, if you
put this on your browser:

http://www.yourserver.com/1shoprock/stats/stats.asp?id=100&cat=Greg+Lake++Shop

Change yourserver by your real server, and you will get some debug
info about the process, like this:

Query String
id=100
cat=Greg Lake Shop
Query String 2
id=100&cat=Greg+Lake++Shop
INSERT INTO stats (h_category, h_ip, h_datetime, h_referer) VALUES
('Greg Lake Shop', '80.58.44.109', '10/14/2003 8:01:32 AM', 'None')

After that, if you check the siteLog.mdb, you will see that the record
has been inserted.

By the way, with the FTP account you have created for me, I cannot
access the directory where the default.asp script is stored to check
if it's including properly the stats script, where probably it's the
problem, because if you access this URL:

http://www.yourserver.com/1shoprock/default2.asp?id=93&cat=Keith+Emerson+Shop

Change yourserver by your real server, you don't get any debug info.
And also, with that FTP account I don't have write permissions to
store any file in the stats dir, so I can't do anything.

This is a simple problem I can fix in no time if you provide me proper
access, it's just a five minute task, so please, give me access and
you will have it running right now.

Regards.

Request for Answer Clarification by binarian-ga on 14 Oct 2003 09:39 PDT
hey i have given u access to the stats directory.. i have included
stats simpley as an include file in default2.asp.

Clarification of Answer by joseleon-ga on 14 Oct 2003 11:25 PDT
Hello, binarian:

Now I have write permissions, so you can check the current stats here:

 http://www.yourserver.com/1shoprock/stats/showstats.asp

But I need access to the default2.asp script, or at least, please,
post the code here, I think the path on the include that points to the
stats.asp it's not set correctly so it can't find the script, please,
post it here and I will fix it.

Regards.

Request for Answer Clarification by binarian-ga on 14 Oct 2003 13:29 PDT
<!--#include file="includes/header.asp"-->
<!--#include file="includes/db.asp"-->
<!--#include file="stats/stats.asp"-->
<%'THIS IS THE DEFAULT CART PAGE AND IT LISTS ACTIVE PRODUCT
CATEGORIES FROM THE DB

'KILL SESSION IF USER IS DONE
If Request.QueryString ("end") = 1 Then
	Session.Abandon 
End If

DIM rsSubCat, SQLSubCat,varSubCatURL

IF Request.QueryString("id")>"" THEN
		'CREATE A RECORDSET TO DISPLAY CATEGORIES
%>
<tr>
	<td colspan=2>
	<h3><font size="6"><%=Request.QueryString("Cat")%></font></h3>
	</td>
</tr>	
<%
'SQLSubCat = "SELECT * FROM subcategories WHERE status = 'YES' and
catid = "&Request.QueryString("id")&" ORDER BY SubCatDesc ASC;"
SQLSubCat = "SELECT * FROM categories, subcategories WHERE
categories.catid=subcategories.catid and subcategories.status = 'YES'
and subcategories.catid = "&Request.QueryString("id")&" ORDER BY
subcategories.sortorder, subcategories.SubCatDesc ASC;"
Set rsSubCat = Server.CreateObject ("ADODB.Recordset")

'OPEN THE RECORDS SET AND LOOP THROUGH THE RECORDS
rsSubCat.Open SQLSubCat, dbc

'IF WE FIND SUB CATS WRITE THEM
IF NOT rsSubCat.EOF THEN
%>
<tr>
	<td bgColor="#000080" valign="center" colspan="2">
	&nbsp;<font color="white"><b>Select A Product Sub Category</b></FONT>
	</td>
</tr>
<tr>
	<td colspan="2">&nbsp;
	
	</td>
</tr>
<tr>
	<td width=0 vAlign=top align=center>
	<IMG SRC="<%=rsSubCat("ImagePath")%>" align=absmiddle BORDER=0
HSPACE=0 VSPACE=0 ALT="<%=Request.QueryString("cat")%>">
	</td>
	<td width=100% align=LEFT vAlign=MIDDLE>
	<ul>
	<%
	WHILE NOT rsSubCat.EOF
	%>
	<b>
	<li>
	<%
	'GENERATE A URL AND ENCODE IT TO ACCOUNT FOR SPECIAL CHARS, SPACES,
ETC
	varSubCatURL="<a HREF=products.asp?id="
	varSubCatURL=varSubCatURL&Server.URLEncode(Request.QueryString("id"))
	varSubCatURL=varSubCatURL&"&Cat="
	varSubCatURL=varSubCatURL&Server.URLEncode(Request.QueryString("cat"))
	varSubCatURL=varSubCatURL&"&SubCat="
	varSubCatURL=varSubCatURL&Server.URLEncode(rsSubCat("SubCatDesc"))
	varSubCatURL=varSubCatURL&"&SubCatID="
	varSubCatURL=varSubCatURL&Server.URLEncode(rsSubCat("SubCatID"))
	varSubCatURL=varSubCatURL&">"
	varSubCatURL=varSubCatURL&rsSubCat("SubCatDesc")

	'WRITE THE HYPERLINKED CATEGORY TO THE PAGE
	Response.Write(varSubCatURL)
	%></a>
	</li>
	</b>
	<%
	'GO GET THE NEXT CATEGORY
	rsSubCat.MoveNext
	Wend
	%>
	</ul>
	</td>
</tr>
<%
'LET THEM KNOW THAT NO SUB CATS WERE FOUND
ELSE
%>
<TR>
	<TD colspan=2>
	<h3>Select A Product Sub Category</h3>
	</TD>
</TR>	
<TR>
	<TD colspan=2>    
	<FONT COLOR=RED>
	No Sub Categories Were Found.
	</FONT>
	</TD>
</TR>
	<%
	END IF
%>
<tr>
	
  <td colspan=2><br>
    Now with lower shipping and handling rates. Discounts will be
applied to multi-item orders.
	<br>
	Questions?  Comments?  Looking for something in particular?  <a
href="contact.asp"><b>Contact us</b></a>.
	</td>
</tr>
<!--#include file="includes/footer.asp"-->
<%	
	'CLOSE AND DESTROY THE RECORD SET 
	rsSubCat.Close
	Set rsSubCat = Nothing
	
	ELSE
	%>
<tr>
	<td colspan=2>
	<h3>Welcome to The <%=cDomain%> Store</h3>
	</td>
</tr>	
<tr>
	<td colspan=2>
	<TABLE WIDTH=85% align=center border=0 CELLPADDING=2>
	<%
		
	'CREATE A RECORDSET TO DISPLAY CATEGORIES
	DIM rs, SQL, rsCount, rsCountHalf, vCounter
	SQL = "SELECT * FROM categories WHERE status = 'YES' ORDER BY
SortOrder, CatDesc ASC;"
	Set rs = Server.CreateObject ("ADODB.Recordset")

	'OPEN THE RECORDS SET AND LOOP THROUGH THE RECORDS
	rs.Open SQL, dbc, adOpenKeyset, adLockReadOnly

	IF NOT rs.EOF THEN
	rsCount=rs.RecordCount
	vCounter=1

While Not rs.EOF
		
'GENERATE A URL AND ENCODE IT TO ACCOUNT FOR SPECIAL CHARS, SPACES,
ETC
	DIM varCatURL, varCatURLFull

'THIS BLOCK GENERATES OUR LEFT COLUMN

'THIS FIRST PART OF THE URL IS FOR USE WITH THE PICTURE
	varCatURL=""
	varCatURL="<a HREF=default2.asp?id="
	varCatURL=varCatURL&Server.URLEncode(rs("CatID"))
	varCatURL=varCatURL&"&cat="
	varCatURL=varCatURL&Server.URLEncode(rs("CatDesc"))
	varCatURL=varCatURL&">"
'THIS SECOND PART IS FOR THE TEXT DESCRIPTION	
	varCatURLFull=varCatURL&rs("CatDesc")
	varCatURLFull=varCatURLFull&"</A>"

'WRITE OUT THE CAT DESC AND IMAGE

	Response.Write("<TR>")	'BEGIN ROW

	Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=25>")	 'BEGIN LEFT
COLUMN
	Response.Write(varCatURL&"<IMG SRC="& rs("ImagePath")&" ALIGN=TOP
BORDER=0 HSPACE=4 VSPACE=0 HEIGHT=100
ALT="&server.URLEncode(rs("CatDesc"))&"></A>")
	Response.Write("</TD>")
	Response.Write("<TD ALIGN=LEFT VALIGN=TOP>")
	Response.Write("<B>"&varCatURLFull&"</B>&nbsp;&nbsp;")
	Response.Write("<br>")
	Response.Write(rs("CatLongDesc"))
	Response.Write("</TD>")	'END LEFT COLUMN
	
	rs.MoveNext	

IF NOT rs.EOF OR rs.BOF THEN

'DO THAT AGAIN FOR OUR RIGHT COLUMN

'THIS FIRST PART OF THE URL IS FOR USE WITH THE PICTURE
	varCatURL=""
	varCatURL="<a HREF=default2.asp?id="
	varCatURL=varCatURL&Server.URLEncode(rs("CatID"))
	varCatURL=varCatURL&"&cat="
	varCatURL=varCatURL&Server.URLEncode(rs("CatDesc"))
	varCatURL=varCatURL&">"
'THIS SECOND PART IS FOR THE TEXT DESCRIPTION	
	varCatURLFull=varCatURL&rs("CatDesc")
	varCatURLFull=varCatURLFull&"</A>"

	Response.Write("<TD ALIGN=CENTER VALIGN=TOP WIDTH=25>")	'BEGIN RIGHT
COLUMN
	Response.Write(varCatURL&"<IMG SRC="& rs("ImagePath")&" ALIGN=TOP
BORDER=0 HSPACE=4 VSPACE=0 HEIGHT=100
ALT="&server.URLEncode(rs("CatDesc"))&"></A>")
	Response.Write("</TD>")
	Response.Write("<TD ALIGN=LEFT VALIGN=TOP>")
	Response.Write("<B>"&varCatURLFull&"</B>&nbsp;&nbsp;")
		Response.Write("<br>")
	Response.Write(rs("CatLongDesc"))
	Response.Write("&nbsp;&nbsp;"&varCatURL&"</A>")
	Response.Write("</TD>")	'END LEFT COLUMN

	Response.Write("</TR>")	'END ROW

	rs.MoveNext	
		
'ADD A HORIZONTAL RULE
	Response.Write("<TR>")
	Response.Write("<TD COLSPAN=4>")
	Response.Write("<HR SIZE=1 NOSHADE COLOR=SILVER>")	
	Response.Write("</TD>")
	Response.Write("</TR>")

END IF

	WEND
%>
  <tr>
    <td colspan="4" align="center">Now with lower shipping and
handling rates. Discounts will be applied to multi-item orders.</td>
  </tr>
</TABLE>
		</td>
	</tr>	
<%ELSE%>
<tr>
	<td colspan=2>
	<br>
	<FONT COLOR=RED>No Active Categories Were Found.</FONT>
	</td>
</tr>
<%END IF%>
<!--#include file="includes/footer.asp"-->
<%
'CLOSE AND DESTROY THE RECORD SET AND THE DB CONNECTON
rs.Close
Set rs = Nothing

END IF

dbc.Close
Set dbc = Nothing
%>

Clarification of Answer by joseleon-ga on 14 Oct 2003 14:15 PDT
Hello, binarian:

  Now should work, it seems you were setting in the header option
explicits to hide errors and to force declare all the variables so I
had to declare all my vars on the stats script. Test the stats here:

http://www.yourserver.com/1shoprock/default2.asp?id=93&cat=Keith+Emerson+Shopaaa

And you will see all the debug info, also check the stats here:

http://www.yourserver.com/1shoprock/stats/showstats.asp

And you will see how are stored, when you want I remove all the debug
info.

Regards.

Clarification of Answer by joseleon-ga on 14 Oct 2003 14:44 PDT
Hello, binarian:
  Almost midnight here, and before get some rest I have removed any
debug info so you can start using it right now. Please, tell me if you
have any problem.

Regards.

Request for Answer Clarification by binarian-ga on 14 Oct 2003 19:27 PDT
how do i view the stats?

Clarification of Answer by joseleon-ga on 14 Oct 2003 23:30 PDT
Hello, binarian:

Check out this link:

http://www.yourserver.com/1shoprock/stats/showstats.asp 

Change yourserver.com by your server, I see that you have entered data ;-)

Regards.
binarian-ga rated this answer:5 out of 5 stars
very helpful and flexible. THANKS A LOT!

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy