SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: query help

  1. #1
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help

    I'm creating a forum and it's structured as such:

    each new post is given a unique id. If the new post is also a new thread, it is given a threadId of 0; if it is a reply to a thread, it is given threadId = id (where id is the id of the thread's initial post).

    in the forum index, I do a query such as:

    SELECT *, UNIX_TIMESTAMP(datetime) AS datetime FROM db WHERE threadId=0 ORDER BY id DESC

    The problem I have is - I want to retrieve the number of replies to a thread. There are multiple ways of doing it, and I managed one (inefficient) way using another query. My question is, how can I (if I can) retrieve the number of posts using the same query above?

    add a COUNT() into there somewhere...I'm not sure exactly how to structure it though.

    Similarly, any other additional efficient methods I can have for retrieving this? Any help would be appreciate. Thanks.

  2. #2
    SitePoint Wizard
    Join Date
    Jul 1999
    Location
    Chicago
    Posts
    2,629
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try doing this:

    SELECT *, count(*) as posts_count UNIX_TIMESTAMP(datetime) AS datetime FROM db WHERE threadId=0 ORDER BY id;

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT *, UNIX_TIMESTAMP(datetime) AS datetime, count(id) AS num FROM db WHERE threadId=0 ORDER BY id DESC

    This should work.
    Marty H.

  4. #4
    SitePoint Member
    Join Date
    Feb 2001
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Those don't work - and it makes sense that they wouldn't. For one, counting * when you are limiting the query to only return new threads and none of the replies will clearly not return the number of replies.

    I guess a new query is needed.

    I have a crude hack, but there's no way I'm going to use it unless I want to crash the server.

    If I do SELECT COUNT(*) AS posts WHERE threadID > 0 I can get the number of all the replies - but it's not broken down per thread. I want to get it so that the result set has as many rows as there are threads, with each row representing the number of replies to that thread.

    anything?

    I guess if all else fails, I can go back and modify the db so that it has a field keeping track of all replies...I rather not..
    eh?

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2001
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I understand and I think you will need two queries.

    $sql1 = "SELECT *, UNIX_TIMESTAMP(datetime) AS datetime FROM db WHERE threadId=0 ORDER BY id DESC";

    That is the first query you posted. And will get you the posts that started threads. To get the number of replies to each you will need.

    $sql2 = "SELECT COUNT(*) AS posts GROUP BY threadId order by threadId DESC";

    That will give you an array with 1 extra row. The last row will be threadid = 0 count, so will contain the number of threads. All other rows will contain the number of replies to a thread with a particular id ordered in a descending fashion. To echo those results

    $res1 = mysql_query($sql1);
    $res2 = mysql-query($sql2);

    while ( $row1 = mysql_fetch array($res1) && $row2 = mysql_fetch_array($res2) ) {
    echo "$row1[thread_titles] - $row2[replies]";
    }

    On second thought, this won't work. The problem is with those threads that do not have any replies. In that case the second query will return fewer rows than the first.

    I am going to post this anyway as it may lead somebody to a solution. I would think that maybe this is going to be difficult without altering the database design.

    You could eliminate this problem, I think, by not placing a 0 for first posts in the threadId field and querying for posts that have the minimum date and a unique threadId. I don't know if that is possible. But it is a thought.
    Marty H.


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
  •