Pulling Data from Three Tables at Once

Databases
1

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

$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 

$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

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

4

Oddz,

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

-John