User:Aerik/Intersections code
Appearance
This is my implementation of Category math also sometimes called category intersections as discussed here on meta and elsewhere. I have it running here on my wiki.
I think the basic concept and implementation are good (feedback welcome on that), and my SQL pretty clean, but I need to do quite a bit to clean up the code (particularly the "printintersections" function - lots of garbage still in there from trying something different). --Aerik 08:52, 5 May 2005 (UTC)
<?php /** * * @package MediaWiki * @subpackage SpecialPage */ # globals $catlist =""; $catexclude =""; $exclpageary =""; /** * */ function wfSpecialIntersections() { global $wgUser, $wgOut, $wgRequest, $catlist, $catexclude, $exclpageary; $catexclude = $wgRequest->getVal( 'catexclude' ); $catlist = $wgRequest->getVal( 'catlist' ); if ($catlist == "") $catlist = "Universe"; #set lists back how it was intended to maintain urls generated later $catlist = str_replace (" ","+",$catlist); $catexclude = str_replace (" ","+",$catexclude); # NOTICE $wgOut->addHTML("This is a draft special page implementing 'Category Intersections' and 'Category Math' as described on en:Village_Pump and variously on Meta. It's workign pretty well, but I'm still refining it, particularly the functioning of exclusions.<br /><br />\n"); #debugging $rdesc = "Results having categories: ".str_replace("+"," AND ",$catlist)."<br />"; if($catexclude) $rdesc .= "And not having categories: ".str_replace("+"," OR ", $catexclude)."<br />"; $wgOut->addHTML($rdesc."<br />\n"); $incatary = explode("+", $catlist); $intersectary = doSubmit(makequery("get_cats",$incatary)); if ($intersectary){ $wgOut->addHTML("Categories intersecting selection<br />"); printintersections($intersectary,"get_cats"); } else { $wgOut->addHTML("No category intersections for selected categories"); } $wgOut->addHTML("<br />\n"); $pageary = doSubmit(makequery("get_pages",$incatary)); if ($catexclude) { $excatary = explode("+", $catexclude); $exclpageary = doSubmit(makequery("get_exclude",$excatary)); } $resary = filterpageresults($pageary); if ($resary["subcats"]){ $wgOut->addHTML("Subcategories<br>\n"); printsubcats($resary["subcats"]); } else $wgOut->addHTML("No subcategories...<br>\n"); if ($resary["pages"]) { $wgOut->addHTML("Pages<br>\n"); printpageresults($resary["pages"]); } else $wgOut->addHTML("No pages at at this intersection...<br>\n"); # Output text for top of page - need to separate default category menu text from general text # $wgOut->addWikiText( wfMsg( "IntersectionsDefault" ) ); } /** * This was initially from AskSQL */ function doSubmit($query) { global $wgOut, $wgUser, $wgServer, $wgScript, $wgArticlePath, $wgLang, $wgContLang; global $wgDBserver, $wgDBuser, $wgDBpassword, $wgDBname, $wgSqlTimeout; # Use a limit, folks! $query = trim( $query ); if( preg_match( "/^SELECT/i", $query ) and !preg_match( "/LIMIT/i", $query ) ) { $query .= " LIMIT 100"; } $conn = Database::newFromParams( $wgDBserver, $wgDBuser, $wgDBpassword, $wgDBname ); # Start timer, will kill the DB thread in $wgSqlTimeout seconds $conn->startTimer( $wgSqlTimeout ); $res = $conn->query( $query, "SpecialIntersections::doSubmit" ); $conn->stopTimer(); if ( "" != $err ) { $wgOut->addHTML( "<p><font color='red' size='+1'>" . htmlspecialchars($err) . "</font>\n" ); } $n = 0; @$n = $conn->numRows( $res ); if ( $n ) { $a = array(); for ( $x = 0; $x < $n; ++$x ) { array_push( $a, $conn->fetchRow($res) ); } $conn->freeResult( $res ); return $a; } } function makequery($qrytype,$catary){ $catcount=0; $params = "("; while ($catary[$catcount]) { $params .= "((categorylinks.cl_to)='$catary[$catcount]')"; if ($catary[$catcount+1]){ $params .= " OR "; } $catcount++; } $params .= ")"; switch ($qrytype){ case "get_pages": $query ="SELECT cur.cur_title, Count(categorylinks.cl_from) AS numlinks, cur.cur_namespace FROM categorylinks LEFT JOIN cur ON categorylinks.cl_from = cur.cur_id WHERE (".$params." ) GROUP BY cur.cur_title HAVING (((Count(categorylinks.cl_from))=".$catcount.")) ORDER BY cur.cur_namespace, cur.cur_title"; return $query; case "get_cats": $query ="SELECT categorylinks_1.cl_to, Count(categorylinks_1.cl_from) AS numlinks FROM categorylinks INNER JOIN categorylinks AS categorylinks_1 ON categorylinks.cl_from = categorylinks_1.cl_from WHERE ".$params." GROUP BY categorylinks_1.cl_to HAVING (((Count(categorylinks_1.cl_from)) >=".$catcount.")) ORDER BY numlinks DESC"; return $query; case "get_exclude": $query ="SELECT cur.cur_title, Count(categorylinks.cl_from) AS numlinks, cur.cur_namespace FROM categorylinks LEFT JOIN cur ON categorylinks.cl_from = cur.cur_id WHERE (".$params." AND ((cur.cur_namespace)='0')) GROUP BY cur.cur_title ORDER BY cur.cur_namespace, cur.cur_title"; return $query; } } function printintersections($resAry){ global $wgOut, $wgRequest,$wgScript, $catlist, $catexclude; $r = "<table border=1 bordercolor=black cellspacing=0 cellpadding=2>\n"; $r .="<th>Category</th><th>Freq</th><th>Jump</th><th>New list</th><th>Include</th><th>Exclude</th>\n"; $rowcheck=""; foreach ($resAry as $row) { #check to see if this entry is already excluded $excheck = strpos($catexclude,$row[0]); $r .= "<tr>"; if ((($row[2] == "14") || ($row[2] == Null)) && $excheck === false) { $catinclink = ""; $catexlink = ""; $r .="<td>".str_replace("_"," ",$row[0])."</td>\n"; $r .= "<td>".$row[1]."</td>"; # link to jump to regular category page $title =& Title::makeTitle( "14", $row[0] ); $r .= "<td><a href=\"" . $title->escapeLocalUrl() . "\" class='internal' title=\"jump to category " .$row[0]."\">jump</a></td>\n"; # link to get a new list of intersections for the selected category $r .= "<td><a href=\"".$wgScript."?title=Special:Intersections&catlist=".$row[0] ."\" title=\"intersections for ".$row[0]." \" class='internal'>new</a></td>\n"; # see if category is already in intersections list $catcheck = strpos($catlist,$row[0]); # check if this is intersections results or subcategory results - skip if subcategory results if( $row[2] == Null) { # link to add this row to category list (inclusive) if($catcheck === false) { if($catexclude) $catexlink = "&catexclude=".$catexclude; $r .= "<td><a href=\"".$wgScript."?title=Special:Intersections&catlist=".$catlist ."+".$row[0].$catexlink."\" title=\"add ".$row[0]." to intersections list\" class='internal'>incl</a></td>\n"; # assume it's included in catlist and don't show "add" link } else { $r .= "<td>X</td>\n"; } # link to add this row to excluding category list $catinclink = "&catlist=".$catlist; if ($catexclude) { $newexclude = $catexclude."+".$row[0]; } else { $newexclude = $row[0]; } $r .= "<td><a href=\"".$wgScript."?title=Special:Intersections".$catinclink."&catexclude=".$newexclude ."\" title=\"add ".$row[0]." to exclusions list\" class='internal'>excl</a></td>\n"; } } $r .="</tr>\n"; $i++; } $r .= "</table>\n"; $wgOut->addHTML( $r); } function printpageresults($resAry){ global $wgOut, $wgRequest,$wgScript,$exclpageary; $r .= "<table border=1 bordercolor=black cellspacing=0 cellpadding=2>\n"; $r .="<th>Page</th>\n"; foreach ($resAry as $row) { if ($exclpageary) $excheck = checkexclusions($row[0]); else $excheck = false; if($excheck === false) { $r .= "<tr>"; $title =& Title::makeTitle( $row[2], $row[0] ); $pagelink = "<a href=\"" . $title->escapeLocalUrl() . "\" class='internal'>" .htmlspecialchars($row[0])."</a>" ; $r .= "<td>".$pagelink."</td>"; $r .="</tr>\n"; } } $r .= "</table>\n"; $wgOut->addHTML( $r."\n" ); } function printsubcats($resAry){ global $wgOut, $wgRequest,$wgScript, $catlist, $catexclude; $r = "<table border=1 bordercolor=black cellspacing=0 cellpadding=2>\n"; $r .="<th>Category</th><th>Freq</th><th>Jump</th><th>New list</th>\n"; $rowcheck=""; foreach ($resAry as $row) { #check to see if this entry is already excluded $excheck = strpos($catexclude,$row[0]); if ($excheck === false) { $r .= "<tr>"; $r .="<td>".str_replace("_"," ",$row[0])."</td>\n"; $r .= "<td>".$row[1]."</td>"; # link to jump to regular category page $title =& Title::makeTitle( "14", $row[0] ); $r .= "<td><a href=\"" . $title->escapeLocalUrl() . "\" class='internal' title=\"jump to category " .$row[0]."\">jump</a></td>\n"; # link to get a new list of intersections for the selected category $r .= "<td><a href=\"".$wgScript."?title=Special:Intersections&catlist=".$row[0] ."\" title=\"intersections for ".$row[0]." \" class='internal'>new</a></td>\n"; $r .="</tr>\n"; } $i++; } $r .= "</table>\n"; $wgOut->addHTML( $r."<br />\n" ); } # function to separate subcategory and regular page results function filterpageresults($resary){ global $wgOut; $pagecount = 0; $subcatcount = 0; foreach ($resary as $row){ if($row[2] == "14") { $rtnary["subcats"][$subcatcount] = $row; $subcatcount++; } elseif($row[2] == "0") { $rtnary["pages"][$pagecount] = $row; $pagecount++; } } # debugging # $wgOut->addHTML(str_replace("\n","<br>",print_r(array_values($rtnary),true))); return $rtnary; } function checkexclusions($page){ global $exclpageary; foreach ($exclpageary as $exclrow) { if ($exclrow[0] == $page) return true; } return false; } ?>