SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict AdRock952's Avatar
    Join Date
    Aug 2006
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problems with group by

    I have a query for my message board which basically gets all records for that topic.

    WHat it does is get the author name, the topic name, the date of each post, the message and counts the total number posts each author in that topic has made much like you see here.

    It all works and displays each record with the info but it's not counting each of the authors posts and that is to do with the group by. If i group by date, all message are displayed but the post count is always 1 but if i group by author, the post count is correct but it doesn't display all posts in that topic by the author, only 1

    Code SQL:
    SELECT t.topicname, m.author, m.message, m.DATE, COUNT(message) AS COUNT FROM topics t INNER JOIN messages m ON t.topicid = m.topicid WHERE t.topicid = $id GROUP BY m.DATE ORDER BY DATE ASC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    counting aggregates and showing details are two separate concepts

    if you're going to show all the details, just do the count in your front-end language

    for example, in php you have the mysql_num_rows function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict AdRock952's Avatar
    Join Date
    Aug 2006
    Posts
    243
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would i use the mysql_num_rows() function in my while loop?

    PHP Code:
    $db = & new MySQL($host,$dbUser,$dbPass,$dbName);
        
    $sql="SELECT t.topicname, m.author, m.message, m.date, count(message) as count
        FROM topics t INNER JOIN messages m ON t.topicid = m.topicid WHERE t.topicid = 
    $id GROUP BY m.date ORDER BY date ASC";

    // Perform a query getting back a MySQLResult object
    $result $db->query($sql);

    $err $result->size();

    if(
    $err !=0) {
        echo 
    "<div class='message'>Reply</div>";
        while (
    $row $result->fetch()) {
            
    $topic ucwords($row['topicname']);
            
    $datetime date('F d, Y, h:i:s A'strtotime($row['date']));
            
            echo 
    "<div class='message'>";
            echo 
    "<div class='tdLeft'><h3>{$row['author']}</h3><p>Posts: {$row['count']}</div>";
            echo 
    "<div class='tdRight'><h3>{$topic}</h3><p>{$datetime}</p><hr />".nl2br($row['message'])."</div>";
            echo 
    "</div>";
        }                
    }
    else {
        echo 
    "No records exist";


  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do php

    moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $groups = array();

    while (
    $row $result->fetch()) {
        
    $groups$row['author'] ] = $row;
    }

    print_r($groups);

    foreach (
    $groups as $author => $rows) {
        echo 
    $author count($rows);
        foreach (
    $rows as $row) {
            echo 
    $row['topicname'] ...
        }


  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    wait, wait!!

    your requirement to count rows is actually solved in a different thread

    see http://www.sitepoint.com/forums/showthread.php?t=609702
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •