Revision [10023]

This is an old revision of WikkaCountingRecords made by JavaWoman on 2005-07-16 13:09:15.

 

Counting Records

this page is a stub - more content will follow

This is the development page for a new 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 or records, and finally retrieves the number from the associative array by name.

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


The following method is added to wikka.php right after the LoadAll() method:
    /**
     * 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:
  1.     /**
  2.      * Check by name if a page exists.
  3.      *
  4.      * @author      {@link http://wikka.jsnx.com/JavaWoman JavaWoman}
  5.      * @copyright   Copyright © 2004, Marjolein Katsma
  6.      * @license     http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
  7.      * @version     1.0
  8.      *
  9.      * @access      public
  10.      * @uses        Query()
  11.      *
  12.      * @param       string  $page  page name to check
  13.      * @return      boolean  TRUE if page exists, FALSE otherwise
  14.      */
  15.     function ExistsPage($page)
  16.     {
  17.         $count = 0;
  18.         $query =    "SELECT COUNT(tag)
  19.                     FROM ".$this->config['table_prefix']."pages
  20.                     WHERE tag='".mysql_real_escape_string($page)."'";
  21.         if ($r = $this->Query($query))
  22.         {
  23.             $count = mysql_result($r,0);
  24.             mysql_free_result($r);
  25.         }
  26.         return ($count > 0) ? TRUE : FALSE;
  27.     }


This can now be rewritten as:
    /**
     * 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:
  1. <?php
  2. /**
  3.  * Print total number of comments in this wiki.
  4.  */
  5. $commentsdata = $this->LoadSingle("SELECT count(*) as num FROM ".$this->config["table_prefix"]."comments");
  6. echo $commentsdata["num"];
  7. ?>


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


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


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


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


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


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


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


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


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


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

actions/lastusers.php
This prints the number of pages owned by a particular (new) user as follows:
  1.  if ($stat!=="0") $num = $this->LoadSingle("select count(*) as n from ".$this->config["table_prefix"]."pages where owner='".$user["name"]."' AND latest = 'Y'");
  2.  $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:
  1.     if ($stat !== "0")
  2.     {
  3.         $where = "`owner` = '".$user['name']."' AND `latest` = 'Y'";
  4.         $htmlout .= "    <td>".$this->Link($user['name'])."</td>\n    <td>"." . . . . . (".$this->getCount('pages',$where).")"."</td>\n    <td>(".$user['signuptime'].")</td>\n";
  5.     }
  6.     else
  7.     {
  8.         $htmlout .= "    <td>".$this->Link($user['name'])."</td>\n    <td></td>\n    <td>(".$user['signuptime'].")</td>\n";
  9.     }

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:
  1.     $str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `owner` ';
  2.     $str .= "= '" . $this->GetUserName() . "' AND `latest` = 'Y'";
  3.     $countQuery = $this->Query( $str );
  4.    
  5.     # get the total # of pages
  6.     $str = 'SELECT COUNT(*) FROM '.$this->config["table_prefix"].'pages WHERE `latest` = \'Y\' ';
  7.     $totalQuery = $this->Query( $str );    
  8.    
  9.     $count  = mysql_result($countQuery, 0);
  10.     $total  = mysql_result($totalQuery, 0);


This can now be rewritten as:
  1.     $whereowner = "`owner` = '" . $this->GetUserName() . "' AND `latest` = 'Y'";
  2.     $wheretotal   = "`latest` = 'Y'";
  3.  
  4.     $count = $this->getCount('pages',$whereowner);
  5.     $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.

to follow

Todo


The method could be extended somewhat to make it more capable; for instance:

Comments?


As always, comments and suggestions more than welcome but please wait until this page is no longer marked as a stub.


CategoryDevelopmentCore CategoryDevelopmentActions CategoryDevelopmentHandlers
There are 2 comments on this page. [Show comments]
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki