Have Geek Brain

Functionality "Inbox" with MYSQL

So I'm writing a simple personal Inbox messaging system.

My table schema looks like this:

  • MESSAGEID
  • message
  • sender id
  • recipient id
  • sent date
  • read (0 = no, 1 = yes)

I can show messages to a user by associating his user id with receiverID. However, I also want to show the messages sent by them in the mailbox to the user.

For example, his mailbox should show:

Darth Vader - 3 New messages
Luke - 0 new messages (0 because either I read all of them OR I sent him a message and he didn't reply).

But I can only think of

Darth Vader - 3 New posts.

Can I get help on how I can make this SQL call?

EDIT: clear up the confusion

I'm not Luke and Dart. I received 3 New Messages from Darth and I sent a message to Luke.

EDIT ** Basically I want to make the mailbox like an SMS app where you can see the sms you just sent to your friends sms list.

+2


source to share


1 answer 


SELECT users.username, count(messageID) AS unread
FROM users, messages
WHERE messages.senderID=<USER ID>
AND messages.receiverID=users.userID
AND messages.read=0


To make sure that I am correct, this query will show all the users to whom I have sent a message and will count the number of those messages that were not read.

0


source






More articles:


All Articles