0

I've been reading this post Creating a threaded private messaging system like facebook and gmail

It explains everything amazingly well but the queries don't work for me. I get an error at line 1 where it says "top 1" . :(

Here are the scenarios I'm trying to reach:

  1. 5 people are talking to member1. Member 1 and the other 5 members have threads, so that's 5 threads in total because there are 5 conversations. I need to fetch the latest reply from each of the 5 threads and the username of the person that left the last reply.

  2. Once the message is open, show the replies.

I'm guessing the post that i was reading is efficient and will work well with thousands and hundred of thousands of threads/replies. If it'll be slow, let me know if anyone can point me to an article that does it correctly. I am not too good with MYSQL, still learning.

I am using this table setup

enter image description here

Community
  • 1
  • 1
Darius
  • 1,613
  • 5
  • 29
  • 58

1 Answers1

1

TOP is Transact-SQL syntax—e.g. for MS SQL Server and Sybase. The equivalent syntax in MySQL is LIMIT (which goes at the end of a SELECT command):

select
  M.message_id
, M.sent_datetime
, M.title
, M.message_text
, S.user_id
, S.user_name
-- and anything else you want...
from MESSAGE M inner join USER S
  on M.sender_user_id = U.user_id
where M.reply_to_message_id = @ThreadRootMessageID
order by
  M.sent_datetime desc
limit 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Do you know how I would get the names of the two participants in the thread? so I can say "messages between You and username" ? – Darius Jul 08 '12 at 02:39