仅在对话中显示消息

Question

i have make its here before. mySQL - how to show all records from the messages table, not just one

Hello

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.

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 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;
                }

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 - is he / she who sends the message

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

Answer 1

Your issue is that you are selecting only

WHERE fms_opslagpm.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

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

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.

Try -

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

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

You will also need to include 1 more param to your 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"];