Question

我有以下问题。我有三个表格:信息、评论和用户。我想PHP从MySQL数据库中印刷以下领域的数据,作为我网页的表格:电文。主题、用户名称和评论数目。所有其他物品都做的是罚款,但我用斜线印制评论。

这是我迄今为止努力做到的:

<?php
 include("info.php");
 $connect;
 $sql="SELECT * FROM message, user
       WHERE message.userID = user.userID AND 
       ORDER BY message.messageID DESC";
 $result=mysql_query($sql) or die(mysql_error());

 $sql2="SELECT message.messageID, COUNT(*) as comments FROM comment 
        INNER JOIN
        message ON comment.messageID = message.messageID 
        GROUP BY comment.messageID";
 $result2=mysql_query($sql2) or die(mysql_error());

<table>
 <thead>
  <tr>
   <th>Subject</th>   
   <th>Sender</th>
   <th>Number of comments</th>
  </tr>
 </thead>
 <tbody>
  <?php
   while($rows=mysql_fetch_array($result)){
  ?>
  <tr>
   <td>
    <a href="php/message.php?id=<?php echo $rows[ messageID ]; ?>">
    <?php echo $rows[ subject ]; ?>
   </td>
   <td>
    <?php echo $rows[ username ]; ?>
   </td>                        
   <td>
    <?php while($rows2=mysql_fetch_array($result2)){ echo $rows2[ comments ];}?>
   </td>
  </tr>
 <?php
 } mysql_close(); ?>
 </tbody>
</table>

Answer 1

你们能否用一个问问点来看待所有这一切。

SELECT
    m.messageID,
    m.subject,
    u.username,
    c.numOfComments
FROM
    message m
    INNER JOIN user u ON m.userID = u.userID
    LEFT JOIN (SELECT COUNT(1) AS numOfComments, messageID FROM comments GROUP BY messageID) c ON m.messageID = c.messageID
ORDER BY
    m.messageID DESC

引文:

<?php
    include("info.php");
    connect();
    $sql = "
        SELECT
            m.messageID,
            m.subject,
            u.username,
            c.numOfComments
        FROM
            message m
            INNER JOIN user u ON m.userID = u.userID
            LEFT JOIN (SELECT COUNT(1) AS numOfComments, messageID FROM comments GROUP BY messageID) c ON m.messageID = c.messageID
        ORDER BY
            m.messageID DESC
    ";
    $result = mysql_query($sql) or die(mysql_error());

    echo "<table>
    <thead>
        <tr>
            <th>Subject</th>
            <th>Sender</th>
            <th>Number of comments</th>
        </tr>
    </thead>
    <tbody>
";
    while ($row = mysql_fetch_assoc($result))
    {
        $row = array_map("htmlspecialchars", $row); // sanitize to prevent XSS

        echo "      <tr>
            <td><a href="php/message.php?id={$row["messageID"]}">{$row["subject"]}</a></td>
            <td>{$row["username"]}</td>
            <td>{$row["numOfComments"]}</td>
        </tr>
";
    }
    echo "  </tbody>
</table>";
?>

There are a few other corrections as well. For instance, your link doesn t have a closing </a> tag, and your script may be vulnerable to XSS attacks. And then there s the nested while-loop, which was causing unnecessary complication and bugs.

Answer 2

The problem i see is that you are nesting your while loops. Please see: http://www.php.net/manual/en/control-structures.while.php#52733

用一张纸张发言。

Answer 3

这是这样做的最佳方式(我认为):

$sql = "
SELECT message.*, user.*, commentsCounter.comments FROM message

INNER JOIN user
ON user.userID = message.userID

INNER JOIN ( SELECT COUNT( 1 ) as comments, comment.messageID FROM comment GROUP BY comment.messageID ) commentsCounter
ON commentsCounter.messageID = message.messageID

ORDER BY message.messageID DESC
";

Answer 4

为什么不像以下那样做?

$query = "SELECT comment FROM message where userId = $userId";

$result = mysql_query($query);

$numberOfComments = mysql_num_rows($result);

echo "$numberOfComments";

至少我相信,你们会重新寻找什么?

手册对此做了说明:http://php.net/manual/en/Function.mysql-num-rows.php rel=“nofollow”>mysql_num_rows(:

我sql_num_rows——因此获得多少行文

Answer 5

http://www.w3schools.com/sql/sql_func_count.asp

$result3=mysql_query("COUNT(*) FROM comment") or die(mysql_error());
$row=mysql_fetch_array($result3);
$numcomments = $row[0];
echo $numcomments;

我在几个地点使用(用不同的表格名称)。

Answer 2

假设你想对一个特殊职位发表评论,就象这样做:

$result3=mysql_query("SELECT DISTINCT message.messageID FROM comment ") or die(mysql_error());
$row=mysql_fetch_array($result3);
$numcomments = count($row);
echo $numcomments;

Answer 2

友情链接