CODEWITHSUNDEEP
phpmysql
NothingtoSay
NothingtoSay

Reputation: 159

MYSQL query for php web chat message list doesn’t work properly

I have a mysql table like Below:

`id` (message id)
`sender` (username of the person who sent the messages)
`receiver` (username of the person to whome it was sent)
`message` (text content)
`time` (time of the message sent)
`read` (status of the message)

I want to display a list of messages sent by or sent to the logged in user $_session['username'] like Messenger, WhatsApp and so on. But I am not able to do this anyway. I tried this: 

SELECT DISTINCT `sender`, `receiver` FROM `messages` WHERE `sender`='".$user."' OR `receiver`='".$user."' ORDER BY `time` DESC

**The problem is: ** It returns duplicate records and it doesn’t seem to avoid duplicate records which are present in both sender and receiver

One more point is that I need to save the final list in an array and encode it in json like below:

[
{"user":"user1", "message":"hello", "read":true},
{"user":"user2", "message":"hi!", "read":false},
...
]

Please help me to do this. I am using simply PHP & MYSQL

★EDIT:
Somehow I ended in this code:

<?php
include 'DB.php';
header('Content-Type: application/json');

 $con = mysqli_connect($HostName,$HostUser,$HostPass,$DatabaseName);


$user = $_GET['user'];

$Sql_Query = "SELECT DISTINCT `sender`, `receiver` FROM `messages` WHERE `sender`='".$user."' OR `receiver`='".$user."' ORDER BY `time` DESC";

$check = mysqli_query($con,$Sql_Query);
 
if($check){
if(mysqli_num_rows($check) > 0){
        
        $added_user = array();
     while($row=mysqli_fetch_assoc($check)){

         if($user == $row['sender']){
         if(in_array($row['receiver'], $added_user)){

         }else{

  array_push($added_user, $row['receiver']);
         
    }

         }elseif($user==$row['receiver']){
         if(in_array($row['sender'], $added_user)){

         }else{

  array_push($added_user, $row['sender']);
   }

         }

     }
    }else{
        
    }
}else{
}


 

echo json_encode($added_user);
 
 mysqli_close($con);

?>

The only problem till now is, in the array, it returns the user at the place when he sent his first message. Such as,

User1 sent u message 1mins ago
User 2 sent u message 30secs ago
User1 again sent u message 10secs ago

It should sort like {"user1", "user2"} But it returns {"user2", "user1"}

Upvotes: 0

Views: 207

Answers (1)

Nikolay Kornushkov
Nikolay Kornushkov

Reputation: 124

List of messages send by:

SELECT * 
FROM table
WHERE sender = '{$session['username']}'

List of messages send to:

SELECT * 
FROM table
WHERE receiver = '{$session['username']}'

List of messages as chat:

SELECT * 
FROM table
WHERE receiver = '{$session['username']}' OR sender = '{$session['username']}'
ORDER BY time DESC

If you want to know how to convert it to JSON format, you need to give us more information about your framework, php version and so on.

Upvotes: 1

Related Questions