Wiki source for WikkaCountingRecords


Show raw source

=====Counting Records=====

>>==See also:==
//general://
~-UnderDevelopment
~-WikkaBetaFeatures

//development pages://
~-PageAdminAction
~-[[UserAdmin UserAdminAction]]
~-WikkaMenulets
~-AdvancedReferrersHandler

//test pages://
~-SysInfo
>>This is the development page for a new [[Docs:WikkaCore core]] method **##getCount()##**.

There are numerous places in Wikka where we need to know "how many" of a certain thing there are in the database: how many pages, how many comments, how many pages owned by the logged-in user, ... etc. And there will be more.

Currently all these counts are retrieved via different methods: sometimes building a query and getting the count directly (the most efficient way); sometimes by using the ##""LoadSingle()""## method after building a query, which is highly inefficient, as it effectively first asks MySQL to assign a name to the count and return records in an associative array, then retrieves the first (only!) item from the array of records, and finally retrieves the number from the associative array by name.::c::
====A single method====

To avoid all this inconsistency and inefficiency, here is a simple method that does nothing but retrieve a count and return it as an integer.

===The ##getCount()## method===

>>Implemented as **[[WikkaBetaFeatures beta feature]]** as of 2005-07-19.
>>The following method is added to ##libs\Wakka.class.php## right after the ##""LoadAll()""## method:::c::
%%(php) /**
* Generic 'count' query.
*
* Get a count of the number of records in a given table that would be matched
* by the given (optional) WHERE criteria. Only a single table can be queried.
*
* @access public
* @uses Query()
*
* @param string $table required: (logical) table name to query;
* prefix will be automatically added
* @param string $where optional: criteria to be specified for a WHERE clause;
* do not include WHERE
* @return integer number of matches returned by MySQL
*/
function getCount($table,$where='') # JW 2005-07-16
{
// build query
$where = ('' != $where) ? ' WHERE '.$where : '';
$query = 'SELECT COUNT(*) FROM '.$this->config['table_prefix'].$table.$where;

// get and return the count as an integer
return (int)mysql_result($this->Query($query),0);
}
%%

As can be seen from the comment, all we pass to the method is the logical table name to query, and optionally the criteria to be used in a ##WHERE## clause. The method builds a query adding the missing bits and returns the result (making sure it is an integer).

====Doing it consistently====

Now, with the ##getCount()## method we have the tool to consistently and efficiently retrieve record counts where needed. Let's first look at where this can be used in the current 1.1.6.0 release.

===Wikka Core===

There is one method in the [[WikkaCore Wikka Core]] where a count of records is used, so we can aply the new method here.

==##""ExistsPage()""## method==
Current code:
%%(php;486) /**
* Check by name if a page exists.
*
* @author {@link http://wikka.jsnx.com/JavaWoman JavaWoman}
* @copyright Copyright © 2004, Marjolein Katsma
* @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
* @version 1.0
*
* @access public
* @uses Query()
*
* @param string $page page name to check
* @return boolean TRUE if page exists, FALSE otherwise
*/
function ExistsPage($page)
{
$count = 0;
$query = "SELECT COUNT(tag)
FROM ".$this->config['table_prefix']."pages
WHERE tag='".mysql_real_escape_string($page)."'";
if ($r = $this->Query($query))
{
$count = mysql_result($r,0);
mysql_free_result($r);
}
return ($count > 0) ? TRUE : FALSE;
}
%%

>>Modified code implemented as **[[WikkaBetaFeatures beta feature]]** as of 2005-07-19.
>>This can now be rewritten as:::c::
%%(php) /**
* Check by name if a page exists.
*
* @author {@link http://wikka.jsnx.com/JavaWoman JavaWoman}
* @copyright Copyright © 2004, Marjolein Katsma
* @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
* @version 1.1
*
* @access public
* @uses getCount()
*
* @param string $page page name to check
* @return boolean TRUE if page exists, FALSE otherwise
*/
function ExistsPage($page)
{
$where = "`tag` = '".mysql_real_escape_string($page)."'";
$count = $this->getCount('pages',$where);
return ($count > 0);
}
%%

