Revision [7915]

This is an old revision of AdvancedReferrersHandler made by DarTar on 2005-05-05 14:41:19.

 

Advanced Referrers Handler


See also:
Documentation: AdvancedReferrersHandlerInfo.
This is the development page for an advanced referrers handler.
 



The referrer lists generated by WikkaWiki on high-traffic servers become easily unmanageable, due to their ever growing size. Of course you can limit the volume of the referrers by changing the referrer_purge_time in the ConfigurationOptions. To increase the legibility of the referrer list without purging the DB, I've modified the referrer handlers to allow searching and filtering the list.

Preview


Here's an example of how the new interface looks like:

External pages linking to HomePage (last 7 days) (see list of domains):
Note to spammers: This page is not indexed by search engines, so don't waste your time.

[View global referring sites | View global referrers | View referrer blacklist]

Display referrers string:
with than: hits
for the last days


The code


First you will need to modify the LoadReferrers() function in wikka.php:

original
  1.     function LoadReferrers($tag = "")
  2.     {
  3.         return $this->LoadAll("select referrer, count(referrer) as num from ".$this->config["table_prefix"]."referrers ".($tag = trim($tag) ? "where page_tag = '".mysql_real_escape_string($tag)."'" : "")." group by referrer order by num desc");
  4.     }


modified

  1.     function LoadReferrers($tag = '', $query)
  2.     {
  3.         return $this->LoadAll("SELECT referrer, COUNT(referrer) AS num FROM ".$this->config["table_prefix"]."referrers WHERE ".(($tag = trim($tag))? "page_tag = '".mysql_real_escape_string($tag)."'" : "1").$query);
  4.     }


Then, you'll have to replace the referrers.php and referrers_sites.php files (in the ./handlers/page/ folder) with the following:

