Система приватних повідомлень з діалогами за допомогою PHP та Mysql

Денис Гущин 29 червня 2021 о 13:10 3

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

У цій публікації пояснюється, як спроектувати систему обміну приватними повідомленнями у вигляді діалогів з використанням PHP та Mysql. Ми використовуватимемо Клас для роботи з базою даних MySQL.

Система приватних повідомлень з діалогами за допомогою PHP та Mysql

Перед початком хочу пояснити, що будуть описані структура і всі методи роботи з базою, які включають вихідні коди SQL запитів. Тут не буде розглядатися питання верстки дизайну листування, оскільки це справа смаку. Описані тут основи дозволять реалізувати на своєму сайті досить непогану систему листування. Достатньо додати асинхронне завантаження за допомогою jQuery і буде вам листування у вигляді діалогів аналогічне 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. Використання цієї техніки дозволить безпомилково виводити послідовність повідомлень.