I thought I'd start the forum off with an example -
This script can be used to track how many times HTML messages were opened (until the feature is implemented into ListMail).
Instructions:
Copy and paste the code below into a new text file.
<?phpsession_start();/* to install databases, run the following query in PhpMyAdminCREATE TABLE `track` (`autoid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,`uid` TINYTEXT NOT NULL ,`msgid` TINYTEXT NOT NULL ,`stamp` TIMESTAMP NOT NULL);*/// CONFIG// admin password (for viewing stats)$apw = 'admin';// track database access info$t_host = 'localhost';$t_user = 'USER';$t_pass = 'PASS';$db = 'DATABASE';$table = 'track';// ListMail database access info$lm_host = 'localhost';$lm_user = 'USER';$lm_pass = 'PASS';$lm_db = 'DATABASE';// leave this$lm_table = 'lm_users';// END CONFIG// initialize vars + mysql// less warnings under windowserror_reporting (E_ALL ^ E_NOTICE);// get data$mid = $_GET['m'];$uid = $_GET['u'];$msg = $_GET['msg'];$admin = $_GET['admin'];$login = $_POST['login'];$logout = $_GET['logout'];$pw = $_POST['pw'];$reset = $_GET['reset'];$PHP_SELF = $_SERVER['PHP_SELF'];// magic quotes in post/get dataif(ini_get('magic_quotes_gpc')>0) $gpc = 1; else $gpc = '';if($gpc){ if($mid) $mid = stripslashes($mid); if($uid) $uid = stripslashes($uid); if($pw) $pw = stripslashes($pw); if($msg) $msg = stripslashes($msg);}$link = mysql_connect($t_host,$t_user,$t_pass);mysql_select_db($db,$link);$link2 = mysql_connect($lm_host,$lm_user,$lm_pass);// define headerfunction mainheader($title,$nohead=''){ echo "<html><head><title>$title</title><style type=text/css rel=stylesheet><!-- .bighead { font: bold 12pt helvetica; color: #000000; } .tbl1 { border: 1px solid #000000; padding: 0px 2px 1px 2px; } .head_bg1 { background: navy; border-bottom: 1px solid #000000; } } .head_txt1 { font: 9pt helvetica; color: white; } .head_txt2 { font: 8pt helvetica; color: #000000 } .txt1 { font: 9pt helvetica;} .txt2 { font: 8pt helvetica } .link1,.link1:visited { font: 9pt helvetica; color: #000000 } .link1:hover { text-decoration: underline; } .viewhead { background: #eeeeee } .button { border: 1px solid #000000; font: 8pt helvetica; width: 120px; background: #eeeeee; margin-top: 3px; } .txtbox { font: 8pt helvetica; border: 1px solid #000000; }}--></style></head><body>\n";if(!$nohead){ $width=' width=120'; echo "<table width=100% border=0 cellspacing=0 cellpadding=0><tr><td><span class=bighead>Message Views</span><br></td><td align=right><a class=link1 href=$PHP_SELF?logout=1>Logout</a><br></td></tr></table>\n";}echo "<table width=100% border=0 cellspacing=0 cellpadding=0>\n <tr>\n <td valign=top$width>\n";}function msgheader(){ echo "<table width=100% class=tbl1 border=0 cellspacing=0 cellpadding=0> <tr class=head_bg1> <td><span class=head_txt1>Messages</span><br></td> </tr> <tr> <td>\n";}function viewheader(){ global $db; global $link; global $msg; global $table; mysql_select_db($db,$link); $rows = mysql_query("select autoid from $table where msgid = '$msg' group by uid",$link); $nviews = @mysql_num_rows($rows); $rows = mysql_query("select autoid from $table where msgid = '$msg'",$link); $tviews = @mysql_num_rows($rows); echo "<table width=100% class=tbl1 border=0 cellspacing=0 cellpadding=0> <tr class=head_bg1> <td colspan=4> <table width=100% border=0 cellspacing=0 cellpadding=0> <tr> <td><span class=head_txt1>Views</span><br></td> <td align=right><span class=head_txt1>$tviews Total Views, $nviews Unique Users</span></td> </tr> </table> </td> </tr> <tr class=viewhead> <td>\n";}function viewfooter(){ echo "</table>\n";}function msgfooter(){ global $msg; echo " </td>\n </tr>\n </table>\n"; if($msg) echo "<input class=button type=button value=\"Reset This Message\" onclick=\"window.location='$PHP_SELF?reset=this&msg=$msg'\"><br>\n"; echo "<input class=button type=button value=\"Reset All Messages\" onclick=\"window.location='$PHP_SELF?reset=all'\"><br>";}function mainfooter(){ echo " </td>\n </tr>\n</table></body>\n</html>\n";}// login / logout / resetif($uid && $mid) $_SESSION['loggedin']='';if($logout){ mainheader('Logged out','nohead'); $_SESSION['loggedin']=''; echo "<span class=txt1>Logged out. <a class=link1 href=$PHP_SELF?admin=1>Re-login?</a><br></span>"; mainfooter(); exit; }if($login){ if($pw==$apw){ $_SESSION['loggedin']='123'; $admin=1; } else sleep(5);}if ($_SESSION['loggedin']=='123') $admin = 1;// begin main scriptif($reset=='all'){ @mysql_query("delete from $table where 1");}if($reset=='this'){ @mysql_query("delete from $table where msgid = '$msg'");}// track the hit if we're not adminif($_SESSION['loggedin']<>'123' && !$admin){ // only continue if msgid and uid from LM if($mid && $uid){ // track the hit $uid = addslashes($uid); $mid = addslashes($mid); mysql_query("insert into $table (uid,msgid) values('$uid','$mid')",$link); header("Content-type: image/gif"); readfile('./1.gif'); }} else { if($admin && $_SESSION['loggedin']=='123'){ // if admin show result pages mainheader('Tracked Messages'); $rows = mysql_query("select msgid from $table where 1 group by msgid order by msgid",$link); msgheader(); if(@mysql_num_rows($rows)>0){ while(list($mid)=mysql_fetch_row($rows)){ $mid = addslashes($mid); $row = mysql_query("select autoid from $table where msgid = '$mid'",$link); $nviews = @mysql_num_rows($row); $mid = stripslashes($mid); echo "<a class=link1 href=$PHP_SELF?msg=".urlencode($mid).">$mid</a> <span class=txt1>($nviews views)</span><br>"; } msgfooter(); if($msg){ echo " </td>\n <td width=10>&</td>\n <td valign=top>\n"; viewheader(); $rows = mysql_query("select autoid,uid,msgid,stamp from $table where msgid = '$msg'",$link); if(@mysql_num_rows($rows)>0){ echo "<span class=head_txt2>Name</span></td><td><span class=head_txt2>Email</span></td><td><span class=head_txt2>Date & Time</span></td> </tr>\n"; while(list($id,$uid,$mid,$stamp)=mysql_fetch_row($rows)){ $stamp = substr($stamp,0,4).'-'.substr($stamp,4,2).'-'.substr($stamp,6,2).' '.substr($stamp,8,2).':'.substr($stamp,10,2).':'.substr($stamp,12,2); // get user data from ListMail mysql_select_db($lm_db,$link2); $urow = mysql_query("select list,email,fname,lname from $lm_table where uid = '$uid'",$link2); mysql_select_db($lm_db,$link2); if(@mysql_num_rows($urow)>0){ list($list,$em,$fn,$ln)=mysql_fetch_row($urow); echo " <tr>\n <td><span class=txt1>$fn $ln</span><br></td>\n <td><span class=txt1>$em</span><br></td>\n <td><span class=txt1>$stamp</span><br> </td>\n </tr>\n"; } else { echo "<tr><td colspan=4><span class=txt1>User not found in ListMail</span><br></td></tr>"; } } } else { echo "<span class=txt1>No views for this message</span><br>\n </td> \n</tr>\n"; } viewfooter(); // <a class=link1 href=# } } else { echo "<span class=txt1>No tracked messages.</span><br>"; msgfooter(); } mainfooter(); } else { if($admin && $_SESSION['loggedin']<>'123'){ mainheader('Login','nohead'); echo "<form name=f1 method=post><input type=hidden name=login value=1><span class=txt1>Password: </span><input type=text class=txtbox name=pw><br><input type=button class=button value=\"Login\"></form><script language=\"javascript\"><!--f1.pw.focus();--></script>\n"; mainfooter(); } }}@mysql_close($link);?>
At the top of the file set a password and enter your MySQL access information.
Save the modified code as track.php and upload the file to your ListMail directory.
Create the database table by running the following query with PhpMyAdmin:
CREATE TABLE `track` (
`autoid` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`uid` TINYTEXT NOT NULL ,
`msgid` TINYTEXT NOT NULL ,
`stamp` TIMESTAMP NOT NULL
)
Set up a message code in ListMail, either Global or for the list(s) you want to use this on, named !uid. Make this code of the type "User’s Unique ID".
To track a message, enter the following into any HTML email:
<img src=./track.php?u=!uid&m=my_message>
Replace “my_message” with a descriptive title for the current message (no spaces or funny characters). This value is used in the tracking script to distinguish between emails.
To login and view tracked hits, browse to
http://yoursite.com/mail/track.php?admin=1 and enter your admin password.
I should have this in the program very soon..
