There are table messages and fields: id - message ID author - Message author poluchatel - Message recipient text - Message text

There are 4 posts:

id - 1 author - Kolya poluchatel - Julia text - Hi Julia!

id - 2 author - Petya poluchatel - Julia text - Hi Julia!

id - 3 author - Peter poluchatel - Julia text - Hi Julia!

id - 4 author - Vasya poluchatel - Julia text - How are you?

I need to display all messages, where (poluchatel is Yulia), all authors who sent her a message, and only one of their last messages! Here are the messages that should be displayed:

id - 1 author - Kolya poluchatel - Julia text - Hi Julia!

id - 3 author - Peter poluchatel - Julia text - Hi Julia!

id - 4 author - Vasya poluchatel - Julia text - How are you?

  • With a conclusion, I solved the problem, one more question, I count the number of messages in the output (the same 3 messages) $ db); $ sum_messages = mysql_fetch_array ($ result_messages); echo "YOU have $ sum_messages [0] MESSAGES"; only with such a request gives the number of all counted messages that were sent to Yulia, and I need to count the number as in the request above!) When exiting, it should be like this: YOU 3 MESSAGES, that is, the message (id-1 id-3 id-4) amf1k

2 answers 2

The request is the simplest ... And for some reason I have a feeling that my answer is only the beginning of the discussion. You will begin to emerge all sorts of "but", "and if", etc.

SELECT * FROM `messages` WHERE `poluchatel` = 'Юля' GROUP BY `author` ORDER BY `id`
  • Sorry, I made a little mistake with the code, instead of id - 2 author - Peter poluchatel - Julia text - Hello Julia! must be id - 2 author - Vasya poluchatel - Julia text - Hi Julia! - amf1k
  • if I write this is the query SELECT * FROM messages WHERE poluchatel = 'Julia' GROUP BY author ORDER BY id I’ll pull out the first messages of all authors from the database, but I need the last messages of all the authors! that is, Vasya wrote 2 messages, and when he exits, he displays his message id - 4, and with such a request I will display id - 2 - amf1k

Learn SQL

 SELECT * FROM messages WHERE receiver = 'name' GROUP BY author ORDER BY id DESC
  • DESC is put in order to sort in the reverse order! - amf1k
  • With the calculation, I solved the question, the last question, how to delete messages, for example: here's the code DELETE FROM messages WHERE author = Вася How to delete all messages from Yulia that Vasya sent, that is, how to delete 2 messages at once, this can be done through LIMIT (DELETE FROM messages WHERE author = Вася LIMIT 2), and if LIMIT is unknown? - amf1k
  • DELETE FROM messages WHERE author = 'Vasya' AND receiver = 'Julia' - Ukeo