===Actions===

==##actions/countcomments.php##==
Current code:
%%(php;1)<?php
/**
* Print total number of comments in this wiki.
*/
$commentsdata = $this->LoadSingle("SELECT count(*) as num FROM ".$this->config["table_prefix"]."comments");
echo $commentsdata["num"];
?>
%%

This can now be rewritten as:
%%(php;1)<?php
/**
* Print total number of comments in this wiki.
*/
echo $this->getCount('comments');
?>
%%

==##actions/countowned.php##==
Current code:
%%(php;1)<?php
/**
* Print number of pages owned by the current user.
*/
$str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `owner` ';
$str .= "= '" . $this->GetUserName() . "' AND `latest` = 'Y'";
$countquery = $this->Query($str);
$count = mysql_result($countquery, 0);
echo $this->Link('MyPages', '', $count,'','','Display a list of the pages you currently own');

?>
%%

This can now be rewritten as:
%%(php;1)<?php
/**
* Print number of pages owned by the current user.
*/
$where = "`owner` = ".$this->GetUserName()." AND `latest` = 'Y'";
$count = $this->getCount('pages',$where);
echo $this->Link('MyPages', '',$count,'','','Display a list of the pages you currently own');
?>
%%

==##actions/countpages.php##==
Current code:
%%(php;1)<?php
/**
* Print the total number of pages in this wiki.
*/
$pagedata = $this->LoadSingle("SELECT count(*) as num FROM ".$this->config["table_prefix"]."pages WHERE latest = 'Y'");
echo $this->Link('PageIndex', '', $pagedata['num'],'','','Display an alphabetical page index');

?>
%%

This can now be rewritten as:
%%(php;1)<?php
/**
* Print the total number of pages in this wiki.
*/
$where = "`latest` = 'Y'";
$count = $this->getCount('pages',$where);
echo $this->Link('PageIndex', '',$count,'','','Display an alphabetical page index');
?>
%%

==##actions/countusers.php##==
Current code:
%%(php;1)<?php
/**
* Print number of registered users.
*/
$userdata = $this->LoadSingle("SELECT count(*) as num FROM ".$this->config["table_prefix"]."users ");
echo $userdata["num"];
?>
%%

This can now be rewritten as:
%%(php;1)<?php
/**
* Print number of registered users.
*/
echo $this->getCount('users');
?>
%%

==##actions/highscores.php##==
This uses the following code to get a 'total' count:
%%(php;13)
$str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `latest` = \'Y\' ';
$totalQuery = $this->Query( $str );
$total = mysql_result($totalQuery, 0);
%%

This can now be rewritten as:
%%(php;13)
$where = "`latest` = 'Y'";
$total = $this->getCount('pages',$where);
%%

While this is actually the same query, we'd use the ##getCount()## method here for consistency (and hiding complexity).

==##actions/lastusers.php##==
This prints the number of pages owned by a particular (new) user as follows:
%%(php;21) if ($stat!=="0") $num = $this->LoadSingle("select count(*) as n from ".$this->config["table_prefix"]."pages where owner='".$user["name"]."' AND latest = 'Y'");
$htmlout .= " <td>".$this->Link($user["name"])."</td>\n <td>".($stat!=="0"?" . . . . . (".$num["n"].")":"")."</td>\n <td>(".$user["signuptime"].")</td>\n";
%%

This can now be (better) rewritten as:
%%(php;21)
if ($stat !== "0")
{
$where = "`owner` = '".$user['name']."' AND `latest` = 'Y'";
$htmlout .= " <td>".$this->Link($user['name'])."</td>\n <td>"." . . . . . (".$this->getCount('pages',$where).")"."</td>\n <td>(".$user['signuptime'].")</td>\n";
}
else
{
$htmlout .= " <td>".$this->Link($user['name'])."</td>\n <td></td>\n <td>(".$user['signuptime'].")</td>\n";
}
%%---''There are other ways in which this action can (and should) be improved but we're looking only at counting records here.''

