I have found it useful to occasionally import the link tracking statistics (number of hits) into Excel to look at trends over time. What you do with the info once it is in Excel is up to you, of course.
Dean helped me write a slick PHP script that could work directly with an Excel Web Query. (See the link in my signature below for info about Excel web queries).
Here is the script, with the file named
hits_wq.php. You should modify
thepassword (in the first line of "The Meat" section) to help provide a little security.
<?php
/* ========================================
Script for displaying hits from ListMailPro
(c) 2004 Vertex42 LLC
This file is placed in a directory called "Links", but you can put
it wherever you like, as long as you edit the $configfile and
$adminfile variables to point to the ListMail scripts.
The URL to access this script will be:
http://yourdomain.com/Links/hits_wq.php?p=thepassword
========================================== */
/* ====== STUFF TO EDIT MANUALLY ============= */
$configfile = "../ListMail/config.php";
$adminfile = "../ListMail/admin.php";
/* ====== THE MEAT ======== */
if ( $p == "thepassword" ) {
// continue
} else {
echo "<html><head></head><body>Cannot Access Hit Statistics</body></html>";
exit;
}
include($configfile); // grabs info to connect to database
include($adminfile); // grabs info to connect to database
$link = mysql_connect($sqlhost, $sqluser, $sqlpass) or die('can\'t connect to MySQL, did you provide the user/pass/db in the commandline?');
mysql_selectdb($sqldb) or die('can\'t select database, does it exist?');
echo " <table width=100% border=0 cellspacing=0 cellpadding=2>
<tr class=bg2>
<td>RefCode</td>
<td>Title</td>
<td>URL</td>
<td>Hits</td>
</tr>\n";
// sort by field
$ordby = "refcode";
$rows = mysql_query("select refcode,title,http,hits from $ktable where 1 order by $ordby");
while(list($ref,$title,$http,$hits)=mysql_fetch_row($rows)) {
echo "
<tr>
<td>$ref<br></td>
<td>$title<br></td>
<td><a href=\"$http\">$http</a><br></td>
<td>$hits<br></td>
</tr>\n";
}
echo " </table>";
?>
The .IQY file for the Excel web query is listed next. You will need to modify the URL to link to the above script. Just save this short bit of text as
filename.iqy and then open Excel and go to
Data > Get External Data > Run Saved Query ... and open your saved .iqy file.
In some cases, you can just double-click on the .iqy file and it will open up Excel and import the data automatically.
WEB
1
http://www.yourdomain.com/Links/hits_wq.php?p=thepassword
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
You ought to try this just to see how cool Excel web queries can be.
You could always just copy and paste into Excel, but that takes the fun out.
