У меня есть три основные таблицы:
tblUsers: usrID usrFirst usrLast 1 John Smith 2 Bill Jones 3 Jane Johnson pm_data: id date_sent title sender_id thread_id content 2 2009-07-29 18:46:13 Subject 1 1 111 Message 2! 3 2009-07-29 18:47:21 Another Subject 1 222 Message 3! pm_info: id thread_id receiver_id is_read 1 111 2 0 2 111 3 0 3 222 2 0 4 222 3 0 По сути, я пытаюсь создать почтовый ящик.
Итак, если usrID 2 (Bill Jones) откроет свой почтовый ящик, он увидит, что он 2 непрочитанных (отсюда столбца is_read) (потоки # 111 и # 222).
В принципе, мне нужно знать, как настроить инструкцию SELECT для JOIN всех трех таблиц (связь между pm_data и pm_info приводит к информации о сообщении, а отношение между tblUsers и pm_data приводит к «отображаемому имени» отправителя), чтобы показать самую последнюю (по метке?) нить сверху.
Таким образом, мы увидим что-то вроде этого:
<?php $usrID = 2; ?> <table id="messages"> <tr id="id-2"> <td> <span> From: John Smith </span> <span>2009-07-29 18:47:21</span> </td> <td> <div>Another subject</div> </td></tr> <tr id="id-1"> <td> <span> From: John Smith </span> <span>2009-07-29 18:46:13</span> </td> <td> <div>Subject 1</div> </td></tr> </table>
Надеюсь, это имеет смысл! Спасибо за любую помощь!
EDIT: Вот мой окончательный ответ:
Я взял совет lc и установил связь между двумя таблицами на основе id (добавлен столбец с именем «message_id» на pm_info).
Затем немного измените инструкцию MySQL, чтобы придумать следующее:
SELECT pm_info.is_read, sender.usrFirst as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id FROM pm_info INNER JOIN pm_data ON pm_info.message_id = pm_data.id INNER JOIN tblUsers AS sender ON pm_data.sender_id = sender.usrID WHERE pm_data.date_sent IN(SELECT MAX(date_sent) FROM pm_data WHERE pm_info.message_id = pm_data.id GROUP BY thread_id) AND pm_info.receiver_id = '$usrID' ORDER BY date_sent DESC
Кажется, это работает для меня (пока).
Вам понадобятся два соединения. Что-то вроде следующего должно начать вас (хотя я не понимаю 100% отношений между pm_data и pm_info ):
SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id FROM pm_info INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/ ORDER BY pm_data.date_sent DESC
Я предполагаю, что отношение между pm_data и pm_info является идентификатором потока. Если это не так, вы должны иметь возможность настроить выше все, что вам нужно. Я также сортировал по дате, отправленной здесь, но это не будет поддерживать потоки вместе . Я не уверен, хотите ли вы сохранить их вместе или нет, так как вы сформулировали свой вопрос.
Если вы хотите сохранить потоки вместе , вам понадобится более сложный запрос:
SELECT pm_info.is_read, sender.usrFirst + ' ' + sender.usrLast as sender_name, pm_data.date_sent, pm_data.title, pm_data.thread_id FROM pm_info INNER JOIN pm_data ON pm_info.thread_id = pm_data.thread_id INNER JOIN tblUsers AS sender ON pm_data.sender_id = tblUsers.usrID INNER JOIN (SELECT thread_id, MAX(date_sent) AS max_date FROM pm_data GROUP BY thread_id) AS most_recent_date ON pm_data.thread_id = most_recent_date.thread_id WHERE pm_info.receiver_id = @USER_ID /*in this case, 2*/ ORDER BY most_recent_date.max_date DESC, pm_data.thread_id, pm_data.date_sent DESC
Этот запрос использует подзапрос, чтобы найти самую последнюю измененную дату для каждого потока, а затем сортирует по первому.
Чтобы получить список сообщений для пользователя вместе с тем, кто и когда он его отправил, вы можете использовать следующий запрос:
select s.usrFirst + ' ' + s.usrLast as SenderName, m.Title, m.DateSent, i.IsRead from tblUsers r inner join pm_info i on r.receiver_id = i.receiver_id inner join pm_data m on i.thread_id = m.thread_id inner join tblUsers s on m.sender_id = s.userID where r.usrid = @id
Это использует тот факт, что вы можете присоединиться к таблице для себя (здесь tblUsers отображается дважды: один раз для получателя и снова для отправителя).
Если вы хотите видеть только непрочитанные сообщения, вы можете поместить and i.IsRead = 0 в предложение where .