Система личных сообщений с диалогами с помощью Mysql и PHP

Большинство CMS и форумов используют довольно устаревшую систему для переписки. Уже давно время поставило свои стандарты, поэтому необходимо идти нога в ногу с прогрессом.

В этом посте объясняется, как спроектировать систему обмена личными сообщениями в виде диалогов с использованием PHP и MySQL. Мы будем использовать Класс для работы с базой данных MySQL.

Перед началом хочу объяснить, что в данной статье будет описана структура и все методы работы с базой данный, которые включают исходные коды SQL запросов. Здесь не будет рассматриваться вопрос верстки дизайна переписки, поскольку это дело вкуса. Описанные здесь основы дадут возможность реализовать на своем сайте довольно неплохую систему переписки. Достаточно добавить асинхронную загрузку с помощью jQuery и будет вам переписка в виде диалогов аналогичная VK или Facebook.

Сразу напишу, что здесь используется моя личная реализация системы переписки. Здесь нет вложенных циклов и другой абсурдной реализации, которая описана на других сайтах. У меня все отлично работает даже при высокой нагрузке.

Основные возможности системы переписки:

– Вывод диалогов получателей и отправителей сообщений.

– Возможность визуально пересмотреть, прочитано сообщение или нет.

– Просмотр количества непрочитанных сообщений.

– Удаление сообщений и диалогов индивидуально для каждого пользователя.

При создании системы переписки мы подробно разберем:

– Создание структуры базы данных.

– Создание диалога и отправка сообщения.

– Вывод диалогов пользователя и личных сообщений.

– Удаление диалога.

Чтобы реализовать систему обмена сообщениями, необходимо создать 3 таблицы: Users, Conversation и Messages.

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

CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(32) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
)

Таблица с диалогами должна иметь следующие поля:
id – Уникальный идентификатор диалога.

first – ID первого участника переписки.

second – ID второго участника переписки.

last_message_id – ID последнего сообщения в переписке.

sender – ID отправителя (необходимо знать, чтобы устанавливать флаг непрочитанных сообщений).

first_delete – Флаг удаления диалога для первого участника переписки.

second_delete – Флаг удаления диалога для второго участника переписки.

unread – Количество непрочитанных сообщений.

По желанию к созданию таблиц можно добавить связи между таблицами с помощью внешних ключей (FOREIGN KEY), тем самым, обеспечить ссылочную целостность приложения. В нашем же примере будем придерживаться принципа проверки валидности данных перед отправкой.

CREATE TABLE `conversation` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `first` int(11) NOT NULL,
    `second` int(11) NOT NULL,
    `last_message_id` int(11) NOT NULL,
    `sender` int(11) NOT NULL,
    `first_delete` int(1) NOT NULL,
    `second_delete` int(1) NOT NULL,
    `unread` int(11) NOT NULL,
    PRIMARY KEY (`id`)
)

Для логичной структуры и взаимосвязи таблиц, последняя таблица Messages должна содержать следующие поля:

id – Уникальный идентификатор сообщения.

conv_id – ID диалога.

sender – ID отправителя.

addressee – ID адресата.

readed – Флаг, который определяет, прочитанное сообщение (1) или нет (0).

sender_delete – Флаг удаления сообщения в отправителя.

addressee_delete – Флаг удаления сообщения в получателя.

message – Текст сообщения.

date – Дата и время отправки.

Соответственно, если один пользователь удаляет сообщение, в собеседника сообщение не удаляется.

CREATE TABLE `messages` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `conv_id` int(11) NOT NULL,
    `sender` int(11) NOT NULL,
    `addressee` int(11) NOT NULL,
    `readed` int(1) NOT NULL,
    `sender_delete` int(1) NOT NULL,
    `addressee_delete` int(1) NOT NULL,
    `message` text,
    `date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`)
)

Благодаря такой реализации мы создали вполне логическую структуру построения системы личных сообщений в виде диалогов.

Будем следовать по порядку. Пользователь хочет написать сообщение другому пользователю, соответственно, при этом должна осуществляться следующая логика:

– Проверка на существование диалога между пользователями.

– Если диалог не существует – создаем диалог и добавляем сообщение. В противном случае просто добавляем сообщение.

Перед данной логикой, на всякий случай, проверяем, не отправляет ли пользователь сообщение сам себе.