==##actions/ownedpages.php##==
This uses two counts. Current code:
%%(php;7) $str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `owner` ';
$str .= "= '" . $this->GetUserName() . "' AND `latest` = 'Y'";
$countQuery = $this->Query( $str );

# get the total # of pages
$str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `latest` = \'Y\' ';
$totalQuery = $this->Query( $str );

$count = mysql_result($countQuery, 0);
$total = mysql_result($totalQuery, 0);
%%

This can now be rewritten as:
%%(php;7)
$whereOwner = "`owner` = '" . $this->GetUserName() . "' AND `latest` = 'Y'";
$whereTotal = "`latest` = 'Y'";

$count = $this->getCount('pages',$whereOwner);
$total = $this->getCount('pages',$whereTotal);
%%

As with the ##highscores## action, this uses actually the same queries, but we use the ##getCount()## method for the same reasons.

====Beta features and proposed code====

A number of current [[WikkaBetaFeatures beta features]] and other code proposed for a next release can also make good use of the ##getCount()## method. We'll look at them here.

===Actions===

==##actions/pageadmin.php##==
>>The ##pageadmin## action has now been fixed using modifications somewhat along the lines indicated below (though details differ); see PageAdminAction for the details.>>//See also the PageAdminAction development page and WikkaBetaFeatures.//::c::
This action uses a lot of counts but (in version 0.3.1) was retrieving them very inefficiently, in most cases by actually retrieving all records and then counting those, without ever using the records themselves. We'll simply show some "before-and-after" code illustrating how the ##getCount()## method can help here. Line numbers are as they were in beta version 0.3.1 as installed on this site.

Applying the new ##getCount()## method applied as outlined below not only solves the problem of the (now) missing ##""LoadReferrers()""## method but makes the whole [[PageAdminAction pageadmin action]] a lot more efficient. See also WikkaBetaFeatures.

Before (version 0.3.1):
%%(php;63) // restrict MySQL query by search string
$where = 'WHERE tag LIKE "%'.$q.'%"and latest = "Y"';

// get total number of pages
$pages = $this->LoadSingle('SELECT count(*) as n FROM '.$this->config['table_prefix'].'pages '.$where);
$numpages = $pages['n'];
%%
After:
%%(php;63) // restrict MySQL query by search string
$where = "`tag` LIKE '%".$q."%' AND `latest` = 'Y'";
// get total number of pages
$numpages = $this->getCount('pages',$where);
%%

Before:
%%(php;177) // get page revisions and create revision link if needed
$revisions = $this->LoadRevisions($page['tag']);
$rv = count($revisions);
%%
After:
%%(php;177) // get page revisions and create revision link if needed
$where = "`tag` = '".$page['tag']."'";
$rv = $this->getCount('pages',$where);
%%

Before:
%%(php;182) // get page comments and create comments link if needed
$comments = $this->LoadComments($page['tag']);
$cn = count($comments);
%%
After:
%%(php;182) // get page comments and create comments link if needed
$where = "`page_tag` = '".$page['tag']."'";
$cn = $this->getCount('comments',$where);
%%

Before:
%%(php;187) // get page backlinks and create backlinks link
$backlinks = $this->LoadPagesLinkingTo($page['tag']);
$bn = count($backlinks);
%%
After:
%%(php;187) // get page backlinks and create backlinks link
$where = "`to_tag` = '".$page['tag']."'";
$bn = $this->getCount('links',$where);
%%

Before:
%%(php;192) // get page referrers and create referrer link
$referrers = $this->LoadReferrers($page['tag']);
$rn = count($referrers);
%%
After:
%%(php;192) // get page referrers and create referrer link
$where = "`page_tag` = '".$page['tag']."'";
$rn = $this->getCount('referrers',$where);
%%
''The ##$where## variable is the same here as for the comments count, so line 193 is actually superfluous! So rather than building the ##$where## variables just before each ##getCount()## call, it would be clearer to build both in advance.''

