CODEWITHSUNDEEP
phpmysqlsql
Nick Pitoniak
Nick Pitoniak

Reputation: 97

Is it possible to combine these two SQL statements into one?


I am using these two MySQL statements to get all messages between two users, but I am running into a pretty big problem when it comes to manipulating the data later in the code since it's not in order by ID. Is there any way to combine these statements AND order the results by ID? 

$sql = "SELECT * FROM messages WHERE sender='" . $username . "' AND receiver='" . $chatPartner . "'"

$sqlTwo = "SELECT * FROM messages WHERE sender='" . $chatPartner . "' AND receiver='" . $username . "'"

Essentially, I need every occurrence of a message between two people, but sorted by ID. As of right now, I am joining the arrays to get my full list, but it's not in order by ID.

Upvotes: 1

Views: 64

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

You could also write a shorter version of @dtj's answer using row constructors.

SELECT *
FROM messages
WHERE (sender, receiver) IN (($username, $chatPartner),($chatPartner, $username))
ORDER BY id DESC

In my opinion it looks a bit nicer in code and makes it more readable, but remember that it doesn't improve performance of execution.

Upvotes: 0

Timothy
Timothy

Reputation: 2003

How about just combine both into 1 query ? 

$sql = "SELECT * FROM messages WHERE (sender=" . $username . " OR sender =". $chatPartner .") AND (receiver=" . $chatPartner . " OR receiver=" . $username .") ORDER BY id"

Upvotes: 0

djt
djt

Reputation: 7535

SELECT * FROM messages 
WHERE (sender='" . $username . "' AND receiver='" . $chatPartner . "'")
OR (sender='" . $chatPartner . "' AND receiver='" . $username . "'")
ORDER BY id DESC

Upvotes: 4

Related Questions