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.
The hitcount
table used in
Web Page Access Counting records only the
access count for each page registered in it. If you want to record
other information about page access, use a different approach. Suppose
that you want to track the client host and time of access for each
request. In this case, you need to log a row for each page access
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) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL, hits BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, t TIMESTAMP, host VARCHAR(255), INDEX (path,hits) ) ENGINE = MyISAM;
See Web Page Access Counting for notes
on choosing the character set and collation for the path
column.
To insert new rows into the hitlog
table, use this statement:
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 automatically in the TIMESTAMP
column t
when you insert new rows.
By linking the path
column to an AUTO_INCREMENT
column hits
, the counter values
for a given page path increment automatically whenever you insert
a new row for that path. The counters are maintained separately
for each distinct path
value.
This counting mechanism requires that you use the MyISAM (or BDB)
storage engine, which is why the table definition includes an
explicit ENGINE
=
MyISAM
clause. (For more information on
how multiple-column sequences work, see Using an AUTO_INCREMENT Column to Create Multiple
Sequences.)
There’s no need to check whether the counter for a page already exists, because you insert a new row each time you record a hit for a page, not just for the first hit.
To determine the current counter value for each page, select
the row for each distinct path
value that has the largest hits
value:
SELECT path, MAX(hits) FROM hitlog GROUP BY path;
To determine the counter for a given page, use this statement:
SELECT MAX(hits) FROM hitlog WHERE path = 'path_name
';
3.12.153.212