==##actions/useradmin.php##==
//See also the [[UserAdmin UserAdminAction]] development page and WikkaBetaFeatures.//

Not surprisingly, this Admin action also uses several counts. But in one case it's pre-building a ##WHERE## clause to be used in two different queries, only one of which is fo counting, so we need to adapt the code for both. Three blocks of "before" and three "after" will show how to handle this:

Before:
%%(php;37) // search results
$where = ($_POST["search"])? "WHERE name LIKE \"%".$_POST["search"]."%\"" : "";
%%
%%(php;42) // 1. Get total number of users
$users = $this->LoadSingle("select count(*) as n FROM ".$this->config["table_prefix"]."users ".$where);
$numusers = $users['n'];
%%
%%(php;66) // get user data
$userdata = $this->LoadAll("SELECT name, email, signuptime FROM ".$this->config["table_prefix"]."users ".
$where." ORDER BY ".$sort." ".$d." limit ".$s.", ".$l);
%%

After:
%%(php;37) // search results
$where = ($_POST['search']) ? '`name` LIKE "%'.$_POST['search'].'%"' : '';
%%
%%(php;42) // 1. Get total number of users
$numusers = $this->getCount('users',$where);
%%
%%(php;65) // get user data
$userdata = $this->LoadAll("SELECT name, email, signuptime FROM ".$this->config["table_prefix"]."users ".
(($where != '') ? "WHERE ".$where : '')." ORDER BY ".$sort." ".$d." limit ".$s.", ".$l);
%%

Farther down, three more counts are used:

Before:
%%(php;90) $num = $this->LoadSingle("SELECT COUNT(*) AS n FROM ".$this->config["table_prefix"].
"pages WHERE owner='".$user["name"]."' AND latest = 'Y'");
$numchanges = $this->LoadSingle("SELECT COUNT(*) AS m FROM ".$this->config["table_prefix"].
"pages WHERE user='".$user["name"]."'");
$numcomments = $this->LoadSingle("SELECT COUNT(*) AS q FROM ".$this->config["table_prefix"].
"comments WHERE user='".$user["name"]."'");
%%

After:
%%(php;89) $whereOwner = "`owner` ='".$user['name']."' AND latest = 'Y'";
$whereUser = "`user` = '".$user['name']."'";
$num = $this->getCount('pages',$whereOwner);
$numchanges = $this->getCount('pages',$whereUser);
$numcomments = $thiis->getCount('comments',$whereUser);
%%


===Handlers===

While the rewritten [[AdvancedReferrersHandler referrers handler]] uses a number of counts, these are part of larger queries: no rewrite necessary here. The new review blacklist handler can use the ##getCount()## method though:

==##handlers/page/review_blacklist.php##==
//See also the AdvancedReferrersHandler development page and WikkaBetaFeatures.//

The new review blacklist handler does a total count as well which can be simplified now.
Before:
%%(php;179) // get total number of domains in blacklist
$query_refcount = 'SELECT COUNT(spammer) AS total';
$query_refcount .= ' FROM '.$pre.'referrer_blacklist';
%%
%%(php;198) $totalrefs = $this->LoadSingle($query_refcount);
%%

After (**the first block at line 179 is no longer needed at all!**):
%%(php;198) $totalrefs = $this->getCount('referrer_blacklist');
%%


====Todo====

The new ##getCount()## method itself and its implementation in ##""ExistsPage()""## have been implemented as beta on this site now. A new version of the (beta) ##pageadmin## action now uses it as well (solving an outstanding bug due to the AdvancedReferrersHandler). However:
~1)All other possible code changes outlined here still need to be tested.
~1)The method could be extended somewhat to make it more capable; for instance:
~~-allow the ##table## parameter to be an array, and build the ##FROM## clause using the list of table names

====Comments?====

As always, comments and suggestions more than welcome.

----
CategoryDevelopmentCore CategoryDevelopmentActions CategoryDevelopmentHandlers
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki