SQL DISTINCT WITH TWO VARIABLES-CodePudding

I am creating a messaging site, with php and ajax.

there is a problem on getting conversations.

The problem is that whenever two user chat between them there is two distinct rows with id;

example

A and B is chattting and only written 4 messages to each other

messages database is like this

id senderid recieverid 
1   a.id      b.id
2   b.id      a.id
3   b.id      a.id
4   a.id      b.id

My aim is getting records with this code

SELECT DISTINCT senderid, recieverid from messages WHERE (senderid = '".$pageowner."' OR recieverid='".$pageowner."')

the $pageowner is the user who logged in;

with this method i get two same conversations

a<->b and b<->a

and the code gives me two conversations on the page i want to only get one result;

my whole php code is like this

if(isset($_POST['id'])){
    include 'config.php';
    $pageowner = $_POST['id'];
    $sql = "SELECT DISTINCT senderid, recieverid from messages WHERE (senderid = '".$pageowner."' OR recieverid='".$pageowner."')";
    $result = mysqli_query($connect, $sql);
    $conversations = mysqli_fetch_all($result);

    $output = "";
    foreach($conversations as $conversation){     
        $senderonmessages = $conversation[0];
        $recieveronmessages = $conversation[1];
        if($pageowner == $senderonmessages){
            $convname = $recieveronmessages;
        }else{
            $convname = $senderonmessages;
        }
        $sql = "SELECT id, name, surname, userimage FROM users WHERE id='".$convname."' ORDER BY id" ;
        $resconv = mysqli_query($connect, $sql);
        $user = mysqli_fetch_assoc($resconv);
        $output .= '
                    <div  id='.$user['id'].'>
                        <img src="'.$user['userimage'].'">
                        <span id="status"></span>
                        <div >
                            <h4><a href="">'.$user['name'].' '.$user['surname'].'</a></h4>
                            <p>Axirinici yazdigim mesaj <span id="time">10:34 AM</span></p>
                        </div>
                        <div >
                            <span id="notif">1</span>
                            <i ></i>
                        </div>
                    </div>';
    }
    echo $output;
 }

CodePudding user response：

You need to fix your sql injection problem first. If you don't do that, you won't have any data in your database to worry about because somebody will drop it.

https://www.php.net/manual/en/security.database.sql-injection.php

Also, your query gives you exactly what you are asking for: DISTINCT senderid, recieverid

For a solution to your question, I'd create a view that is something like:

create view conversations as 
SELECT senderid, recieverid FROM messages GROUP BY 1, 2
UNION
SELECT receiverid, senderid FROM messages GROUP BY 1, 2

Then, you can select from this view and get what you want.

As mentioned in the comment the UNION will give you distinct so you don't even need that...