SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count Contents of Each Category

    I'm using the following script to extract the category names from my database
    PHP Code:
    if (isset($userID)) {
     
    $GetAllCats_result mysql_query("SELECT cID, name FROM cats ORDER BY name ASC")
     or die(
    mysql_error());
     
    $GetAllCats mysql_query($GetAllCats_result); 
    I also have a whole bunch of links stored in a table called "links" and I would like to count how many links are associated with category and display the number next to the category name. in the links table, I need to do a
    PHP Code:
    if (isset($userID)) {
      
    $GetAllCats_result mysql_query("SELECT count(*) AS count FROM links WHERE lcID=cID GROUP BY lcID")
      or die(
    mysql_error());
      
    $GetAllCats mysql_query($GetAllCats_result); 
    This is okay if I only want to count a particular category, but I can't work out how to join the 2 queries togther.

    Ian

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT c.cID
         , c.name
         , count(l.lID) as linkcount
     FROM cats c
    LEFT OUTER
      JOIN links l
        ON l.lcID = c.cID
    GROUP
        BY c.cID
         , c.name
    ORDER
        BY name ASC

  3. #3
    SitePoint Evangelist spoondevil's Avatar
    Join Date
    Jun 2001
    Location
    Harlow, Essex, UK
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers longneck, I'll give it a go tomorrow when I get some time.

    Ian


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
  •