mysql

How to show all records from the table having the same title as the selected ID


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?


Solution

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