SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying how many entries are in database?

    I have a basic array output like this:

    PHP Code:
    <?php 

    $query 
    mysql_query("SELECT category, subcategory FROM jos_linksubcat ORDER BY category, subcategory"); 
    if (
    mysql_num_rows($query)) { 
    while(
    $info mysql_fetch_object($query)) { 
    $cat[$info->category][] = array('category'=>$info->subcategory); 



    $categories array_keys($cat); 
    echo 
    '<ul>'
    foreach (
    $categories as $main) { 
    echo 
    '<span class="style6"><br>'.$main.'</span>'
    if (isset(
    $cat[$main])) { 
    echo 
    '<ul>'
    foreach (
    $cat[$main] as $sub) { 
    echo 
    '<a href="?category='.$main.'&subcategory='.$sub['category'].'" style="text-decoration:none">'.$sub['category'].'</a><br>'

    echo 
    '</ul>'

    echo 
    '</li>'

    echo 
    '</ul>'

    ?>
    Basically on this line here:

    PHP Code:
    echo '<a href="?category='.$main.'&subcategory='.$sub['category'].'" style="text-decoration:none">'.$sub['category'].'</a><br>'
    I'm wanting it to do an output that says:

    SubCategory Name (Number of entries in this sub category)

    example:

    Travel Links (8)

    How would I get it to do this?

    Would it be like "Select number of entries from jos_links where sub category = '.$sub['category'].'"?

  2. #2
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is this related to the checksum feature?

  3. #3
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have two options:

    1. Execute a query to count items assigned to each subcategory (this is slow since it will execute a query for every sub category).

    2. Use a query to do a GROUP BY, and run a query per top-level category. It will give you the sub categories and entry counts:

    Just for example:
    SELECT c.category, count(*) as item_count
    FROM category c, entries e
    WHERE c.category_id = e.category_id
    AND c.parent_category_id = <PARENT CATEGORY ID>
    GROUP BY c.category

    Then just loop through the results of this query for your sub-category list.

    You could get really fancy and build one query to return top categories, sub categories, and item counts. :-)

  4. #4
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would I make that appear in the array?

  5. #5
    SitePoint Member
    Join Date
    Aug 2006
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you give me the exact tables and column names? Then I could give you a better example...

  6. #6
    SitePoint Guru
    Join Date
    Apr 2007
    Posts
    685
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    table name: jos_links

    column names: id - company - website address - category - subcategory


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
  •