This is how I build a messaging system for my website:
fra_id
is the one who sends the message
til_id
it is the person receiving the message.
The problem 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 show all the messages in the same conversation, i.e. all that have the same title as the selected ID?
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"];