php query function repeating data

I am trying to make a facebook style chat page using php and mysql. But i have a problem with my query result. The chat table looks like this.

And i write this function for getting the message for loged in user.

/*Get Messges*/
public function Get_Messages($toid,$logedInUserID){
   // Here is loged in user id the id is 2
   $logedInUserID = mysqli_real_escape_string($this->db,$logedInUserID);
   // Loged in user id talked with $toid before the id is 3
   $toid = mysqli_real_escape_string($this->db,$toid);
   $logedInUserID = '2';  
   $toid = '3';

   $query = mysqli_query($this->db,"SELECT DISTINCT C.msg_id,C.from_user_id, C.to_user_id, C.message_created_time, C.message_text, U.user_name, U.user_fullname FROM users U, chat C WHERE C.from_user_id='$logedInUserID' AND C.to_user_id = '$toid' ORDER BY C.msg_id ASC LIMIT 20") or die(mysqli_error($this->db));
   while($row=mysqli_fetch_array($query,MYSQLI_ASSOC)) {
        // Store the result into array
        $data[]=$row;
     }
     if(!empty($data)) {
        // Store the result into array
        return $data;
     }
}

and the foreachloop for getting result

<?php 
    $GetAllMessages = $Get->Get_Messages($toUserID,$uid);
     if($GetAllMessages){
         echo '<span class="all_messages">';
      foreach($GetAllMessages as $getMessage){
         $getTexts = $getMessage['message_text']; 
         $getToUID = $getMessage['to_user_id']; 
         $from_to_class= 'you';
         if($getToUID == $uid){
             $from_to_class = 'friend';
         } 
         echo '<div class="messageBox_body"><div class="'.$from_to_class.'">'.$getTexts.'</div></div>';
       }
         echo '</span>';
      } 
?>

So the problem is my query repeating the messages like the following screenshot also the query not showing loged in user messages.

To be a little more descriptive:

$logedInUserID = '2';
$toid = '3';

The query showing $toid = '3'; messages with repeat and not showing $logedInUserID = '2'; from data.

What is the problem in my query:

SELECT DISTINCT C.msg_id,C.from_user_id, C.to_user_id, C.message_created_time, 
C.message_text, U.user_name, U.user_fullname FROM users U, chat C WHERE 
C.from_user_id='$logedInUserID' AND C.to_user_id = '$toid' 
ORDER BY C.msg_id ASC LIMIT 20

Answer

In given query it seems that you are trying to fetch details from two table User and Chat, however you have not written join condition.

Please try this SQL query, with $logedInUserID = ‘2’; $toid = ‘3’;

SELECT DISTINCT C.msg_id, 
                C.from_user_id, 
                C.to_user_id, 
                C.message_created_time, 
                C.message_text, 
                U.user_name, 
                U.user_fullname 
FROM   users U 
       JOIN chat C 
         ON U.id = C.from_user_id 
WHERE  C.from_user_id = '$logedInUserID' 
       AND C.to_user_id = '$toid' 
ORDER  BY C.msg_id ASC 
LIMIT  20

If you want entire chat with both user just use “OR” condition in Sql query with interchanged $logedInUserID = ‘3’; $toid = ‘2’

Advertisement

Answer