referrers.php
  1. <div class="page">
  2. <?php
  3. /**
  4.  * Display, filter and search the referrer list for the current page
  5.  *
  6.  * Usage: append /referrers to the URL of the page
  7.  *
  8.  * This handler allows users to display, filter and search the referrer list for
  9.  * the current page and for the whole site. Current search criteria include strings,
  10.  * number of hits, reference period.
  11.  *
  12.  * @package         Handlers
  13.  * @subpackage        
  14.  * @name              referrers
  15.  *
  16.  * @author            {@link http://wikka.jsnx.com/DarTar Dario Taraborelli} - code cleanup, search/filter functionality added.  
  17.  * @version           0.1
  18.  * @since             Wikka 1.1.6.X
  19.  *                      
  20.  * @input       string  $q  optional: string used to filter the referrers;
  21.  *              default: '';
  22.  *              the default can be overridden by providing a POST parameter 'q'
  23.  * @input       integer $qo optional: determines the kind of search to be performed for string $q:
  24.  *              1: search for all referrers containing a given string
  25.  *              0: search for all referrers not containing a given string
  26.  *              default: 1;
  27.  *              the default can be overridden by providing a POST parameter 'qo'
  28.  * @input       integer  $h  optional: number of hits used to filter the referrers;
  29.  *              default: 1;
  30.  *              the default can be overridden by providing a POST parameter 'h'
  31.  * @input       integer $ho optional: determines the kind of filter to be applied to $h:
  32.  *              1: search for referrers with more than $h hits;
  33.  *              0: search for referrers with less than $h hits;
  34.  *              default: 1;
  35.  *              the default can be overridden by providing a POST parameter 'ho'
  36.  * @input       integer  $days  optional: number of days used to filter the referrers;
  37.  *              default: 1;
  38.  *              the default can be overridden by providing a POST parameter 'h'
  39.  */
  40.  
  41. //constants
  42. define('RECORD_LIMIT_STEP', '1');
  43. define('SEARCH_DEFAULT_OPTION', '1');
  44. define('HITS_DEFAULT', '0');
  45. define('HITS_DEFAULT_OPTION', '1');
  46. define('DAYS_DEFAULT', '7');
  47.  
  48. //initializing variables
  49. $q = ''; #search string
  50. $qo = ''; #search string option
  51. $h = ''; #hits number
  52. $ho = ''; #hits option
  53. $option = '';
  54. $hits_option = '';
  55. $n ='';
  56. $days = '';
  57. $max_days = $this->GetConfigValue('referrers_purge_time');
  58. $query = '';
  59. $global = '';
  60.  
  61. //getting POST parameters
  62. $q = (isset($_POST['q']))? $_POST['q'] : '';
  63. $qo = (isset($_POST['qo']))? $_POST['qo'] : SEARCH_DEFAULT_OPTION;
  64. $h = (isset($_POST['h']) && (strlen($_POST['h']) > 0))? $_POST['h'] : HITS_DEFAULT;
  65. $ho = (isset($_POST['ho']))? $_POST['ho'] : HITS_DEFAULT_OPTION;
  66. $days = (isset($_POST['days']))? $_POST['days'] : DAYS_DEFAULT;
  67. $global = (isset($_POST['global']))? $_POST['global'] : ((isset($_GET['global']))? $_GET['global'] : '');
  68.  
  69.  
  70. // restrict MySQL query by search string, time and number of hits
  71. $option = ($qo == '1')? 'LIKE' : 'NOT LIKE'; #set search string operator
  72. $hits_option = ($ho == '1')? '>' : '<'; #set hits operator
  73.  
  74. // build query from chunks
  75. //$query = 'WHERE ';
  76. //$query .= ($tag = trim($tag) ? 'page_tag = "'.mysql_real_escape_string($tag).'"' : '1'); #filter by tag
  77. $query .= (isset($_POST['q']))? ' AND referrer '.$option.' "%'.$q.'%"' : ''; #filter by string
  78. $query .= ' AND TO_DAYS(NOW()) - TO_DAYS(time) <= '.mysql_real_escape_string($days); #filter by time
  79. $query .= ' GROUP BY referrer ';
  80. $query .= ' HAVING num '.$hits_option.' '.mysql_real_escape_string($h); #filter by hits number
  81. $query .= ' ORDER BY num desc'; # set order
  82.  
  83. $IsAdmin = $this->IsAdmin();
  84. if ($global == 1)
  85. {
  86.     $title = "Sites linking to this wiki ".
  87.         ($days ? " (last ".($days == 1 ? "24 hours" : $days." days").")" : "")." (<a href=\"".$this->Href("referrers_sites", "", "global=1")."\">see list of domains</a>):";
  88.     $referrers = $this->LoadReferrers('', $query);
  89. }
  90. else
  91. {
  92.     $title = "External pages linking to ".$this->Link($this->GetPageTag()).
  93.         ($days ? " (last ".($days == 1 ? "24 hours" : $days." days").")" : "")." (<a href=\"".$this->Href("referrers_sites")."\">see list of domains</a>):";
  94.     $referrers = $this->LoadReferrers($this->GetPageTag(), $query);
  95. }
  96.  
  97. print("<strong>$title</strong><br />\n");
  98. print("<em>Note to spammers: This page is not indexed by search engines, so don't waste your time.</em><br />");
  99. //print 'Query: '.$query.'<br />'; #debug
  100. //print 'Global: '.$global; #debug
  101. //print links
  102. if ($global == 1)
  103. {
  104.     print("<br />[<a href=\"".$this->href("referrers_sites")."\">View referring sites for ".$this->GetPageTag()." only</a> | <a href=\"".$this->href("referrers")."\">View referrers for ".$this->GetPageTag()." only</a> | <a href=\"".$this->href("review_blacklist")."\">View referrer blacklist</a>]<br /><br />");
  105. }
  106. else
  107. {
  108.     print("<br />[<a href=\"".$this->href("referrers_sites", "", "global=1")."\">View global referring sites</a> | <a href=\"".$this->href("referrers", "", "global=1")."\">View global referrers</a> | <a href=\"".$this->href("review_blacklist")."\">View referrer blacklist</a>]<br /><br />");
  109. }
  110.  
  111. // build form
  112. $form = $this->FormOpen('referrers','','post');
  113. $form .= '<table style="border: 1px solid #666; padding: 2pt; margin: 5pt">';
  114. $form .= '<tr><td><strong>Display referrers</strong></td>';
  115. $form .= '<td><select name="qo" title="Select search option">';
  116. $form .= '<option value="1" '.(($qo == '1')? 'selected = "selected"' : '').'>containing</option>';
  117. $form .= '<option value="0" '.(($qo == '0')? 'selected = "selected"' : '').'>not containing</option>';
  118. $form .=  '</select> ';
  119. $form .= 'string: <input type ="text" name="q" title="Enter a search string" size="10" maxlength="50" value="'.$q.'"/></td><tr>';
  120. $form .= '<tr><td></td><td>with ';
  121. $form .= '<select name="ho" title="Select filter option">';
  122. $form .= '<option value="1" '.(($ho == '1')? 'selected = "selected"' : '').'>more</option>';
  123. $form .= '<option value="0" '.(($ho == '0')? 'selected = "selected"' : '').'>less</option>';
  124. $form .=  '</select> ';
  125. $form .= ' than: <input type ="text" name="h" title="Enter number of hits" size="10" maxlength="5" value="'.$h.'"/> hits</td></tr>';
  126. $form .= '<tr><td></td><td>for the last <select name="days" title="Select time interval">';
  127. // build drop-down menu
  128. for ($n = 1; $n < ($max_days+RECORD_LIMIT_STEP); $n+=RECORD_LIMIT_STEP) {
  129.     $selected = ($n == $days)? 'selected="selected"' : '';  
  130.     $form .= '<option value="'.$n.'" '.$selected.'>'.$n.'</option>';
  131. }
  132. $form .=  '</select> days</td></tr>';
  133. $form .= '<tr><td></td><td><input type="submit" value="Submit" title="Click to query the referrer list" accesskey="a" /></td></tr>';
  134. $form .= '</table>';
  135. $form .= '<input type="hidden" name="global" value="'.$global.'" />';
  136. $form .= $this->FormClose();
  137. echo $form;
  138. echo '<p>&nbsp;</p>';
  139.  
  140.  
  141. if ($this->GetUser()) {
  142.     if ($referrers)
  143.     {
  144.         print("<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n");
  145.         foreach ($referrers as $referrer)
  146.         {
  147.             print("<tr>");
  148.             print("<td width=\"30\" align=\"right\" valign=\"top\" style=\"padding-right: 10px\">".$referrer["num"]."</td>");
  149.             print("<td valign=\"top\"><a href=\"".$this->htmlspecialchars_ent($referrer["referrer"])."\">".$this->htmlspecialchars_ent($referrer["referrer"])."</a> ".($IsAdmin ? "[<a href=\"".$this->href("delete_referrer", "", "spam_link=").$this->htmlspecialchars_ent($referrer["referrer"])."&redirect=".$this->GetMethod()."\">Blacklist</a>]" : "")."</td>");
  150.             print("</tr>\n");
  151.         }
  152.         print("</table>\n");
  153.     }
  154.     else
  155.     {
  156.         print("<em>None</em><br />\n");
  157.     }
  158. } else {
  159.     print("<em>You need to login to see referring sites</em><br />\n");
  160. }
  161.  
  162. if ($global == 1)
  163. {
  164.     print("<br />[<a href=\"".$this->href("referrers_sites")."\">View referring sites for ".$this->GetPageTag()." only</a> | <a href=\"".$this->href("referrers")."\">View referrers for ".$this->GetPageTag()." only</a> | <a href=\"".$this->href("review_blacklist")."\">View referrer blacklist</a>]");
  165. }
  166. else
  167. {
  168.     print("<br />[<a href=\"".$this->href("referrers_sites", "", "global=1")."\">View global referring sites</a> | <a href=\"".$this->href("referrers", "", "global=1")."\">View global referrers</a> | <a href=\"".$this->href("review_blacklist")."\">View referrer blacklist</a>]");
  169. }
  170.  
  171.  
  172. ?>
  173. </div>



