查询查找评论在一个表中,用户名中的另一个表
问题描述:
我使用所谓的“登录”一个MySQL表结构如下:查询查找评论在一个表中,用户名中的另一个表
loginid, username, password, email, actcode, disabled, activated, created, points
我使用一种叫“注释” MySQL表与结构如下:
commentid, loginid, submissionid, comment, datecommented
对于给定的“submisssionid”,我想打印出从表中“注释”的以下信息:
-The领域的“意见”和“datecommented”。
同时,我想打印出从表“登陆”以下内容:
对应于“登录ID”为每排都排-The“用户名”被从表中选择“评论”。
我该怎么做?
我试了下面的代码,但它没有奏效。
由于提前,
约翰
$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>";
答
SELECT comment.comment, comment.datecommented, login.username
FROM comment
LEFT JOIN login ON comment.loginid=login.loginid
WHERE submissionid=$submissionid
ORDER BY comment.datecommented DESC
LIMIT 100";