Web Page Access Logging
Problem
You want to know more about a page than just the number of times it’s been accessed, such as the time of access and the host from which the request originated.
Solution
Maintain a hit log rather than a simple counter.
Discussion
The hitcount
table records only the count for each
page registered in it. If you want to record other information about
page access, use a different approach. Suppose you want to track the
client host and time of
access for each request. In this case, you need a log for each page
rather than just a count. But you can still maintain the counts by
using a multiple-column index that combines the page path and an
AUTO_INCREMENT
sequence column:
CREATE TABLE hitlog ( path VARCHAR(255) BINARY NOT NULL, hits BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, t TIMESTAMP, host VARCHAR(64), PRIMARY KEY (path,hits) );
To insert new records, use this query:
INSERT INTO hitlog (path, host) VALUES(path_val
,host_val
);
For example, in a JSP page, hits can be logged like this:
<c:set var="host"> <%= request.getRemoteHost ( ) %> </c:set> <c:if test="${empty host}"> <c:set var="host"> <%= request.getRemoteAddr ( ) %> </c:set> </c:if> <c:if test="${empty host}"> <c:set var="host"> UNKNOWN </c:set> </c:if> <sql:update dataSource="${conn}"> INSERT INTO hitlog (path, host) VALUES(?,?) <sql:param><%= request.getRequestURI ( ) %></sql:param> <sql:param value="${host}" /> </sql:update>
The hitlog
table has the following useful
properties:
Access times are recorded ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.