User:Aerik/Intersections code

From Meta, a Wikimedia project coordination wiki

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;
}
?>
Retrieved from "https://meta.wikimedia.org/w/index.php?title=User:Aerik/Intersections_code&oldid=24123204"