I have this table that stores messages for the user. It has id(PK), username, message, time, date. When you log in to your account it shows a list of all of your chats. But this DB only shows the messages. Do I have to create another table somehow connected to the first DB or filter in a way that I get the usernames of the people I'm chatting with? What I mean by getting the usernames is that I have this list of the people I have messaged. Is there a way I could get those usernames?
I am sorry if this is not clear. If you need more explanation, tell me right away. This problem is quite complex for me.
By the way, here is the code:
<div id='messages'>
<nav>
<ul id=''>
<?php
$m = new mysqli('localhost', 'root', 'xamppprogram', 'blue_messenger');
if($m === FALSE){
include('./errordb2.php');
die();
}
$sql = "SELECT * FROM messages WHERE username='$username'";
$results = $m->query($sql);
if($results->num_rows > 0){
while($r = $results->fetch_assoc()){
// What to do here?
}
} else {
echo "No new messages.";
}
?>
<ul>
<nav>
</div>
<div id='chat-content'>
You could add another column on the table, like toUser that stores the name of the user you're chatting with. Then you can use a GROUP BY toUser condition in your sql. This would be the easiest solution, but as others have stated in the comments of the question, a relational database would be best.
You would be best to use a prepared statement since user input seems to be used here and would be open to an SQL injection.
References: