User 1 and 2 has a form.
<form action = "" method = "post">
<input type = "text" name = "msg" placeholder = "message...">
<input type = "submit" name = "submit" value = "send">
</form>
And I have this table on my database:
AccID | FROM | TO | MESSAGE
The PHP Code that sends this message to the database is quite simple too:
$sql = "INSERT INTO messages (from, to, message) VALUES ('$userid','$to','$msg')";
$q = mysqli_query($con,$sql);
Now if user1 would send "Hello User 2!", it would then register to the database with user1's id to the from table, and then user2's id to the to right? And if user2 sends, user2's id would be in the from and user1's on the to.
Given this sample Table now: (Let's say other users starts to message too)
AccID | FROM | TO | MESSAGE
1 user1 user2 hello user2!
2 user2 user1 hi watsup?
3 user3 user1 hi user1
4 user4 user3 hey user3
How can I select and view user1 and user 2's conversation only? I tried "SELECT * FROM messages WHERE from,to = 'user1' AND from,to = 'user2'"; but somehow didn't worked.
Try below query. Assuming that AccID is unique for each record, I have used ORDER BY AccID so that messages can be fetched in their insertion order.
SELECT * FROM messages
WHERE (`from` = 'user1' AND `to` = 'user2') OR (`from` = 'user2' AND `to` = 'user1')
ORDER BY AccID;
Another option (For Better performance):
SELECT * FROM messages
WHERE `from` IN ('user1','user2') AND `to` IN ('user1','user2')
ORDER BY AccID;
NOTE: from and to are SQL reserved keywords. So kindly avoid using them as names of columns in your table.
Instead try using some meaningful names that do not clash with any reserved keywords and also increase readability, for example, sent_from_user, sent_to_user.