Hi, does anyone know how to alter this code so the ips.asp page (code below) sorts the table "Stats" ORDER BY Date, Time? Or, possibly since the date is passed in a query string (as per below) then maybe only sort by TIME since the day has been established? Confusing.
Thanks for any help.
--Cindy
**************************************
<!--#include file="config.asp"-->
<%
'-------------------------------------------------------------
'StatCounteX 3.1
'
'File: config.asp
'Description: Graphical Report Generator
'Initiated by Kevin Yochum on Feb 5, 2001
'-------------------------------------------------------------
'You may use the code for any purpose
'But re-publishing is discouraged.
'See License.txt for additional information
'-------------------------------------------------------------
'Change Log:
'-------------------------------------------------------------
'# Feb 12, 2001 by Hakan Eskici
'Added missing </table> tags
'-------------------------------------------------------------
' Sub ListIps
'
' Usage:
' lYear - the numerical year (optional)
' lMonth - the numerical month (optional)
' lDay - the numerical day (optional)
' lHour - the numerical hour (optional)
'
Sub ListIps( lYear, lMonth, lDay, lHour )
sDataSource = ""
If Len(lHour) > 0 Then
sDataSource = "GroupIpsByHourAndDate"
Else
sDataSource = "GroupIpsByDate"
End If
sSQL = "SELECT * From " & sDataSource & " Where 1=1 "
If Len( lYear ) > 0 Then
sSQL = sSQL & "and DatePart(""yyyy"",[Date])=" & lYear & " "
End If
If Len( lMonth ) > 0 Then
sSQL = sSQL & "and DatePart( ""m"", [Date])= " & lMonth & " "
End If
If Len( lDay ) > 0 Then
sSQL = sSQL & "and DatePart( ""d"", [Date])= " & lDay & " "
End If
If Len( lHour ) > 0 Then
sSQL = sSQL & "and Hour= " & lHour & " "
End If
' Connect to the database
OpenDB sConnStats
rs.Open sSQL,,,adCmdTable
%>
<table border=0 cellspacing=1 cellpadding=2 bgcolor = "#ffe4b5">
<tr>
<td bgcolor="#ffe4b5" width=10>»</td>
<td class="smallerheader" bgcolor="#ffe4b5">Stats: Visitors on <%=GetDate%></td>
<td bgcolor="#ffe4b5" width=10></td>
</tr>
<%
do while not rs.eof
sLink = "<a href=""ips.asp?ip=" & rs("IP"
If Len( lYear ) > 0 Then
sLink = sLink & "&year=" & lYear
End If
If Len( lMonth ) > 0 Then
sLink = sLink & "&month=" & lMonth
End If
If Len( lDay ) > 0 Then
sLink = sLink & "&day=" & lDay
End If
If Len( lHour ) > 0 Then
sLink = sLink & "&hour=" & lHour
End If
sLink = sLink & """>" & rs("IP" & "</a>"
%>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=sLink%></td>
<td bgcolor="#fffaf0"></td>
</tr>
<%
rs.movenext
loop
%>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=rs.RecordCount%> visitors</td>
<td bgcolor="#fffaf0"></td>
</tr>
</table>
<%
conn.close
'set rs=nothing
'set conn=nothing
End Sub
'
' Sub ShowClickPath
'
' Usage:
' sIP - The IP to show the click path for.
' lYear - the numerical year (optional)
' lMonth - the numerical month (optional)
' lDay - the numerical day (optional)
' lHour - the numerical hour (optional)
'
Sub ShowClickPath( sIp, lYear, lMonth, lDay, lHour )
If Len( sIP ) = 0 Then
Response.Write( "Error: IP Address not provided for displaying a Click Path." )
Exit Sub
End If
' sSQL = "SELECT Stats.*, Refs.RefName, Paths.PathName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID WHERE (((Stats.IP)='"&sIp&"') AND ((Stats.Date)="&sDate&")"
sSQL = "SELECT Stats.Date, Stats.Time, Stats.IP, Paths.PathName, Refs.RefName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID Where Stats.IP='" & sIp & "'"
If Len( lYear ) > 0 Then
sSQL = sSQL & " and DatePart(""yyyy"", [Stats].[Date]) = " & lYear
End If
If Len( lMonth ) > 0 Then
sSQL = sSQL & " and DatePart(""m"", [Stats].[Date]) = " & lMonth
End If
If Len( lDay ) > 0 Then
sSQL = sSQL & " and DatePart(""d"", [Stats].[Date]) = " & lDay
End If
If Len( lHour ) > 0 Then
sSQL = sSQL & " and DatePart(""h"", [Stats].[Time]) = " & lHour
End If
' Connect to the database
OpenDB sConnStats
rs.Open sSQL,,,adCmdTable
sFieldName = ""
If bShowLinks And InStr( rs("RefName", " ) > 0 Then
sFieldName = "<a href=""" & rs("RefName" & """>" & rs("RefName" & "</a>"
Else
sFieldName = rs("RefName"
end if
%>
<table border=0 cellspacing=1 cellpadding=2 bgcolor = "#ffe4b5">
<tr>
<td bgcolor="#ffe4b5" width=10>»</td>
<td colspan=3 class="smallerheader" bgcolor="#ffe4b5">Stats: Click path for <%=sIp%> on <%=rs("Date"%></td>
<td bgcolor="#ffe4b5" width=10></td>
</tr>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=rs("Date"%></td>
<td bgcolor="#fffaf0"><%=rs("Time"%></td>
<td bgcolor="#fffaf0"><%=sFieldName%></td>
<td bgcolor="#fffaf0"></td>
</tr>
<%
' Calculate totals
do while not rs.eof
sFieldName = ""
If bShowLinks And InStr( rs("PathName", " ) > 0 Then
sFieldName = "<a href=""" & rs("PathName" & """>" & rs("PathName" & "</a>"
Else
sFieldName = rs("RefName"
end if
%>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=rs("Date"%></td>
<td bgcolor="#fffaf0"><%=rs("Time"%></td>
<td bgcolor="#fffaf0"><%=sFieldName%></td>
<td bgcolor="#fffaf0"></td>
</tr>
<%
rs.movenext
loop
%>
</table>
<%
conn.close
'set rs=nothing
'set conn=nothing
End Sub
Function GetDate
If Len(Request.QueryString("Month") > 0 Then
GetDate = MonthName(Request.QueryString("Month")
If Len(Request.QueryString("Day") > 0 Then
GetDate = GetDate & " " & Request.QueryString("Day"
End If
GetDate = GetDate & ", "
End If
If Len(Request.QueryString("Year") > 0 Then
GetDate = GetDate & Request.QueryString("Year"
End If
If Len(GetDate) = 0 Then
GetDate = "All Data"
End If
End Function
sIp = Request.QueryString( "IP" )
sYear = Request.QueryString( "Year" )
sMonth = Request.QueryString( "Month" )
sDay = Request.QueryString( "Day" )
sHour = Request.QueryString( "Hour" )
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>StatCounteX 3.1</title>
<link rel="stylesheet" type="text/css" href="scx.css">
</head>
<body>
<table border=0 width="100%">
<td class="title">
StatCounteX 3.1 Reports
</td>
<td class="smallertext" align=right>
<a href=" target="_blank">Report a Bug</a> |
<a href=" target="_blank">Recommend a feature</a> |
<a href=" target="_blank">Ask a question</a> |
<a href=" target="_blank">Submit a site</a>
</td>
</table>
<hr size="1" color="#C0C0C0" noshade>
» <a href="reports.asp">Reports</a> » <a href="reportpathy.asp">Yearly Report</a>
» <a href="reportpathm.asp?year=<%=sYear%>">Monthly Report</a>
» <a href="reportpathd.asp?year=<%=sYear%>&month=<%=sMonth%>">Daily Report</a>
<%
If Len( sIp ) > 0 Then
%>
» <a href="ips.asp?year=<%=sYear%>&month=<%=sMonth%>&day=<%=sDay%>">Visitors Report</a> » Click Path
<%
Else
%>
» Visitors Report
<%
End If
%>
<br><br>
<%
Response.Write( "<b>Displaying Data For " & GetDate & "</b><br><br>" )
If Len( sIp ) > 0 Then
ShowClickPath sIp, sYear, sMonth, sDay, sHour
Else
ListIps sYear, sMonth, sDay, sHour
End If
%>
<a href="<%= Request.ServerVariables("HTTP_REFERER" %>">Back to Previous Page</a>
<hr size="1" color="#C0C0C0" noshade>
<table border=0 width="100%">
<tr>
<td class="smallertext">
Visit <a href=" for more OpenSource VB and ASP Projects.
</td>
<td align="right">
<a href=" <img src="icostatcountex.gif" width=90 height=30 alt="StatCounteX" border="0">
</a>
</td>
</tr>
</table>
</body>
</html>
Thanks for any help.
--Cindy
**************************************
<!--#include file="config.asp"-->
<%
'-------------------------------------------------------------
'StatCounteX 3.1
'
'File: config.asp
'Description: Graphical Report Generator
'Initiated by Kevin Yochum on Feb 5, 2001
'-------------------------------------------------------------
'You may use the code for any purpose
'But re-publishing is discouraged.
'See License.txt for additional information
'-------------------------------------------------------------
'Change Log:
'-------------------------------------------------------------
'# Feb 12, 2001 by Hakan Eskici
'Added missing </table> tags
'-------------------------------------------------------------
' Sub ListIps
'
' Usage:
' lYear - the numerical year (optional)
' lMonth - the numerical month (optional)
' lDay - the numerical day (optional)
' lHour - the numerical hour (optional)
'
Sub ListIps( lYear, lMonth, lDay, lHour )
sDataSource = ""
If Len(lHour) > 0 Then
sDataSource = "GroupIpsByHourAndDate"
Else
sDataSource = "GroupIpsByDate"
End If
sSQL = "SELECT * From " & sDataSource & " Where 1=1 "
If Len( lYear ) > 0 Then
sSQL = sSQL & "and DatePart(""yyyy"",[Date])=" & lYear & " "
End If
If Len( lMonth ) > 0 Then
sSQL = sSQL & "and DatePart( ""m"", [Date])= " & lMonth & " "
End If
If Len( lDay ) > 0 Then
sSQL = sSQL & "and DatePart( ""d"", [Date])= " & lDay & " "
End If
If Len( lHour ) > 0 Then
sSQL = sSQL & "and Hour= " & lHour & " "
End If
' Connect to the database
OpenDB sConnStats
rs.Open sSQL,,,adCmdTable
%>
<table border=0 cellspacing=1 cellpadding=2 bgcolor = "#ffe4b5">
<tr>
<td bgcolor="#ffe4b5" width=10>»</td>
<td class="smallerheader" bgcolor="#ffe4b5">Stats: Visitors on <%=GetDate%></td>
<td bgcolor="#ffe4b5" width=10></td>
</tr>
<%
do while not rs.eof
sLink = "<a href=""ips.asp?ip=" & rs("IP"
If Len( lYear ) > 0 Then
sLink = sLink & "&year=" & lYear
End If
If Len( lMonth ) > 0 Then
sLink = sLink & "&month=" & lMonth
End If
If Len( lDay ) > 0 Then
sLink = sLink & "&day=" & lDay
End If
If Len( lHour ) > 0 Then
sLink = sLink & "&hour=" & lHour
End If
sLink = sLink & """>" & rs("IP" & "</a>"
%>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=sLink%></td>
<td bgcolor="#fffaf0"></td>
</tr>
<%
rs.movenext
loop
%>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=rs.RecordCount%> visitors</td>
<td bgcolor="#fffaf0"></td>
</tr>
</table>
<%
conn.close
'set rs=nothing
'set conn=nothing
End Sub
'
' Sub ShowClickPath
'
' Usage:
' sIP - The IP to show the click path for.
' lYear - the numerical year (optional)
' lMonth - the numerical month (optional)
' lDay - the numerical day (optional)
' lHour - the numerical hour (optional)
'
Sub ShowClickPath( sIp, lYear, lMonth, lDay, lHour )
If Len( sIP ) = 0 Then
Response.Write( "Error: IP Address not provided for displaying a Click Path." )
Exit Sub
End If
' sSQL = "SELECT Stats.*, Refs.RefName, Paths.PathName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID WHERE (((Stats.IP)='"&sIp&"') AND ((Stats.Date)="&sDate&")"
sSQL = "SELECT Stats.Date, Stats.Time, Stats.IP, Paths.PathName, Refs.RefName FROM Paths RIGHT JOIN (Refs RIGHT JOIN Stats ON Refs.RefID = Stats.RefID) ON Paths.PathID = Stats.PathID Where Stats.IP='" & sIp & "'"
If Len( lYear ) > 0 Then
sSQL = sSQL & " and DatePart(""yyyy"", [Stats].[Date]) = " & lYear
End If
If Len( lMonth ) > 0 Then
sSQL = sSQL & " and DatePart(""m"", [Stats].[Date]) = " & lMonth
End If
If Len( lDay ) > 0 Then
sSQL = sSQL & " and DatePart(""d"", [Stats].[Date]) = " & lDay
End If
If Len( lHour ) > 0 Then
sSQL = sSQL & " and DatePart(""h"", [Stats].[Time]) = " & lHour
End If
' Connect to the database
OpenDB sConnStats
rs.Open sSQL,,,adCmdTable
sFieldName = ""
If bShowLinks And InStr( rs("RefName", " ) > 0 Then
sFieldName = "<a href=""" & rs("RefName" & """>" & rs("RefName" & "</a>"
Else
sFieldName = rs("RefName"
end if
%>
<table border=0 cellspacing=1 cellpadding=2 bgcolor = "#ffe4b5">
<tr>
<td bgcolor="#ffe4b5" width=10>»</td>
<td colspan=3 class="smallerheader" bgcolor="#ffe4b5">Stats: Click path for <%=sIp%> on <%=rs("Date"%></td>
<td bgcolor="#ffe4b5" width=10></td>
</tr>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=rs("Date"%></td>
<td bgcolor="#fffaf0"><%=rs("Time"%></td>
<td bgcolor="#fffaf0"><%=sFieldName%></td>
<td bgcolor="#fffaf0"></td>
</tr>
<%
' Calculate totals
do while not rs.eof
sFieldName = ""
If bShowLinks And InStr( rs("PathName", " ) > 0 Then
sFieldName = "<a href=""" & rs("PathName" & """>" & rs("PathName" & "</a>"
Else
sFieldName = rs("RefName"
end if
%>
<tr>
<td bgcolor="#fffaf0"></td>
<td bgcolor="#fffaf0"><%=rs("Date"%></td>
<td bgcolor="#fffaf0"><%=rs("Time"%></td>
<td bgcolor="#fffaf0"><%=sFieldName%></td>
<td bgcolor="#fffaf0"></td>
</tr>
<%
rs.movenext
loop
%>
</table>
<%
conn.close
'set rs=nothing
'set conn=nothing
End Sub
Function GetDate
If Len(Request.QueryString("Month") > 0 Then
GetDate = MonthName(Request.QueryString("Month")
If Len(Request.QueryString("Day") > 0 Then
GetDate = GetDate & " " & Request.QueryString("Day"
End If
GetDate = GetDate & ", "
End If
If Len(Request.QueryString("Year") > 0 Then
GetDate = GetDate & Request.QueryString("Year"
End If
If Len(GetDate) = 0 Then
GetDate = "All Data"
End If
End Function
sIp = Request.QueryString( "IP" )
sYear = Request.QueryString( "Year" )
sMonth = Request.QueryString( "Month" )
sDay = Request.QueryString( "Day" )
sHour = Request.QueryString( "Hour" )
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>StatCounteX 3.1</title>
<link rel="stylesheet" type="text/css" href="scx.css">
</head>
<body>
<table border=0 width="100%">
<td class="title">
StatCounteX 3.1 Reports
</td>
<td class="smallertext" align=right>
<a href=" target="_blank">Report a Bug</a> |
<a href=" target="_blank">Recommend a feature</a> |
<a href=" target="_blank">Ask a question</a> |
<a href=" target="_blank">Submit a site</a>
</td>
</table>
<hr size="1" color="#C0C0C0" noshade>
» <a href="reports.asp">Reports</a> » <a href="reportpathy.asp">Yearly Report</a>
» <a href="reportpathm.asp?year=<%=sYear%>">Monthly Report</a>
» <a href="reportpathd.asp?year=<%=sYear%>&month=<%=sMonth%>">Daily Report</a>
<%
If Len( sIp ) > 0 Then
%>
» <a href="ips.asp?year=<%=sYear%>&month=<%=sMonth%>&day=<%=sDay%>">Visitors Report</a> » Click Path
<%
Else
%>
» Visitors Report
<%
End If
%>
<br><br>
<%
Response.Write( "<b>Displaying Data For " & GetDate & "</b><br><br>" )
If Len( sIp ) > 0 Then
ShowClickPath sIp, sYear, sMonth, sDay, sHour
Else
ListIps sYear, sMonth, sDay, sHour
End If
%>
<a href="<%= Request.ServerVariables("HTTP_REFERER" %>">Back to Previous Page</a>
<hr size="1" color="#C0C0C0" noshade>
<table border=0 width="100%">
<tr>
<td class="smallertext">
Visit <a href=" for more OpenSource VB and ASP Projects.
</td>
<td align="right">
<a href=" <img src="icostatcountex.gif" width=90 height=30 alt="StatCounteX" border="0">
</a>
</td>
</tr>
</table>
</body>
</html>