SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting the number of rows in a specfic table when selecting from more than one.

    Lengthy topic name. I'll try to keep it as simple as possible.

    I'm making a pretty basic forum program and would like to display the forum names and the number of threads in that specific forum with one query.

    Normally to get the number of threads I would do

    SELECT COUNT(*) FROM ef_threads WHERE forum='ForumID'

    But as I need to select the name of the forum from a sepperate table it obviously means the count doesn't work. I've tried COUNT(ef_threads.*) and other variations but it doesn't work. Any suggestions on the following query to get what I'm after would be most appreciated.

    SELECT ef_forums.name AS ForumName, ef_forums.id AS ForumID, count(ef_threads.*) AS ThreadCount FROM ef_forums, ef_threads WHERE ef.threads.parentforum='ForumID'

    Thanks in advance. And sorry for the complexity of my explanation. I never seem able to phrase my questions in an easy and concise way. Hopefully you can understand what I mean.

  2. #2
    SitePoint Enthusiast Stallion's Avatar
    Join Date
    Jan 2001
    Location
    Cumberland, RI, US
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Although the above would be possible, it is MUCH faster to store the total number of threads in a value in the forum table. Performing many COUNT(*) queries on large tables will seriously bog down MySQL.
    /* Chris Lambert - chris@php.net
    WhiteCrown Networks, CTO - Web Application Security
    vBulletin, Security Programmer - Instant Community
    */


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
  •