Revision [7913]

This is an old revision of AdvancedReferrersHandler made by DarTar on 2005-05-05 13:46:44.

 

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.  */
  21.  
  22. //constants
  23. define('RECORD_LIMIT_STEP', '1');
  24. define('SEARCH_DEFAULT_OPTION', '1');
  25. define('HITS_DEFAULT', '0');
  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']) && (strlen($_POST['h']) > 0))? $_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.  
  51. // restrict MySQL query by search string, time and number of hits
  52. $option = ($qo == '1')? 'LIKE' : 'NOT LIKE'; #set search string operator
  53. $hits_option = ($ho == '1')? '>' : '<'; #set hits operator
  54.  
  55. // build query from chunks
  56. //$query = 'WHERE ';
  57. //$query .= ($tag = trim($tag) ? 'page_tag = "'.mysql_real_escape_string($tag).'"' : '1'); #filter by tag
  58. $query .= (isset($_POST['q']))? ' AND referrer '.$option.' "%'.$q.'%"' : ''; #filter by string
  59. $query .= ' AND TO_DAYS(NOW()) - TO_DAYS(time) <= '.mysql_real_escape_string($days); #filter by time
  60. $query .= ' GROUP BY referrer ';
  61. $query .= ' HAVING num '.$hits_option.' '.mysql_real_escape_string($h); #filter by hits number
  62. $query .= ' ORDER BY num desc'; # set order
  63.  
  64. $IsAdmin = $this->IsAdmin();
  65. if ($global == 1)
  66. {
  67.     $title = "Sites linking to this wiki ".
  68.         ($days ? " (last ".($days == 1 ? "24 hours" : $days." days").")" : "")." (<a href=\"".$this->Href("referrers_sites", "", "global=1")."\">see list of domains</a>):";
  69.     $referrers = $this->LoadReferrers('', $query);
  70. }
  71. else
  72. {
  73.     $title = "External pages linking to ".$this->Link($this->GetPageTag()).
  74.         ($days ? " (last ".($days == 1 ? "24 hours" : $days." days").")" : "")." (<a href=\"".$this->Href("referrers_sites")."\">see list of domains</a>):";
  75.     $referrers = $this->LoadReferrers($this->GetPageTag(), $query);
  76. }
  77.  
  78. print("<strong>$title</strong><br />\n");
  79. print("<em>Note to spammers: This page is not indexed by search engines, so don't waste your time.</em><br />");
  80. //print 'Query: '.$query.'<br />'; #debug
  81. //print 'Global: '.$global; #debug
  82. //print links
  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','','post');
  94. $form .= '<table style="border: 1px solid #666; padding: 2pt; margin: 5pt">';
  95. $form .= '<tr><td><strong>Display referrers</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.  
  122. if ($this->GetUser()) {
  123.     if ($referrers)
  124.     {
  125.         print("<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n");
  126.         foreach ($referrers as $referrer)
  127.         {
  128.             print("<tr>");
  129.             print("<td width=\"30\" align=\"right\" valign=\"top\" style=\"padding-right: 10px\">".$referrer["num"]."</td>");
  130.             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>");
  131.             print("</tr>\n");
  132.         }
  133.         print("</table>\n");
  134.     }
  135.     else
  136.     {
  137.         print("<em>None</em><br />\n");
  138.     }
  139. } else {
  140.     print("<em>You need to login to see referring sites</em><br />\n");
  141. }
  142.  
  143. if ($global == 1)
  144. {
  145.     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>]");
  146. }
  147. else
  148. {
  149.     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>]");
  150. }
  151.  
  152.  
  153. ?>
  154. </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