CODEWITHSUNDEEP
sqlmysqli
Jesperbook
Jesperbook

Reputation: 151

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 is the one who sends the message

til_id it is the person receiving the message.

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

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

and i have try its here:

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

and i have try its here again

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

Upvotes: 0

Views: 120

Answers (2)

Sean
Sean

Reputation: 12433

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"];

Upvotes: 1

user565869
user565869

Reputation:

Your GROUP BY clause is collapsing records with the same title. Add another field to the GROUP BY clause, or discard it - it's not currently being used to feed an aggregation anyway.

Upvotes: 0

Related Questions