php query function repeating data

Question

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

Hi, in given query it seems that your are trying to fetch details from two table User and Chat, however you have not written join condition. — Sonam Tripathi
– Sonam Tripathi, Commented Aug 16, 2018 at 5:24

Sonam Tripathi · Accepted Answer · 2019-05-12 08:08:25Z

1

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'

edited May 12, 2019 at 8:08

answered Aug 16, 2018 at 5:47

Sonam Tripathi

1831 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php query function repeating data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related