Intereting Posts

Состояние MySQL mysqli больше или меньше ошибки символа Как подключить выпуск разработчика Arduino Uno Wifi к PHP Как суммировать значение времени в массиве PHP не может читать сеансы из общего ресурса NFS Как изменить eregi PHP на preg_match Как создать маршрут в symfony 2, который отображает внешний URL? Доступ запрещен для публикации CURL Функция PHP для получения субдомена URL-адреса Symlinked MAMP Folder Causeing 403 Запрещено PHP. Получите md5 удаленного файла? Когда использовать класс или функцию в PHP Html / PHP – Форма – ввод как массив Как повторно использовать экземпляр браузера Dusk test? Что случилось с моей контактной формой AJAX? (php mail ()) максимальная длина сеанса php и файлов cookie

Я мог бы использовать некоторую помощь с моей командой SQL

У меня есть таблица базы данных, называемая «mesg» со следующей структурой:

receiver_id | sender_id | сообщение | временная метка | читать

Пример:

2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than ghoti' | yearsago | 1 *(«seen it)* 2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than fish' | now | 1 *(«seen it)* 6 *(«nice girl)* | 2 *(«me)* | 'Hey, wanna go fish?' | yearsago+1sec | 0 *(«she hasn't seen it)*

Это довольно сложная вещь, которую я хочу достичь.

Я хочу получить: последнее сообщение (= ORDER BY time DESC) + «имя контакта» + время для каждого «беседы».

Контактное имя = uname WHERE uid = 'ID контакта (имя пользователя находится в другой таблице)
Contact ID = if (sessionID * («me) * = sender_id) {receiver_id} else {sender_id}
Разговор – это me = receiver OR me = отправитель

Например:

 From: **Bas Kreuntjes** *(« The message from bas is the most recent)* Hey $py, How are you doing... From: **Sophie Naarden** *(« Second recent)* Well hello, would you like to buy my spam? ... *(«I'll work on that later >p)* To: **Melanie van Deijk** *(« My message to Melanie is 3th)* And? Did you kiss him? ...

Это грубый результат.

ВОПРОС: Может кто-нибудь помочь мне настроить хорошую команду SQL. Это будет в то время как loup

 <?php $sql = "????"; $result = mysql_query($sql); while($fetch = mysql_fetch_assoc($result)){ ?> <div class="message-block"> <h1><?php echo($fetch['uname']); ?></h1> <p><?php echo($fetch['message']); ?></p> <p><?php echo($fetch['time']); ?></p> </div> <?php } ?>

Надеюсь, мои объяснения достаточно хороши, если нет, скажите, пожалуйста. Пожалуйста, не обращайте внимания на мой английский и голландские имена (я сам голландский). Не стесняйтесь исправить мой английский.

UPDATE1: Лучшее, что у меня есть до сих пор: но я не хочу показывать больше одного разговора … u = таблица пользователя m = таблица сообщений

 SELECT u.uname, m.message, m.receiver_uid, m.sender_uid, m.time FROM m, u WHERE (m.receiver_uid = '$myID' AND u.uid = m.sender_uid) OR (m.sender_uid = '$myID' AND u.uid = m.receiver_uid) ORDER BY time DESC;

Solutions Collecting From Web of "Я мог бы использовать некоторую помощь с моей командой SQL"

Скудно … но выполнимо. Постройте ответ на один кусок за раз.

Построение запроса – шаг за шагом

Учитывая конкретный ссылочный пользователь («я») в примерах, вам нужно найти все сообщения между «мной» и другим человеком. Для каждого такого другого человека вы хотите найти одно сообщение с самой последней отметкой времени.

Запрос 1

Давайте создадим идентификатор беседы, связав все сообщения вместе. Поскольку нас интересует одна сторона («я») за раз, мы можем использовать идентификатор другого человека для идентификации разговора.

 SELECT m.recipient_uid AS conversation, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid = '$myID' UNION SELECT m.sender_uid AS conversation, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.recipient_uid = '$myID'

Это позволяет прокручивать все чаты вместе с одним и тем же идентификатором беседы.

Запрос 2

Теперь вам нужно сгруппировать это по разговору, чтобы найти разговор и самое последнее время; для этого вам не нужны другие столбцы (recipient_uid или sender_uid):

 SELECT conversation, MAX(timestamp) AS max_timestamp FROM (SELECT m.recipient_uid AS conversation, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid = '$myID' UNION SELECT m.sender_uid AS conversation, m.timestamp AS timestamp FROM mesg AS m WHERE m.recipient_uid = '$myID') AS c GROUP BY conversation

Запрос 3

Итак, для каждого разговора мы теперь знаем самую последнюю метку времени. Нам просто нужно присоединиться к этой информации с предыдущим запросом, чтобы получить большинство деталей:

 SELECT c.recipient_uid, c.sender_uid, c.timestamp FROM (SELECT conversation, MAX(timestamp) AS max_timestamp FROM (SELECT m.recipient_uid AS conversation, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid = '$myID' UNION SELECT m.sender_uid AS conversation, m.timestamp AS timestamp FROM mesg AS m WHERE m.recipient_uid = '$myID') AS c GROUP BY conversation ) AS x JOIN (SELECT m.recipient_uid AS conversation, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid = '$myID' UNION SELECT m.sender_uid AS conversation, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.recipient_uid = '$myID' ) AS c ON c.conversation = x.conversation AND c.timestamp = x.max_timestamp

Запрос 4

Поскольку вы сказали, что хотите иметь имена, вы можете расширить это, чтобы присоединиться к таблице пользователей дважды:

 SELECT c.recipient_uid, c.sender_uid, c.timestamp, u1.uname AS recipient, u2.uname AS sender FROM (SELECT conversation, MAX(timestamp) AS max_timestamp FROM (SELECT m.recipient_uid AS conversation, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid = '$myID' UNION SELECT m.sender_uid AS conversation, m.timestamp AS timestamp FROM mesg AS m WHERE m.recipient_uid = '$myID') AS c GROUP BY conversation ) AS x JOIN (SELECT m.recipient_uid AS conversation, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid = '$myID' UNION SELECT m.sender_uid AS conversation, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.recipient_uid = '$myID' ) AS c ON c.conversation = x.conversation AND c.timestamp = x.max_timestamp JOIN user AS u1 ON u1.uid = c.recipient_uid JOIN user AS u2 ON u2.uid = c.sender_uid

Там – это весело. Я бы не хотел писать это за один раз, но создавал кусок за раз, это не слишком устрашающе (хотя это не тривиально).

Тестовая схема

Таблица пользователей

 CREATE TABLE user ( uid INTEGER NOT NULL PRIMARY KEY, uname VARCHAR(30) NOT NULL );

Таблица Mesg

DATETIME YEAR TO SECOND – забавный способ написания TIMESTAMP (в IBM Informix Dynamic Server – где я тестировал это).

 CREATE TABLE mesg ( recipient_uid INTEGER NOT NULL REFERENCES user(uid), sender_uid INTEGER NOT NULL REFERENCES user(uid), message VARCHAR(255) NOT NULL, timestamp DATETIME YEAR TO SECOND NOT NULL, PRIMARY KEY (recipient_uid, sender_uid, timestamp), READ CHAR(1) NOT NULL );

Данные пользователя

 INSERT INTO USER VALUES(2, 'My Full Name'); INSERT INTO USER VALUES(6, 'Her Full Name'); INSERT INTO USER VALUES(3, 'Dag Brunner');

Данные Mesg

 INSERT INTO mesg VALUES(2, 6, 'I like you, more than ghoti', '2008-01-01 00:05:03', 1); INSERT INTO mesg VALUES(2, 6, 'I like you, more than fish', '2011-01-15 13:45:09', 1); INSERT INTO mesg VALUES(6, 2, 'Hey, wanna go fish?', '2008-01-01 09:30:47', 0); INSERT INTO mesg VALUES(2, 3, 'Wanna catch a beer?', '2011-01-14 13:45:09', 1); INSERT INTO mesg VALUES(3, 2, 'Sounds good to me!!', '2011-01-14 13:55:39', 1); INSERT INTO mesg VALUES(3, 6, 'Heading home now???', '2010-12-31 12:27:41', 1); INSERT INTO mesg VALUES(6, 3, 'Yes - on the bus!!!', '2010-12-31 13:55:39', 1);

Результаты 1

 Conv Recv Send When 3 2 3 2011-01-14 13:45:09 3 3 2 2011-01-14 13:55:39 6 2 6 2008-01-01 00:05:03 6 2 6 2011-01-15 13:45:09 6 6 2 2008-01-01 09:30:47

Результаты 2

 Conv Most Recent 3 2011-01-14 13:55:39 6 2011-01-15 13:45:09

Результаты 3

 Recv Send When 3 2 2011-01-14 13:55:39 2 6 2011-01-15 13:45:09

Результаты 4

 Recv Send When Receiver Sender 3 2 2011-01-14 13:55:39 Dag Brunner My Full Name 2 6 2011-01-15 13:45:09 My Full Name Her Full Name

Ничего себе, я не часто получаю часть SQL, которая является этим сложным сразу с первого раза, но в этом случае, помимо поездки по методу receiver_uid vs recipient_uid на первой итерации, она работала правильно в первый раз.

Общее решение

Опуская параметр «кто» (или «я» или «$ myID»), мы можем придумать общее решение для последнего сообщения в любом из разговоров между двумя людьми. Разговор определяется идентификатором участника с более высоким и нижним (или наоборот). В противном случае он очень похож на предыдущий.

 SELECT c.recipient_uid, c.sender_uid, c.timestamp, u1.uname AS recipient, u2.uname AS sender FROM (SELECT conv01, conv02, MAX(timestamp) AS max_timestamp FROM (SELECT m.recipient_uid AS conv01, m.sender_uid AS conv02, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid < m.recipient_uid UNION SELECT m.sender_uid AS conv01, m.recipient_uid AS conv02, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid > m.recipient_uid ) AS C GROUP BY conv01, conv02 ) AS x JOIN (SELECT m.recipient_uid AS conv01, m.sender_uid AS conv02, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid < m.recipient_uid UNION SELECT m.sender_uid AS conv01, m.recipient_uid AS conv02, m.recipient_uid AS recipient_uid, m.sender_uid AS sender_uid, m.timestamp AS timestamp FROM mesg AS m WHERE m.sender_uid > m.recipient_uid ) AS C ON c.conv01 = x.conv01 AND c.conv02 = x.conv02 AND c.timestamp = x.max_timestamp JOIN USER AS u1 ON u1.uid = C.recipient_uid JOIN USER AS u2 ON u2.uid = C.sender_uid;

Результат с образцовыми данными

 Recv Send When Recipient Sender 6 3 2010-12-31 13:55:39 Her Full Name Dag Brunner 2 6 2011-01-15 13:45:09 My Full Name Her Full Name 3 2 2011-01-14 13:55:39 Dag Brunner My Full Name

Это довольно сложная вещь, которую я хочу достичь.

На самом деле, нет.

Вы можете получить время последнего сообщения, просто используя:

 SELECT receiver_id, sender_id, MAX(timestamp) as mtime FROM mesg GROUP BY receiver_id, sender_id

(Вероятно, вы хотите добавить класс where, чтобы фильтровать отправителем / получателем) И затем, чтобы получить фактическое сообщение ….

 SELECT m2.* FROM mesg m2, (SELECT receiver_id, sender_id, MAX(timestamp) as mtime FROM mesg GROUP BY receiver_id, sender_id) ilv WHERE m2.sender_id=ilv.seder_id AND m2.receiver_id=ilv.receiver_id AND m2.timestamp=ilv.mtime;

Это, однако, довольно неэффективно, так как оно открывает 2 курсора на столе, более эффективное решение (и если вы запускаете очень старую версию mysql, единственное решение) – использовать макс concat трюк .

Я оставлю это упражнением для вас.

Я усовершенствовал сценарий Джонатана: Неправда (ОБНОВЛЕНИЕ)

Первый шаг работает:

 SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, sender_uid AS me, receiver_uid AS contact FROM messages WHERE sender_uid = '$me' ) UNION ( SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, receiver_uid AS me, sender_uid AS contact FROM messages WHERE receiver_uid = '$me' ) ORDER BY time DESC

Он дает хороший чистый список, который говорит мне, являюсь ли я отправителем или нет, и кто мой контакт. Но комбинация во второй части [MAX () и GROUP BY] не работает так, как я хочу.

Он не выбирает самую последнюю часть разговора (отправлять или получать не должны иметь значения). Он скорее просто выбирает первые новые ID-встречи. Поэтому MAX () не выполняет свою работу.

 SELECT m.message, m.receiver, m.sender, max(m.time) « doesn't work well... AS time, m.me, m.contact, u.ufirstname FROM( ( SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, sender_uid AS me, receiver_uid AS contact FROM messages WHERE sender_uid = '$me' ) UNION ( SELECT message, receiver_uid AS receiver, sender_uid AS sender, time, receiver_uid AS me, sender_uid AS contact FROM messages WHERE receiver_uid = '$me' ) ORDER BY time DESC) AS m, users AS u WHERE u.uid = m.contact GROUP BY m.contact «This one does not do what we want it to do :( ORDER BY time DESC;

В каком формате время в базе данных должно использовать функцию MAX (). Макс () не выбирает самую последнюю дату, это то, что происходит неправильно.