SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MAN ALIVE! Just as I thought everything was working!

    I've got the majority of a web directory going but I need to know how I would go about counting the links that are contained in the categories, sub-categories, sub-sub-categories etc etc...

    My Database tables are as follows:-
    Categories
    ----------
    ParentCat
    CatID
    Heirarchy
    Name
    LinkCount

    Links
    -----
    LinkID
    CatOwner
    URL
    Description
    Name
    ClickCount

    I can run a SELECT * FROM links WHERE catowner = '2' or something like that but that only gives the link count of the links that are 'solidly' a part of that category.

    I need to have it so that it would appear in the following way

    Soccer (20)
    : World Cup (11)
    : History (7)
    : Current Tournament (4)
    : MLS (9)
    : History (3)
    : Current Season (6)

    At the moment I get 0 links in Soccer, 0 in World Cup and 0 in MLS. Any ideas? (PHP & MySQL btw)

    Cheers,

  2. #2
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've hit exactly the same problem with my open source links management system (link in sig, site is down at the moment though). Unfortunately there is no easy solution - the two I've come up with are:

    1. For each category perform recursive SQL queries on all sub categories and add together the total number of links. This hammers your database and is a very bad idea for anything where performance under load could be an issue.

    2. 'Cache' the number of total links in a category in a field in the category table. Whenever a link is added or removed from a sub category the script goes up through all the branches above updating the cached value. This is a better solution than the above as far as performance is concerned but can lead to data inbalances in your database if you forget to update the stored number when adding a new link.

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Regarding Skunk's suggestions:

    2) This is where triggers and stored procedures are handy. Pitty MySQL doesn't have them You could try and enforce the "business" rule that when you update a category or link you recalculate the category counts by wrapping all the relevent code into a series of functions or classes. This is where classes become handy in a design. This way in your script you don't directly call mysql_query but put the database access into the relevent functions say:

    updateCounts() // recalculates all the category counts in the db
    addLink() // adds link to db and call updateCounts()
    deleteLink() // deletes linkand call updateCounts()
    modifyLinkCategory // changes a links category and call updateCounts()

    1) Recursion... Hmmmm... recursion. Couldn't resist having a doodle at some code:
    Code:
    function countCats($parentCat, &$count, &$connection) {
       // receives:
       //   $parentCat - id of the parent category to count for
       //   &$count - by ref, the count of the $parentCat's total links 
       //   &$connection - by ref, the active database connection
      
       // count all links that belong directly to $parentCat and add to $count 
       $sql = "SELECT COUNT(*) 
               FROM Links
               WHERE CatOwner = $parentCat";   
       $result = mysql_query($sql, $connection);
       $i = mysql_fetch_field($result, 0);
       $count += $i;
       mysql_free_result($result);
       
       // call countCats() recursively for all sub-categories of $parentCat
       $sql = "SELECT CatID
               FROM Categories
               WHERE ParentCat = $parentCat";
       $result = mysql_query($sql, $connection);
       
       while($subCat = mysql_fetch_array($result)) {
          countCats($subCat[0], $count, $connection);
       }
    }
    Tell me if this is completely wrong I wrote it assuming that there might be links at each level of category/sub-category.

    Ha - even this function has to be called recursively to count a total for each category/sub-category - whew!
    Last edited by freakysid; Apr 3, 2001 at 09:07.

  4. #4
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freakysid - excellent idea about using a class interface. I may well do that for the next release of my links script

  5. #5
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha!

    Here is proof that I write the untidiest code EVER!
    I got it to work, Skunk..just about.

    Code:
    // LinkCounter
    mysql_connect($site, $username, $password); 
    $remove = mysql_db_query($dbName, "UPDATE categories SET linkcount = '0'");
    
    
    $result = mysql($dbName, "SELECT * from categories");
    $num = mysql_num_rows($result);
    $i = 0;
    while ($i < $num) :
    $catids[$i] = mysql_result($result,$i,"catid");
    $parentids[$i] = mysql_result($result,$i,"parentcat");
    $work = mysql($dbName, "SELECT * from links where catowner = '$catids[$i]'");
    $numlinks[$i] = mysql_num_rows($work);
    
    $i++;
    endwhile;
    
    $d = 0;
    
    while ($d < $num) :
    $currentlinks = $numlinks[$d];
    $z = 0;
    $catfind = mysql($dbName, "SELECT * from categories WHERE parentcat = '$catids[$d]'");
    $found = mysql_num_rows($catfind);
     
      if ($found == "0") {
    
      $worker = mysql_db_query($dbName, "UPDATE categories SET linkcount = '$currentlinks' WHERE catid = '$catids[$d]'");
     
      } else {
    
      $totallinks = $numlinks[$d];
    
      while ($z < $found) :
      
      $workingparent = mysql_result($catfind,$z,"catid");
    
      $totallinks = $totallinks + $numlinks[$workingparent];
    
      $z++;
      endwhile;
      $worker = mysql_db_query($dbName, "UPDATE categories SET linkcount = '$totallinks' WHERE catid = '$catids[$d]'");
      }
    $worker = "";
    $d++;
    endwhile;
    This script basically checks out the category that you are in, stores the amount of links in a variable, checks out the subcategories, adds the links to the variable and then updates the LinkCount column in the DB.

    Seems to work fine for me. Anyone wants it, take it
    I'm sure you guys will find a way of cleaning it up.

    I take your point about server load, but as I'll be running this script about once a week as part of our update, it shouldn't be a problem.

    Cheers,

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I know how to accomplish the whole thing in one query, but I'm still fuzzy on how the IDs join together. If you could tell me what exactly the relationships are I can see if my SQL is busted or not

    Something like:
    Categories
    ----------
    ParentCat (where is this generated from)?
    CatID (auto increment?)
    Heirarchy
    Name
    LinkCount

    Links
    -----
    LinkID (auto increment )
    CatOwner (refers to CAtegories -> CatID??)
    URL
    Description
    Name
    ClickCount

    And then show a couple of rows I'd be happy.

  7. #7
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay man, here ya go.

    Starting with some tables..

    Code:
    Categories
    ----------
    ParentCat (entered when a new category is entered, by hand ATM)
    CatID (auto increment)
    Heirarchy (basically a text description of the heirarchy, example below)
    Name (category name)
    LinkCount (current amount of links)
    
    Links
    -----
    LinkID (auto increment)
    CatOwner (refers to CatID on category table)
    URL (URL)
    Description (text description)
    Name (Link Name, ie. SitePoint.com)
    ClickCount (current amount of clicks from our site)
    And now for some rows (in column format, strangely enough)...

    Code:
    ------------
    CATEGORIES
    ------------
    ParentCat - 1
    CatID  - 3
    Heirarchy - NetGuide >> Soccer >> World Cup 98
    Name - World Cup 98
    LinkCount - 4
    ------------
    ParentCat - 999 (meaning no parent)
    CatID  - 1
    Heirarchy - NetGuide >> Soccer
    Name - Soccer
    LinkCount - 6
    ------------
    LINKS
    ------------
    LinkID - 12
    CatOwner - 3
    URL - www.worldcup98.com
    Description - Official World Cup 98 Site
    Name - WorldCup98.com
    ClickCount - 14
    -------------
    That should do ya, need anything else, give me a yell.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •