仅在对话中显示一条消息 [英] only displays a message in the conversation

问题描述

我以前来过这里. mySQL-如何显示全部消息表中的记录，而不仅仅是一个

你好

这就是我要构建的消息传递系统，该消息传递系统将使用户1和用户2在某处进行对话.

This is how I'm going to build a messaging system which make the user 1 and user 2 has a conversation somewhere.

我自己去了网站，所以所有对话都出现在页面上.

That itself, I go to the site so come all the conversations appear on the page.

问题在于它仅从数据库中发送一条消息.因此，我希望它显示数据库中的所有消息.

the problem is such that it does only one message from the database. Therefore, I would like it to display all messages from the database.

数据库

CREATE TABLE IF NOT EXISTS `fms_opslagpm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fra_id` int(11) NOT NULL,
  `til_id` int(11) NOT NULL,
  `title` varchar(30) NOT NULL,
  `besked` longtext NOT NULL,
  `datotid` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `fms_opslagpm` (`id`, `fra_id`, `til_id`, `title`, `besked`, `datotid`) VALUES
(1, 2, 1, 'hrerherhe', 'hello world ', '2014-04-01 22:25:29'),
(2, 2, 1, 'hrerherhe', 'hej', '2014-04-01 23:51:49');

mysqli/php在这里.

mysqli/php here.

$sql = "
                SELECT fms_bruger.fornavn, fms_bruger.efternavn, fms_opslagpm.id, fms_opslagpm.fra_id, fms_opslagpm.til_id, fms_opslagpm.title, fms_opslagpm.besked 
                FROM fms_bruger INNER JOIN fms_opslagpm ON fms_bruger.id=fms_opslagpm.fra_id 
                WHERE fms_opslagpm.id = ? and fms_opslagpm.fra_id = ? OR fms_opslagpm.til_id = ? 
                GROUP BY fms_opslagpm.title ORDER BY fms_opslagpm.datotid DESC
                ";
                if ($stmt = $this->mysqli->prepare($sql)) { 
                    $stmt->bind_param('iii', $id, $fra_id, $til_id);
                    $id = $_GET["id"];
                    $fra_id = $_SESSION["id"];
                    $til_id = $_SESSION["id"];
                    $stmt->execute();
                    $stmt->store_result();
                    $stmt->bind_result($fornavn, $efternavn, $id, $fra_id, $til_id, $title, $besked);
                    while ($stmt->fetch()) {
                    ?>
                    <tr class="postbox">
                        <td class="beskedinfoBOX">
                            <p>
                                <?php
                                    echo $fornavn . " " . $efternavn;
                                ?>
                            </p>
                        </td>
                        <td>
                            <?php
                            //beskeden.
                            echo $besked;
                            ?>
                        </td>
                    </tr>
                    <?php
                    }
                    $stmt->close();
                }
                else
                {
                    echo 'Der opstod en fejl i erklæringen: ' . $this->mysqli->error;
                }

这就是当我写fra_id = 2和til_id = 1时显示它仍然只是页面内容的方式.因此，请在页面上发送一条消息.

This is how when I write fra_id = 2 and til_id = 1, then shows it is still only the content of the page. So samtidigvæk a message on the page.

fms_opslagpm = fra_id-是发送消息的他/她

fms_opslagpm = fra_id - is he / she who sends the message

fms_opslagpm = til_id-是接收消息的他/她

fms_opslagpm = til_id - is he / she who receives the message

推荐答案

您的问题是您仅选择

WHERE fms_opslagpm.id = ?

因此，它只会返回1行，其中id上有一个完全匹配的行.看起来像您在尝试选择与id

So it will only return 1 row where there is an exact match on the id. It looks like you where trying to also select the rows that have the same title as the row with the id

GROUP BY fms_opslagpm.title

但是，即使您返回的行多于1行，这也会再次将结果折叠为1行.

but even if you returned more than 1 row, this would have collapsed the results into 1 row again.

您需要更改查询以获取行WHERE fms_opslagpm.id = ?的title，然后使用OR选择具有相同title的所有其他行.

You need to change your query to get the title of the row WHERE fms_opslagpm.id = ?, and using OR select all the other rows with the same title.

尝试-

SELECT 
  fms_bruger.fornavn, 
  fms_bruger.efternavn, 
  fms_opslagpm.id, 
  fms_opslagpm.title, 
  fms_opslagpm.besked
FROM fms_bruger 
INNER JOIN fms_opslagpm ON fms_bruger.id=fms_opslagpm.fra_id 
WHERE (
       fms_opslagpm.id = ? 
       OR fms_opslagpm.title = (
                                SELECT fms_opslagpm.title 
                                FROM fms_opslagpm 
                                WHERE fms_opslagpm.id = ?
                                ) 
       ) 
       AND 
       (
       fms_opslagpm.fra_id = ? 
       OR
       fms_opslagpm.til_id = ? 
       )

ORDER BY fms_opslagpm.datotid DESC

请参阅此SQLFiddle示例- http://sqlfiddle.com/#!2/36d534/6

See this SQLFiddle example - http://sqlfiddle.com/#!2/36d534/6

您还需要在bind_param

$stmt->bind_param('iiii', $id, $id1, $fra_id, $til_id);
                $id = $_GET["id"];
                $id1 = $_GET["id"];
                $fra_id = $_SESSION["id"];
                $til_id = $_SESSION["id"];

这篇关于仅在对话中显示一条消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！