SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Pulling Data from Three Tables at Once

    Hello,

    I'm using three MySQL tables with the following names and field structures:

    Table: comment
    Fields: commentid, loginid, submissionid, comment, datecommented, level

    Table: login
    Fields: loginid, username, password, email, actcode, disabled, activated, created, points

    Table: submission
    Fields: submissionid, loginid, title, url, displayurl, datesubmitted

    OK, the code below works great. It prints out an HTML table with the 10 most recently added "titles" from the MySQL table "submission." It also prints out which "username" has the same "loginid" as the "title."

    Each "title" in the HTML table below has a unique "submissionid." This same "submissionid" may appear multiple times or not at all in the MySQL table "comment." What I would like to do is count the number of times any given title's "submissionid" appears in the MySQL table "comment," and then print this total below where "countComments" appears.

    So I would just like to add the total comments for each submission right next to where the submitter's username is currently showing up.

    How can I do this?

    Thanks in advance,

    John

    Code:
    $sqlStr = "SELECT s.loginid, s.title, s.url, s.displayurl, l.username
                 FROM submission AS s,
                      login AS l
                WHERE s.loginid = l.loginid
             ORDER BY s.datesubmitted DESC
               LIMIT 10";
    			
    			
    $result = mysql_query($sqlStr);
    
    $arr = array(); 
    echo "<table class=\"samplesrec\">";
    while ($row = mysql_fetch_array($result)) { 
        echo '<tr>';
        echo '<td class="sitename1"><a href="http://www.'.$row["url"].'">'.$row["title"].'</a></td>';
    	echo '</tr>';
    	echo '<tr>';
    	echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>';
    	echo '</tr>';
    	}
    echo "</table>";

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    PHP Code:
    $sqlStr "SELECT 
                    s.loginid
                    ,s.title
                    ,s.url
                    ,s.displayurl
                    ,l.username
                    ,COUNT(c.commentid) countComments
                 FROM 
                     submission s
                INNER
                 JOIN
                     login l
                   ON
                    s.loginid = l.loginid
                 LEFT OUTER
                 JOIN
                     comment c
                    ON
                     s.submissionid = c.submissionid
                 GROUP
                    BY
                     s.submissionid
                 ORDER  
                    BY 
                     s.datesubmitted DESC
                 LIMIT 
                     10"
    ;
                
                
    $result mysql_query($sqlStr);

    $arr = array(); 
    echo 
    "<table class=\"samplesrec\">";
    while (
    $row mysql_fetch_array($result)) { 
        echo 
    '<tr>';
        echo 
    '<td class="sitename1"><a href="http://www.'.$row["url"].'">'.$row["title"].'</a></td>';
        echo 
    '</tr>';
        echo 
    '<tr>';
        echo 
    '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a><a href="http://www...com/sandbox/comments/index.php?submission='.$row["title"].'">'.$row["countComments"].'</a></td>';
        echo 
    '</tr>';
        }
    echo 
    "</table>"

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    when you have a GROUP BY clause, oddz, it is normal practice to make sure that the grouping column is actually one of the SELECT clause columns -- it doesn't have to be, but it's usually very confusing if it isn't

    the real problem here, though, is that the ORDER BY clause of a GROUP BY query cannot reference a column that isn't in the SELECT clause

    the fact that mysql will actually go ahead and execute a query with that error makes me ill
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2009
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oddz,

    I tried the code you offered and it works. Thanks. You are cool.

    -John


Tags for this Thread

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
  •