SQL Page Counter

Created by . Last modified on 2003/08/05.

This is a fast and easy way to set up a page counter with a SQL backend. Page counters using the ZODB are not reccomended.

I used Postgres7, and PoPy. But any relational DB should work fine with a few adjustments.

First set up a database, and a database connection.
Then create your table. I called mine "countpages":


--- SQL METHOD "create_table" ---
CREATE TABLE countpages (
count INT4,
url VARCHAR(255)
)


Then, you'll need a few SQl methods:


--- SQL METHOD "test_for_page_count" ---
ARGUMENTS: url
----------------------------------------
SELECT count(count) FROM countpages
WHERE
url = <dtml-sqlvar url type=nb>



--- SQL METHOD "getPageViews" ---
ARGUMENTS: url
----------------------------------------
select * from countpages
WHERE
url = <dtml-sqlvar url type=nb>



--- SQL METHOD "update_page_count" ---
ARGUMENTS: url
----------------------------------------
UPDATE countpages
SET
count = count + 1
WHERE
url = <dtml-sqlvar url type=nb>



--- SQL METHOD "initialize_page_count" ---
ARGUMENTS: url
----------------------------------------
INSERT INTO countpages (
count, url
) VALUES (
1,
<dtml-sqlvar url type=nb>
)


Then, place this DTML in your standard_html_footer (or whereever you want the count to show up.


<dtml-comment>

### PAGE COUNT TOOLKIT ###

### Test to see if this page exists in the database
### If it does, update the count and display it.
### If not, create a record for it, and display that.

</dtml-comment>

<dtml-call "REQUEST.set('url', URL0)">
<dtml-in test_for_page_count>

<dtml-if "count>=1">
<dtml-call update_page_count>
<dtml-in getPageViews>
<center>
This page has been viewed <b><dtml-var count></b> times.
</center>
</dtml-in>
<dtml-else>
<dtml-call initialize_page_count>
<dtml-in getPageViews>
<center>
This page has been viewed <b><dtml-var count></b> time.
</center>
</dtml-in>
</dtml-if>
</dtml-in>
<BR><BR>

<dtml-comment>
### END PAGE COUNT TOOLKIT ###
</dtml-comment>