$user_one = 1;
$user_two = 4;
$message = 'Hello My Friend';
if ($user_one != $user_two) {
    // Поиск диалога
    $row_conversation = $db->super_query("
    SELECT
        `id`
    FROM
        `conversation`
    WHERE
        (`first` = '{$user_one}' AND `second` = '{$user_two}')
            OR
        (`first` = '{$user_two}' AND `second` = '{$user_one}')");
    // Если диалог не создан ранее - создаем
    if (!isset($row_conversation['id'])) {
        $db->query("
        INSERT INTO `conversation`
            (`first`,          `second`,         `last_message_id`,    `sender`,         `first_delete`,    `second_delete`,    `unread`)
        VALUES
            ('{$user_one}',    '{$user_two}',    '0',                  '{$user_one}',    '0',               '0',                '0')");
        // ID последнего запроса
        $last_conversation_id = $db->insert_id();
    } else {
        $last_conversation_id = $row_conversation['id'];
    }
    // Добавляем сообщение
    $row_messages = $db->query("
    INSERT INTO `messages`
        (`conv_id`,                    `sender`,         `addressee`,      `readed`,    `sender_delete`,    `addressee_delete`,    `message`,       `date`)
    VALUES
        ('{$last_conversation_id}',    '{$user_one}',    '{$user_two}',    '0',          '0',               '0',                   '{$message}',    '".date("Y-m-d H:i:s")."')");
    // Обновляем таблицу с диалогом
    $db->query("
    UPDATE
        `conversation` `C`
    SET
        `C`.`last_message_id`    = '{$db->insert_id($row_messages)}',
        `C`.`sender`             = '{$user_one}',
        `C`.`unread`             = (SELECT
            COUNT(*)
            FROM
                `messages` `M`
            WHERE
                `M`.`conv_id` = '{$last_conversation_id}' AND `M`.`readed` = '0' AND `M`.`sender` = '{$user_one}')
    WHERE
        `id` = '{$last_conversation_id}'");
}

Следующим шагом мы выведем список всех диалогов пользователя. Для этого мы создадим запрос, в котором ищем все записи, в которых фигурирует данный пользователь (в данном случае с ID 1). Также сортировка будет происходить по полю непрочитанных сообщений, поэтому они всегда будут наверху и навиду:

$user_one = 1;
// Вывод всех диалогов пользователя
$sql_result = $db->query("
SELECT
    `U`.`id` as `userId`,
    `U`.`username`,
    `C`.`id` as `convId`,
    `C`.`sender`,
    `C`.`unread`,
    `M`.`message`,
    `M`.`date`
FROM
    `users` `U`,
    `conversation` `C`
LEFT JOIN
    `messages` `M` ON (`C`.`last_message_id` = `M`.`id`)
WHERE
    (`C`.`first` = '{$user_one}' OR `C`.`second` = '{$user_one}')
    AND
    CASE
        WHEN `C`.`first` = '{$user_one}'
            THEN `C`.`second` = `U`.`id` AND `C`.`first_delete` = '0'
        WHEN `C`.`second` = '{$user_one}'
            THEN `C`.`first` = `U`.`id` AND `C`.`second_delete` = '0'
    END
ORDER BY
    `C`.`unread`
DESC");
// Перебираем результат
if (!$db->num_rows($sql_result)) {
    echo 'Диалогов нет!';
} else {
    // Перебираем результат
    echo '
    <table border="1">
        <tr>
            <td>ID диалога</td>
            <td>ID собеседника</td>
            <td>Имя собеседника</td>
            <td>Последнее сообщение</td>
            <td>Дата сообщения</td>
            <td>Непрочитанных сообщений</td>
        </tr>';
    while ($row = $db->get_row($sql_result)) {
        echo '
        <tr>
            <td>'.$row['convId'].'</td>
            <td>'.$row['userId'].'</td>
            <td>'.$row['username'].'</td>
            <td>'.$row['message'].'</td>
            <td>'.$row['date'].'</td>
            <td>'.($row['sender'] != $user_one ? $row['unread'] : 0).'</td>
        </tr>';
    }
echo '
    </table>';
}

После того, как мы отправили сообщение, просмотрели список диалогов, нам необходимо увидеть все сообщение с определенным пользователем, что полностью реализует следующий код:

$user_one = 1;
$user_two = 4;
if ($user_one != $user_two) {
    // Поиск диалога
    $row_conversation = $db->super_query("
    SELECT
        `id`,
        `unread`
    FROM
        `conversation`
    WHERE
        (`first` = '{$user_one}' AND `second` = '{$user_two}')
            OR
        (`first` = '{$user_two}' AND `second` = '{$user_one}')");
    // Если диалог не создан ранее
    if (!isset($row_conversation['id'])) {
        echo 'Сообщений с данным пользователем нет!';
    } else {
        $sql_result = $db->query("
        SELECT
            `id`,
            `date`,
            `message`,
            `sender`
        FROM 
            `messages`
        WHERE
            `conv_id` = '{$row_conversation['id']}'
            AND
            CASE
                WHEN `sender` = '{$user_one}'
                    THEN `sender_delete` = '0'
                WHEN `addressee` = '{$user_one}'
                    THEN `addressee_delete` = '0'
            END
        ORDER BY 
            `id`
        ASC");
        if (!$db->num_rows($sql_result)) {
            echo 'Сообщений с данным пользователем нет!';
        } else {
            // Перебираем результат
            echo '
            <table border="1">
                <tr>
                    <td>ID сообщения</td>
                    <td>Дата</td>
                    <td>Сообщение</td>
                    <td>Статус</td>
                </tr>';
            while ($row = $db->get_row($sql_result)) {
                echo '
                <tr>
                    <td>'.$row['id'].'</td>
                    <td>'.$row['date'].'</td>
                    <td>'.$row['message'].'</td>
                    <td>'.($row['sender'] == $user_one ? 'Отправлено' : 'Принято').'</td>
                </tr>';
            }
            echo '
            </table>';
        }
    if ($row_conversation['unread'] != '0') {
        // Обновляем флаг просмотров сообщений
        $db->query("
        UPDATE LOW_PRIORITY
            `messages`
        SET
            `readed` = '1'
        WHERE
            `conv_id` = '{$row_conversation['id']}'");
        // Обновляем таблицу с диалогом
        $db->query("
        UPDATE LOW_PRIORITY
            `conversation`
        SET
            `unread` = '0'
        WHERE
            `id` = '{$row_conversation['id']}'");
        }
    }
}

Думаете это все? Ошибаетесь. Остается еще 2 важных функции, а именно удаления конкретного сообщения и диалога. Начнем с удаления сообщения:

$user_one = 1;
$delete_id = 5; 
// Проверяем существование сообщения
$sql_result = $db->super_query("
SELECT
    `id`
FROM
    `messages`
WHERE 
    `id` = '{$delete_id}' AND (`sender` = '{$user_one}' OR `addressee` = '{$user_one}')");
if (!isset($sql_result['id'])) {
    echo 'Сообщение не существует!';
} else {
    $db->query("
    UPDATE
        `messages`
    SET
        `sender_delete` =
                CASE `sender`
                    WHEN '{$user_one}'
                        THEN '1'
                    ELSE
                        `sender_delete`
                END,
        `addressee_delete` = 
                CASE `addressee`
                    WHEN '{$user_one}'
                        THEN '1'
                    ELSE
                        `addressee_delete`
                END
    WHERE
        `id` = '{$delete_id}'");
}

Для удаления диалога выполняем код:

$user_one = 1;
$conv_id = 1; 
// Проверяем существование диалога
$sql_result = $db->super_query("
SELECT
    `id`
FROM
    `conversation`
WHERE 
    `id` = '{$conv_id}' AND (`first` = '{$user_one}' OR `second` = '{$user_one}')");
if (!isset($sql_result['id'])) {
    echo 'Диалог не существует!';
} else {
    $db->query("
    UPDATE
        `messages` 
    SET
        `sender_delete` =
                CASE `sender`
                    WHEN '{$user_one}'
                        THEN '1'
                    ELSE
                        `sender_delete`
                END,
        `addressee_delete` = 
                CASE `addressee`
                    WHEN '{$user_one}'
                        THEN '1'
                    ELSE
                        `addressee_delete`
                END
    WHERE
        `conv_id` = '{$conv_id}'");
    // Обновляем таблицу диалогов
    $db->query("
    UPDATE
        `conversation` 
    SET
        `first_delete` =
                CASE `first`
                    WHEN '{$user_one}'
                        THEN '1'
                    ELSE
                        `first_delete`
                END,
        `second_delete` = 
                CASE `second`
                    WHEN '{$user_one}'
                        THEN '1'
                    ELSE
                        `second_delete`
                END
    WHERE
        `id` = '{$conv_id}'");
}

Самое важное, а это структура базы данных, было изложено. При проектировании вашей системы сообщений, а это будет именно ваша, потому что для каждого проекта необходимый свой функционал, вам необходимо будет перерабатывать и дорабатывать код. В данной статье были упущены некоторые детали, например, обновления полей таблицы диалога при удалении сообщения, отображение аватарок при выводе сообщений, сортировка диалогов и другое, но если все описывать, так вам не будет над чем работать, поэтому усовершенствуйте свои знания в области запросов SQL и проектируйте свои, намного быстрее и качественные продукты.

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

Для реализации необходимо дополнительно иметь идентификаторы стилей start_id и last_id, которые обозначают ID первого и последнего сообщения, которые будут отображены пользователю. Данные идентификаторы нужны для того, чтобы подгружать предыдущие сообщения, основываясь на start_id (10 записей до до данного ID), и новые сообщения, ID которых больше за last_id. Использование данной техники позволит безошибочно выводить порядочность сообщений.

Добавить комментарий Отменить ответ