Query to look up comment in one table, username in another table
- by John
Hello,
I am using a MySQL table called "login" with the following structure:
loginid, username, password, email, actcode, disabled, activated, created, points
I am using another MySQL table called "comment" with the following structure:
commentid, loginid, submissionid, comment, datecommented
For a given "submisssionid", I would like to print out the following information from the table "comment":
-The fields "comment" and "datecommented".
At the same time, I would like to print out the following from the table "login":
-The "username" that corresponds to the "loginid" for each row being row being selected from the table "comment".
How can I do this?
I tried the code below but it did not work.
Thanks in advance,
John
$submission = mysql_real_escape_string($_GET['submission']);
$submissionid = mysql_real_escape_string($_GET['submissionid']);
$sqlStr = "SELECT
c.loginid
,c.submissionid
,c.comment
,c.datecommented
,l.username
,COUNT(c.commentid) countComments
FROM
comment c
WHERE
c.submissionid = $submissionid
INNER
JOIN
login l
ON
c.loginid = l.loginid
GROUP
BY
c.submissionid
ORDER
BY
c.datecommented DESC
LIMIT
100";
$result = mysql_query($sqlStr);
$arr = array();
echo "<table class=\"samplesrec\">";
while ($row = mysql_fetch_array($result)) {
echo '<tr>';
echo '<td class="sitename1">'.$row["comment"].'</td>';
echo '</tr>';
echo '<tr>';
echo '<td class="sitename2"><a href="http://www...com/sandbox/members/index.php?profile='.$row["username"].'">'.$row["username"].'</a>'.$row["datecommented"].'</td>';
echo '</tr>';
}
echo "</table>";