referrers_sites.php
  1. <div class="page">
  2. <?php
  3. /**
  4.  * Display, filter and search the domain list for the current page
  5.  *
  6.  * Usage: append /referrers_sites to the URL of the page
  7.  *
  8.  * This handler allows users to display, filter and search the referrer list for
  9.  * the current page and for the whole site. Current search criteria include strings,
  10.  * number of hits, reference period.
  11.  *
  12.  * @package         Handlers
  13.  * @subpackage        
  14.  * @name              referrers_sites
  15.  *
  16.  * @author            {@link http://wikka.jsnx.com/DarTar Dario Taraborelli} - code cleanup, search/filter functionality added.  
  17.  * @version           0.1
  18.  * @since             Wikka 1.1.6.X
  19.  *                      
  20.  */
  21.  
  22. //constants
  23. define('RECORD_LIMIT_STEP', '1');
  24. define('SEARCH_DEFAULT_OPTION', '1');
  25. define('HITS_DEFAULT', '1');
  26. define('HITS_DEFAULT_OPTION', '1');
  27. define('DAYS_DEFAULT', '7');
  28.  
  29. //initializing variables
  30. $q = ''; #search string
  31. $qo = ''; #search string option
  32. $h = ''; #hits number
  33. $ho = ''; #hits option
  34. $option = '';
  35. $hits_option = '';
  36. $n ='';
  37. $days = '';
  38. $max_days = $this->GetConfigValue('referrers_purge_time');
  39. $query = '';
  40. $global = '';
  41.  
  42. //getting POST parameters
  43. $q = (isset($_POST['q']))? $_POST['q'] : '';
  44. $qo = (isset($_POST['qo']))? $_POST['qo'] : SEARCH_DEFAULT_OPTION;
  45. $h = (isset($_POST['h']))? $_POST['h'] : HITS_DEFAULT;
  46. $ho = (isset($_POST['ho']))? $_POST['ho'] : HITS_DEFAULT_OPTION;
  47. $days = (isset($_POST['days']))? $_POST['days'] : DAYS_DEFAULT;
  48. $global = (isset($_POST['global']))? $_POST['global'] : ((isset($_GET['global']))? $_GET['global'] : '');
  49.  
  50. // restrict MySQL query by search string, time and number of hits
  51. $option = ($qo == '1')? 'LIKE' : 'NOT LIKE'; #set search string operator
  52. $hits_option = ($ho == '1')? '>=' : '<='; #set hits operator
  53.  
  54. // build query from chunks
  55. //$query = 'WHERE ';
  56. //$query .= ($tag = trim($tag) ? 'page_tag = "'.mysql_real_escape_string($tag).'"' : '1'); #filter by tag
  57. $query .= (isset($_POST['q']))? ' AND referrer '.$option.' "%'.$q.'%"' : ''; #filter by string
  58. $query .= ' AND TO_DAYS(NOW()) - TO_DAYS(time) <= '.mysql_real_escape_string($days); #filter by time
  59. $query .= ' GROUP BY referrer ';
  60. $query .= ' HAVING num '.$hits_option.' '.mysql_real_escape_string($h); #filter by hits number
  61. $query .= ' ORDER BY num desc'; # set order
  62.  
  63. $IsAdmin = $this->IsAdmin();
  64. if ($global == 1)
  65. {
  66.     $title = "Domains/sites linking to this wiki ".
  67.         ($days ? " (last ".($days == 1 ? "24 hours" : $days." days").")" : "")." (<a href=\"".$this->Href("referrers", "", "global=1")."\">see list of different URLs</a>):";
  68.     $referrers = $this->LoadReferrers('', $query);
  69. }
  70. else
  71. {
  72.     $title = "Domains/sites pages linking to ".$this->Link($this->GetPageTag()).
  73.         ($days ? " (last ".($days == 1 ? "24 hours" : $days." days").")" : "")." (<a href=\"".$this->Href("referrers")."\">see list of different URLs</a>):";
  74.     $referrers = $this->LoadReferrers($this->GetPageTag(), $query);
  75. }
  76.  
  77. print("<strong>$title</strong><br />\n");
  78. print("<em>Note to spammers: This page is not indexed by search engines, so don't waste your time.</em><br />");
  79. //print 'Query: '.$query.'<br />'; #debug
  80. //print 'Global: '.$global; #debug
  81. //print links
  82.  
  83. if ($global == 1)
  84. {
  85.     print("<br />[<a href=\"".$this->Href("referrers_sites")."\">View referring sites for ".$this->GetPageTag()." only</a> | <a href=\"".$this->Href("referrers")."\">View referrers for ".$this->GetPageTag()." only</a> | <a href=\"".$this->Href("review_blacklist")."\">View referrer blacklist</a>]<br /><br />");
  86. }
  87. else
  88. {
  89.     print("<br />[<a href=\"".$this->Href("referrers_sites", "", "global=1")."\">View global referring sites</a> | <a href=\"".$this->Href("referrers", "", "global=1")."\">View global referrers</a> | <a href=\"".$this->Href("review_blacklist")."\">View referrer blacklist</a>]<br /><br />");
  90. }
  91.  
  92. // build form
  93. $form = $this->FormOpen('referrers_sites','','post');  
  94. $form .= '<table style="border: 1px solid #666; padding: 2pt; margin: 5pt">';
  95. $form .= '<tr><td><strong>Display domains</strong></td>';
  96. $form .= '<td><select name="qo" title="Select search option">';
  97. $form .= '<option value="1" '.(($qo == '1')? 'selected = "selected"' : '').'>containing</option>';
  98. $form .= '<option value="0" '.(($qo == '0')? 'selected = "selected"' : '').'>not containing</option>';
  99. $form .=  '</select> ';
  100. $form .= 'string: <input type ="text" name="q" title="Enter a search string" size="10" maxlength="50" value="'.$q.'"/></td><tr>';
  101. $form .= '<tr><td></td><td>with ';
  102. $form .= '<select name="ho" title="Select filter option">';
  103. $form .= '<option value="1" '.(($ho == '1')? 'selected = "selected"' : '').'>more</option>';
  104. $form .= '<option value="0" '.(($ho == '0')? 'selected = "selected"' : '').'>less</option>';
  105. $form .=  '</select> ';
  106. $form .= ' than: <input type ="text" name="h" title="Enter number of hits" size="10" maxlength="5" value="'.$h.'"/> hits</td></tr>';
  107. $form .= '<tr><td></td><td>for the last <select name="days" title="Select time interval">';
  108. // build drop-down menu
  109. for ($n = 1; $n < ($max_days+RECORD_LIMIT_STEP); $n+=RECORD_LIMIT_STEP) {
  110.     $selected = ($n == $days)? 'selected="selected"' : '';  
  111.     $form .= '<option value="'.$n.'" '.$selected.'>'.$n.'</option>';
  112. }
  113. $form .=  '</select> days</td></tr>';
  114. $form .= '<tr><td></td><td><input type="submit" value="Submit" title="Click to query the referrer list" accesskey="a" /></td></tr>';
  115. $form .= '</table>';
  116. $form .= '<input type="hidden" name="global" value="'.$global.'" />';
  117. $form .= $this->FormClose();
  118. echo $form;
  119. echo '<p>&nbsp;</p>';
  120.  
  121. if ($this->GetUser()) {
  122.     if ($referrers)
  123.     {
  124.         for ($a = 0; $a < count($referrers); $a++)
  125.         {
  126.             $temp_parse_url = parse_url($referrers[$a]["referrer"]);
  127.             $temp_parse_url = ($temp_parse_url["host"] != "") ? strtolower(preg_replace("/^www\./Ui", "", $temp_parse_url["host"])) : "unknown";
  128.  
  129.             if (isset($referrer_sites["$temp_parse_url"]))
  130.             {
  131.                 $referrer_sites["$temp_parse_url"] += $referrers[$a]["num"];
  132.             }
  133.             else
  134.             {
  135.                 $referrer_sites["$temp_parse_url"] = $referrers[$a]["num"];
  136.             }
  137.         }
  138.  
  139.         array_multisort($referrer_sites, SORT_DESC, SORT_NUMERIC);
  140.         reset($referrer_sites);
  141.  
  142.         print("<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n");
  143.         foreach ($referrer_sites as $site => $site_count)
  144.         {
  145.             print("<tr>");
  146.             print("<td width=\"30\" align=\"right\" valign=\"top\" style=\"padding-right: 10px\">$site_count</td>");
  147.             print("<td valign=\"top\">" . (($site != "unknown") ? "<a href=\"http://".$this->htmlspecialchars_ent($site)."\">".$this->htmlspecialchars_ent($site)."</a>" : $site) . "</a> ".($IsAdmin ? "[<a href=\"".$this->href("delete_referrer", "", "spam_site=").$this->htmlspecialchars_ent($site)."&redirect=".$this->GetMethod()."\">Blacklist</a>]" : "")."</td>");
  148.             print("</tr>\n");
  149.         }
  150.         print("</table>\n");
  151.     }
  152.     else
  153.     {
  154.         print("<em>None</em><br />\n");
  155.     }
  156. } else {
  157.     print("<em>You need to login to see referring sites</em><br />\n");
  158. }
  159.  
  160. if ($global == 1)
  161. {
  162.     print("<br />[<a href=\"".$this->Href("referrers_sites")."\">View referring sites for ".$this->GetPageTag()." only</a> | <a href=\"".$this->Href("referrers")."\">View referrers for ".$this->GetPageTag()." only</a> | <a href=\"".$this->Href("review_blacklist")."\">View referrer blacklist</a>]");
  163. }
  164. else
  165. {
  166.     print("<br />[<a href=\"".$this->Href("referrers_sites", "", "global=1")."\">View global referring sites</a> | <a href=\"".$this->Href("referrers", "", "global=1")."\">View global referrers</a> | <a href=\"".$this->Href("review_blacklist")."\">View referrer blacklist</a>]");
  167. }
  168.  
  169.  
  170. ?>
  171. </div>


CategoryDevelopment
There are 8 comments on this page. [Show comments]
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki