0

I'm trying to recreate a conversation list like whatsapp where it displays the contact name of the person you are having a conversation with and also the time the message was posted and also the last message regardless of if it was from you or the contact that posted the message, In other words I need to always see the contact name and the last time the message was posted and the last message which could have been sent by me or the recipient, I also need to bring back the image of the other user from the users table.

USERS TABLE

 userid |  first_name  |       url          |
 ---------------------------------------------
  101   |    name1     | www.image_url1.jpg |
  102   |    name2     | www.image_url2.jpg |
  103   |    name3     | www.image_url3.jpg |
  104   |    name4     | www.image_url4.jpg |

MESSAGES TABLE

MessageId  |  userid  | senderid | message  | timestamp |
-----------------------------------------------------------
    1      |    101   |    102   | message1 |  1234567  |
    2      |    102   |    101   | message2 |  1234578  |
    3      |    101   |    102   | message3 |  1235679  |
    4      |    103   |    101   | message4 |  1256379  |

What i'm trying to query from the database if my userid = 101 is the last message and timestamp from either me or the contact along with the contacts userid, first_name and url

  userid  |  first_name |        url         |  message  |  timestamp |
 ---------------------------------------------------------------------
    102   |    name2    | www.image_url2.jpg |  message3 |   1235679  |
    104   |    name4    | www.image_url4.jpg |  message4 |   1227878  |
 ----------------------------------------------------------------------

1) I want to extract the userid, first_name and url of the contact from the users table
2) And extract the newest message and timestamp from the messages table from the contact

Any help would be great...

TRicks
  • 35
  • 9

2 Answers2

0

Add a LIMIT 1 at the end of the query and see if it helps.

petemir
  • 167
  • 9
  • 1
    This is not an answer... Answers do not end with a question mark – Wes Foster Apr 24 '16 at 00:28
  • No because limit 1 will only bring back one record, I need to see each conversation but only the last message of each conversation and the other users name – TRicks Apr 24 '16 at 00:32
  • You're right. I'm not able to find where's the error. Maybe sample data and/or output would help. Btw, note that the distinct and group by on mems.userid (the 1 in the clause I believe) is a bit redundant (you should use just [distinct](http://stackoverflow.com/a/164544/1794490), and also I don't understand why you group by mems.url (the 3 in the clause). – petemir Apr 24 '16 at 01:02
0

You have to know the ID of the last message sent/received between the two users, so you could get the details of the newest message between them. Your query didn't indicate that, which should be in your where clause.

Amir
  • 98
  • 1
  • 1
  • 5
  • I do have a messageid field but it's a primary key so each entry gives a incremented value, so should i include another messageid which shares the messageid with each message sent in the same conversation and later group by on that? How would a query like that look? – TRicks Apr 24 '16 at 00:37
  • No. Use that primary key. Keep track of the ID of the last message you received and use it while querying for new messages by telling MySQL to get messages with ID higher than that id. This you will do each time you are fetching new messages. You understand? – Amir Apr 24 '16 at 01:19
  • yes that makes sense but how would i bring in the top message of each conversation? What SQL code to grab the top based on a sub query maybe? – TRicks Apr 24 '16 at 02:00
  • [Look here](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/), the "Do it with user variables on MySQL" section; you'll put a row number to each record and then you'll grab the records with row number equal 1. Adapt that code and you'll be able to get what you ask in the comment.. – petemir Apr 24 '16 at 03:36
  • @tricks, by "top message", you mean only the last message between two users? If yes, your initial query should work. Just order in descending order and limit to 1. – Amir Apr 24 '16 at 10:25
  • Yes correct but if I do a limit 1 it's only going to bring back 1 record which is incorrect if there are multiple conversations. – TRicks Apr 24 '16 at 16:16
  • You have to go with keeping track of the highest chat ID of the conversation between the users, save it in a hidden form input (maybe) and use it to get chats between the users with ID higher than it. Update the ID each time you get new messages. – Amir Apr 24 '16 at 21:28