MySQL-如何显示邮件表中的所有记录，而不仅仅是一个 [英] mySQL - how to show all records from the messages table, not just one

问题描述

这就是我为网站构建消息传递系统的方式，这就是我希望两种用途之间的所有对话都位于一个交替的地方，

this is how I build a messaging system for my website and that is how I would like that all conversations between the two uses is a place in alternate several places,

fra_id是发送消息的人

til_id是接收消息的人.

现在的问题是，即使对话中有更多消息，它也只显示一条消息.

What the problem is here and now is that it only shows a message even though there are more messages in the conversation.

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 = ? 
GROUP BY fms_opslagpm.title 
ORDER BY fms_opslagpm.datotid DESC

我该怎么做呢?所有采访都必须放在页面上而不是页面上.

How can I do this all the interviews have a place rather than on the page.

EIDT

$sql = "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 = ? 
GROUP BY fms_opslagpm.title AND fra_id = ? OR til_id = ? 
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"];

EIDT EIDT

EIDT EIDT

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 = ? 
GROUP BY fms_opslagpm.title 
AND (fra_id = ? OR til_id = ?) 
ORDER BY fms_opslagpm.datotid DESC

我已经在这里尝试过了

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 = ? and (fra_id = ? OR til_id = ?)
GROUP BY fms_opslagpm.title  
ORDER BY fms_opslagpm.datotid DESC

我已经在这里再次尝试

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 = ? and (fra_id = ? OR til_id = ?) 
ORDER BY fms_opslagpm.datotid DESC

推荐答案

您的问题是您仅选择

WHERE fms_opslagpm.id = ?

因此，在id上有完全匹配项的情况下，它将仅返回1行.看起来像您在尝试选择与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"];

这篇关于MySQL-如何显示邮件表中的所有记录，而不仅仅是一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！