julp/README.md

## README.md

      
    Raw
  

              README.md
            
          
    A faire :

renommer pdo.php ?
corriger l'header après sortie sur show.php
remplacer (facultativement) certaines requêtes par des triggers SQL ?
améliorer le marquage des MP lus en prenant en compte la page/date ?
mentionner/documenter que les MP ne sont pas en réalité limité à un expéditeur/destinataire (suffit juste de créer autant de lignes dans mp_meta que de destinataires + l'expéditeur) ?

A tester :

PostgreSQL
les suppressions logique et physique

Ne sont pas fournis :

complétion ajax du pseudo du detinataire
l'inscription et le login

Les scripts sont prévus ainsi :

index.php : affiche la liste des MP
show.php : afficher un MP et y répondre
create.php : créer un nouveau MP
delete.php : suppression logique d'un MP
tables_<SGBD>.sql : structures des tables
cron.php : la suppression physique des MP, qui n'a lieu que si le MP est marqué supprimé par chacun des participants et si le dernier message date d'au moins 7 jours
users.php : autocomplétion (sommaire) du destinataire

Fonctionnalités :

compatible MySQL et PostgreSQL
marquage des messages (non-)lus


## create.php
<?php
session_start();

require 'pdo.php';

const MIN_SUJET_LEN = 3;
const MIN_MESSAGE_LEN = 3;

if (!isset($_SESSION['id'])) {
    http_response_code(403);
    die("L'accès à cette page requiert d'être authentifié");
}

$erreurs = [];
if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (empty($_POST['destinataire'])) {
        $erreurs['destinataire'] = "Champ destinataire non rempli";
    } else {
        $stmt = $bdd->prepare('SELECT id FROM membres WHERE nom = :pseudo');
        $stmt->execute(['pseudo' => $_POST['destinataire']]);
        if (!($destinataire_id = $stmt->fetchColumn())) {
            $erreurs['destinataire'] = "Utilisateur inconnu au bataillon";
        } else if ($destinataire_id == $_SESSION['id']) {
            $erreurs['destinataire'] = "Vous ne pouvez pas vous écrire";
        }
    }
    if (empty($_POST['sujet'])) {
        $erreurs['message'] = "Champ sujet non rempli";
    } else if (mb_strlen($_POST['sujet']) < MIN_SUJET_LEN) {
        $erreurs['sujet'] = sprintf("sujet trop court, au moins %d caractères", MIN_MESSAGE_LEN);
    }
    if (empty($_POST['message'])) {
        $erreurs['message'] = "Champ message non rempli";
    } else if (mb_strlen($_POST['message']) < MIN_MESSAGE_LEN) {
        $erreurs['message'] = sprintf("message trop court, au moins %d caractères", MIN_SUJET_LEN);
    }
    if ($erreurs) {
        echo '<p>Veuillez corriger les erreurs suivantes :</p>';
        echo '<ul><li>', implode('</li><li>', $erreurs), '</li></ul>';
    } else {
        $bdd->beginTransaction();
            $stmt = $bdd->prepare('INSERT INTO mp_sujets(titre, premier_message_auteur_id, dernier_message_auteur_id, premier_message_date, dernier_message_date) VALUES(:titre, :auteur_id_premier_message, :auteur_id_dernier_message, NOW(), NOW())' . ('pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? ' RETURNING id' : ''));
            $stmt->bindValue('titre', $_POST['sujet'], PDO::PARAM_STR);
            $stmt->bindValue('auteur_id_premier_message', $_SESSION['id'], PDO::PARAM_INT);
            $stmt->bindValue('auteur_id_dernier_message', $_SESSION['id'], PDO::PARAM_INT);
            $stmt->execute();
            $sujet_id = 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? $stmt->fetchColumn() : $bdd->lastInsertId();

            $stmt = $bdd->prepare('INSERT INTO mp_meta(sujet_id, membre_id) VALUES(:sujet_id, :membre_id)');
            $stmt->bindValue('sujet_id', $sujet_id, PDO::PARAM_INT);
            $stmt->bindParam('membre_id', $membre_id, PDO::PARAM_INT);
            foreach ([$_SESSION['id'], $destinataire_id] as $membre_id) {
                $stmt->execute();
            }

            $stmt = $bdd->prepare('INSERT INTO mp_messages(sujet_id, auteur_id, message, created_at) VALUES(:sujet_id, :auteur_id, :message, NOW())');
            $stmt->bindValue('sujet_id', $sujet_id, PDO::PARAM_INT);
            $stmt->bindValue('auteur_id', $_SESSION['id'], PDO::PARAM_INT);
            $stmt->bindValue('message', $_POST['message'], PDO::PARAM_STR);
            $stmt->execute();

            $stmt = $bdd->prepare('UPDATE mp_sujets SET premier_message_id = ' . LAST_INSERT_ID_FUNC_NAME . '(), dernier_message_id = ' . LAST_INSERT_ID_FUNC_NAME . '() WHERE id = :sujet_id');
            $stmt->bindValue('sujet_id', $sujet_id, PDO::PARAM_INT);
            $stmt->execute();
        $bdd->commit();
        header('Location: index.php');
        exit;
    }
}
?>

<form method="POST">
    <div>
        <label for="destinataire">À :</label>
        <input autocomplete="off" type="text" id="destinataire" name="destinataire" class="<?= isset($erreurs['destinataire']) ? 'erreur' : '' ?>" value="<?= htmlspecialchars($_POST['destinataire'] ?? '') ?>"/>
        <datalist id="destinataire_list"></datalist>
    </div>
    <div>
        <label for="sujet">Sujet :</label>
        <input type="text" id="sujet" name="sujet" class="<?= isset($erreurs['sujet']) ? 'erreur' : '' ?>" value="<?= htmlspecialchars($_POST['sujet'] ?? '') ?>"/>
    </div>
    <div>
        <label for="message">Message :</label>
        <textarea id="message" name="message" class="<?= isset($erreurs['message']) ? 'erreur' : '' ?>"><?= htmlspecialchars($_POST['message'] ?? '', ENT_NOQUOTES) ?></textarea>
    </div>
    <div>
        <input type="submit" value="Envoyer"/>
    </div>
</form>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script type="text/javascript">
$(
    function () {
        var $datalist = $('#destinataire_list');

        $('input[name="destinataire"]').on(
            'keyup',
            function (e) {
                switch (e.key) {
                    case 'Enter':
                    case 'Backspace':
                    case 'ArrowUp':
                    case 'ArrowDown':
                    case 'ArrowLeft':
                    case 'ArrowRight':
                        return;
                }
                $.post(
                    'users.php',
                    {
                        term: $(this).val()
                    }
                ).done(
                    function (data) {
                        $datalist.empty();
                        for (var i = 0; i < data.length; i++) {
                            console.log(data[i]);
                            $('<option/>').attr('value', data[i]).text(data[i]).appendTo($datalist);
                        }
                    }
                );
            }
        );
    }
);
</script>

## cron.php
<?php
require __DIR__ . '/pdo.php';

const INTERVAL = '7 DAY';

// MySQL : INTERVAL 7 DAY vs PostgreSQL : INTERVAL '7 DAY'
$interval = 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? $bdd->quote(INTERVAL) : INTERVAL;

$bdd->exec(<<<EOS
    SELECT id
        FROM mp_sujets
        WHERE dernier_message_date < INTERVAL {$interval}
            AND NOT EXISTS(
                SELECT 1
                    FROM mp_meta
                    WHERE sujet_id = mp_sujets.id
                        AND NOT m.deleted
            )
EOS
);

## delete.php
<?php
session_start();

require 'pdo.php';

if (!isset($_SESSION['id'])) {
    http_response_code(403);
    die("L'accès à cette page requiert d'être authentifié");
}

if (isset($_POST['selected']) && is_array($_POST['selected'])) {
    $bdd->exec('UPDATE mp_meta SET deleted = TRUE WHERE membre_id = ' . intval($_SESSION['id']) . ' AND sujet_id IN(' . implode(', ', array_map('intval', $_POST['selected'])) . ')');
}
header('Location: index.php');
exit;

## index.php
<?php
session_start();

require 'pdo.php';

if (!isset($_SESSION['id'])) {
    http_response_code(403);
    die("L'accès à cette page requiert d'être authentifié");
}

$page_courante = isset($_GET['page']) ? max(intval($_GET['page']), 1) : 1;

$stmt = $bdd->prepare(<<<'EOS'
    SELECT
        COUNT(*)
    FROM mp_sujets s
        JOIN mp_meta m ON m.sujet_id = s.id
    WHERE m.membre_id = :user_id
        AND ((NOT m.deleted) OR s.dernier_message_auteur_id <> m.membre_id/*:user_id_bis*/)
EOS
);
$stmt->bindValue('user_id', $_SESSION['id'], PDO::PARAM_INT);
// $stmt->bindValue('user_id_bis', $_SESSION['id'], PDO::PARAM_INT);
$stmt->execute();
$nombre_sujets = $stmt->fetchColumn();

if ($nombre_sujets > 0) {
    $datefmt = new IntlDateFormatter('fr_FR', NULL, NULL, NULL, NULL, 'EEEE dd LLLL HH:mm');
    $msgfmt = new MessageFormatter('fr_FR', '{0, plural, =0{aucun message} =1{1 message} other{# messages}}');

    $topics = $bdd->prepare(<<<'EOS'
        SELECT
            s.*,
            pa.nom AS premier_auteur,
            da.nom AS dernier_auteur,
            m.read_at IS NULL OR m.read_at < s.dernier_message_date AS unread
        FROM mp_sujets s
            JOIN mp_meta m ON m.sujet_id = s.id
            JOIN membres pa ON s.premier_message_auteur_id = pa.id
            JOIN membres da ON s.dernier_message_auteur_id = da.id
        WHERE m.membre_id = :user_id
             AND ((NOT m.deleted) OR s.dernier_message_auteur_id <> m.membre_id/*:user_id_bis*/)
        ORDER BY dernier_message_date DESC
        LIMIT :limit
        OFFSET :offset
EOS
    );
    $topics->bindValue('user_id', $_SESSION['id'], PDO::PARAM_INT);
//     $topics->bindValue('user_id_bis', $_SESSION['id'], PDO::PARAM_INT);
    $topics->bindValue('limit', NB_SUJETS_PAR_PAGE, PDO::PARAM_INT);
    $topics->bindValue('offset', NB_SUJETS_PAR_PAGE * ($page_courante - 1), PDO::PARAM_INT);
    $topics->execute();

    pagination($nombre_sujets, NB_SUJETS_PAR_PAGE, $page_courante);
    echo '<form method="POST" action="delete.php">';
    echo '<table width="100%">';
    foreach ($topics as $topic) {
?>
        <tr class="<?= $topic['unread'] ? 'unread' : '' ?>">
            <td>
                <input type="checkbox" name="selected[]" value="<?= $topic['id'] ?>"/>
            </td>
            <td>
                <a href="show.php?id=<?= $topic['id'] ?>"><?= htmlspecialchars($topic['titre'], ENT_NOQUOTES) ?></a>
                <br/>
                <?= $datefmt->format(date_create($topic['premier_message_date'])) ?> par <?= htmlspecialchars($topic['premier_auteur']) ?>
            </td>
            <td><?= $msgfmt->format([ $topic['nb_messages'] ]) ?></td>
            <td>
                <a href="show.php?id=<?= $topic['id'] ?>&amp;page=<?= ceil($topic['nb_messages'] / NB_MESSAGES_PAR_PAGE) ?>#<?= $topic['dernier_message_id'] ?>">
                    <?= $datefmt->format(date_create($topic['dernier_message_date'])) ?>
                    <br />
                    par <?= htmlspecialchars($topic['dernier_auteur']) ?>
                </a>
            </td>
        </tr>
<?php
    }
    echo '</table>';
    echo '<p><input type="submit" name="delete" value="Supprimer la sélection"/></p>';
    echo '</form>';
    pagination($nombre_sujets, NB_SUJETS_PAR_PAGE, $page_courante);
    echo '<p><a href="create.php">Nouveau MP</a></p>';
} else {
    echo 'Aucun MP. <a href="create.php">En rédiger un nouveau</a>.';
}
?>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<script type="text/javascript">
$(
    function () {
        $('<button/>').prependTo($('form p')).text('Inverser la sélection').on(
            'click',
            function () {
                $('input[name="selected[]"]', $(this).parent().parent()).each(
                    function () {
                        $(this).prop('checked', !$(this).prop('checked'));
                    }
                );
                return false;
            }
        );
    }
);
</script>

## pdo.php
<?php
mb_internal_encoding('UTF-8');

# MySQL
$bdd = new PDO('mysql:host=localhost;dbname=base_de_données;charset=utf8', 'login_mysql', 'mot_de_passe');
# PostgreSQL
#$bdd = new PDO('pgsql:host=localhost;dbname=base_de_données', 'login', 'mot_de_passe');
#$bdd->exec("SET NAMES 'UTF-8'");

$bdd->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

const NB_SUJETS_PAR_PAGE = 10;
const NB_MESSAGES_PAR_PAGE = 10;

define('LAST_INSERT_ID_FUNC_NAME', 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? 'LASTVAL' : 'LAST_INSERT_ID');

function pagination($nombre_entrees, $nombre_par_page, $page_courante, $params = [])
{
    if ($nombre_entrees > $nombre_par_page) { # inutile d'afficher la barre de navigation si tout tient sur une seule "page"
        $qs = $params ? '&amp;' . http_build_query($params, NULL, '&amp;') : '';
        echo '<ul class="pagination">';
        if ($page_courante > 1) {
            printf('<li><a href="?page=%d%s">Page précédente</a></li>', $page_courante - 1, $qs);
        }
        for ($i = 1, $derniere_page = ceil($nombre_entrees / $nombre_par_page); $i <= $derniere_page; $i++) {
            if ($i == $page_courante) { # ne pas afficher la page courante sous forme de lien
                printf('<li class="page-courante">%d</li>', $i);
            } else {
                echo '<li><a href="?page=' . $i . $qs . '">' . $i . '</a></li>';
            }
        }
        if ($page_courante < $derniere_page) {
            printf('<li><a href="?page=%d%s">Page suivante</a></li>', $page_courante + 1, $qs);
        }
        echo '</ul>';
    }
}

## show.php
<?php
session_start();

require 'pdo.php';

if (!isset($_SESSION['id'])) {
    http_response_code(403);
    die("L'accès à cette page requiert d'être authentifié");
}

$stmt = $bdd->prepare(<<<'EOS'
    SELECT s.*, m.id AS meta_id, m.read_at
        FROM mp_sujets s
        LEFT JOIN mp_meta m ON m.sujet_id = s.id AND m.membre_id = :user_id
    WHERE s.id = :sujet_id
EOS
);
$stmt->bindValue('sujet_id', $_GET['id'], PDO::PARAM_INT);
$stmt->bindValue('user_id', $_SESSION['id'], PDO::PARAM_INT);
$stmt->execute();

if ($sujet = $stmt->fetch()) {
    if (!$sujet['meta_id']) {
        http_response_code(403);
        die("Ce MP ne vous est pas adressé");
    } else {
        $stmt = $bdd->prepare('UPDATE mp_meta SET read_at = NOW() WHERE membre_id = :user_id AND sujet_id = :sujet_id');
        $stmt->bindValue('sujet_id', $_GET['id'], PDO::PARAM_INT);
        $stmt->bindValue('user_id', $_SESSION['id'], PDO::PARAM_INT);
        $stmt->execute();

        $page_courante = isset($_GET['page']) ? max(intval($_GET['page']), 1) : 1;

        $messages = $bdd->prepare(<<<'EOS'
            SELECT m.*, u.nom, :read IS NULL OR :read_at < created_at AS unread
                FROM mp_messages m
                JOIN membres u ON m.auteur_id = u.id
                WHERE sujet_id = :sujet_id
                LIMIT :limit
                OFFSET :offset
EOS
        );
        $messages->bindValue('read', $sujet['read_at'], PDO::PARAM_STR);
        $messages->bindValue('read_at', $sujet['read_at'], PDO::PARAM_STR);
        $messages->bindValue('sujet_id', $_GET['id'], PDO::PARAM_INT);
        $messages->bindValue('limit', NB_MESSAGES_PAR_PAGE, PDO::PARAM_INT);
        $messages->bindValue('offset', NB_MESSAGES_PAR_PAGE * ($page_courante - 1), PDO::PARAM_INT);
        $messages->execute();

        echo '<h1>', htmlspecialchars($sujet['titre'], ENT_NOQUOTES), '</h1>';

        pagination($sujet['nb_messages'], NB_MESSAGES_PAR_PAGE, $page_courante, ['id' => $sujet['id']]);

        echo '<table width="100%">';
        $datefmt = new IntlDateFormatter('fr_FR', NULL, NULL, NULL, NULL, 'EEEE dd LLLL HH:mm');
        foreach ($messages as $message) {
?>
            <tr id="<?= $message['id'] ?>" class="<?= $message['unread'] ? 'unread' : '' ?>">
                <td>
                    <?= htmlspecialchars($message['nom'], ENT_NOQUOTES) ?>
                    <br />
                    <?= $datefmt->format(date_create($message['created_at'])) ?>
                </td>
                <td><?= nl2br(htmlspecialchars($message['message'], ENT_NOQUOTES)) ?></td>
            </tr>
<?php
        }
        echo '</table>';

        pagination($sujet['nb_messages'], NB_MESSAGES_PAR_PAGE, $page_courante, ['id' => $sujet['id']]);
    }
} else {
    http_response_code(404);
    die("MP inexistant");
}

$erreurs = [];
if ('POST' == $_SERVER['REQUEST_METHOD']) {
    if (empty($_POST['message'])) {
        $erreurs['message'] = "Champ message non rempli";
    } else if (mb_strlen($_POST['message']) < 3) {
        $erreurs['message'] = "message trop court";
    }
    if ($erreurs) {
        echo '<p>Veuillez corriger les erreurs suivantes :</p>';
        echo '<ul><li>', implode('</li><li>', $erreurs), '</li></ul>';
    } else {
        $bdd->beginTransaction();
            $stmt = $bdd->prepare('INSERT INTO mp_messages(sujet_id, auteur_id, message, created_at) VALUES(:sujet_id, :auteur_id, :message, NOW())' . ('pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? ' RETURNING id' : ''));
            $stmt->bindValue('sujet_id', $_GET['id'], PDO::PARAM_INT);
            $stmt->bindValue('auteur_id', $_SESSION['id'], PDO::PARAM_INT);
            $stmt->bindValue('message', $_POST['message'], PDO::PARAM_STR);
            $stmt->execute();
            $msg_id = 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? $stmt->fetchColumn() : $bdd->lastInsertId();

            $stmt = $bdd->prepare('UPDATE mp_sujets SET dernier_message_id = ' . LAST_INSERT_ID_FUNC_NAME . '(), dernier_message_date = NOW(), dernier_message_auteur_id = :auteur_id, nb_messages = nb_messages + 1 WHERE id = :sujet_id');
            $stmt->bindValue('sujet_id', $_GET['id'], PDO::PARAM_INT);
            $stmt->bindValue('auteur_id', $_SESSION['id'], PDO::PARAM_INT);
            $stmt->execute();
        $bdd->commit();
        header('Location: show.php?id=' . $sujet['id'] . '#' . $msg_id);
        exit;
    }
}
?>

<form method="POST">
    <div>
        <label for="message">Message :</label>
        <textarea id="message" name="message" class="<?= isset($erreurs['message']) ? 'erreur' : '' ?>"><?= htmlspecialchars($_POST['message'] ?? '', ENT_NOQUOTES) ?></textarea>
    </div>
    <div>
        <input type="submit" value="Répondre"/>
    </div>
</form>

## tables_mysql.sql
/*
-- simule de façon incomplète votre table utilisateurs
CREATE TABLE membres(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    nom VARCHAR(80) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (nom)
);
*/

CREATE TABLE mp_sujets(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    nb_messages INT UNSIGNED NOT NULL DEFAULT 1,
    premier_message_id INT UNSIGNED NULL DEFAULT NULL,
    premier_message_date DATETIME NOT NULL,
    premier_message_auteur_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    dernier_message_id INT UNSIGNED NULL DEFAULT NULL,
    dernier_message_date DATETIME NOT NULL,
    dernier_message_auteur_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    titre VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE mp_messages(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    message MEDIUMTEXT NOT NULL,
    created_at DATETIME NOT NULL,
    auteur_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    sujet_id INT UNSIGNED NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (id)
);

ALTER TABLE mp_sujets ADD FOREIGN KEY (premier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE mp_sujets ADD FOREIGN KEY (dernier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE mp_meta(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    read_at DATETIME NULL DEFAULT NULL,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    sujet_id INT UNSIGNED NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    membre_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (id),
    UNIQUE KEY (sujet_id, membre_id)
);

## tables_postgresql.sql
/*
-- simule de façon incomplète votre table utilisateurs
CREATE TABLE membres(
    id SERIAL PRIMARY KEY NOT NULL,
    nom VARCHAR(80) NOT NULL,
    UNIQUE (nom)
);
*/

CREATE TABLE mp_sujets(
    id SERIAL PRIMARY KEY NOT NULL,
    nb_messages INT NOT NULL DEFAULT 1,
    premier_message_id INT NULL DEFAULT NULL,
    premier_message_date TIMESTAMP NOT NULL DEFAULT NOW(),
    premier_message_auteur_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    dernier_message_id INT NULL DEFAULT NULL,
    dernier_message_date TIMESTAMP NOT NULL DEFAULT NOW(),
    dernier_message_auteur_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    titre VARCHAR(100) NOT NULL
);

-- CREATE INDEX ON mp_sujets(premier_message_id);
-- CREATE INDEX ON mp_sujets(premier_message_auteur_id);
-- CREATE INDEX ON mp_sujets(dernier_message_id);
-- CREATE INDEX ON mp_sujets(dernier_message_auteur_id);

CREATE TABLE mp_messages(
    id SERIAL PRIMARY KEY NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    auteur_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    sujet_id INT NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- CREATE INDEX ON mp_messages(sujet_id);
-- CREATE INDEX ON mp_messages(auteur_id);

ALTER TABLE mp_sujets ADD FOREIGN KEY (premier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE mp_sujets ADD FOREIGN KEY (dernier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;

CREATE TABLE mp_meta(
    id SERIAL PRIMARY KEY NOT NULL,
    read_at TIMESTAMP NULL DEFAULT NULL,
    deleted BOOLEAN NOT NULL DEFAULT FALSE,
    sujet_id INT NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE,
    membre_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE (sujet_id, membre_id)
);

-- CREATE INDEX ON mp_meta(sujet_id);
-- CREATE INDEX ON mp_meta(membre_id);

## triggers_mysql.sql
DELIMITER //
CREATE TRIGGER after_insert_mp_message AFTER INSERT ON mp_messages FOR EACH ROW
BEGIN
    UPDATE mp_sujets SET
        dernier_message_id = NEW.id,
        dernier_message_date = NOW(),
        dernier_message_auteur_id = NEW.auteur_id,
        nb_messages = nb_messages + 1
        WHERE id = NEW.sujet_id
    ;
END//

CREATE TRIGGER after_delete_comments AFTER DELETE ON comments FOR EACH ROW
BEGIN
    UPDATE posts SET comments_count = comments_count - 1 WHERE id = OLD.post_id;
END//
DELIMITER ;

## users.php
<?php
session_start();

require 'pdo.php';

if (!isset($_SESSION['id'])) {
    http_response_code(403);
    die("L'accès à cette page requiert d'être authentifié");
}

header('Content-type: application/json; charset=utf-8');
$stmt = $bdd->prepare(<<<'EOS'
    SELECT nom
        FROM membres
        WHERE nom LIKE CONCAT(:term, '%') ESCAPE '!'
        ORDER BY nom
        LIMIT 20
EOS
);
$stmt->execute(['term' => preg_replace('~[%_!]~', '!\0', $_POST['term'])]);
echo json_encode($stmt->fetchAll(PDO::FETCH_COLUMN, 0));
	<?php
	session_start();

	require 'pdo.php';

	const MIN_SUJET_LEN = 3;
	const MIN_MESSAGE_LEN = 3;

	if (!isset($_SESSION['id'])) {
	http_response_code(403);
	die("L'accès à cette page requiert d'être authentifié");
	}

	$erreurs = [];
	if ('POST' == $_SERVER['REQUEST_METHOD']) {
	if (empty($_POST['destinataire'])) {
	$erreurs['destinataire'] = "Champ destinataire non rempli";
	} else {
	$stmt = $bdd->prepare('SELECT id FROM membres WHERE nom = :pseudo');
	$stmt->execute(['pseudo' => $_POST['destinataire']]);
	if (!($destinataire_id = $stmt->fetchColumn())) {
	$erreurs['destinataire'] = "Utilisateur inconnu au bataillon";
	} else if ($destinataire_id == $_SESSION['id']) {
	$erreurs['destinataire'] = "Vous ne pouvez pas vous écrire";
	}
	}
	if (empty($_POST['sujet'])) {
	$erreurs['message'] = "Champ sujet non rempli";
	} else if (mb_strlen($_POST['sujet']) < MIN_SUJET_LEN) {
	$erreurs['sujet'] = sprintf("sujet trop court, au moins %d caractères", MIN_MESSAGE_LEN);
	}
	if (empty($_POST['message'])) {
	$erreurs['message'] = "Champ message non rempli";
	} else if (mb_strlen($_POST['message']) < MIN_MESSAGE_LEN) {
	$erreurs['message'] = sprintf("message trop court, au moins %d caractères", MIN_SUJET_LEN);
	}
	if ($erreurs) {
	echo '<p>Veuillez corriger les erreurs suivantes :</p>';
	echo '<ul><li>', implode('</li><li>', $erreurs), '</li></ul>';
	} else {
	$bdd->beginTransaction();
	$stmt = $bdd->prepare('INSERT INTO mp_sujets(titre, premier_message_auteur_id, dernier_message_auteur_id, premier_message_date, dernier_message_date) VALUES(:titre, :auteur_id_premier_message, :auteur_id_dernier_message, NOW(), NOW())' . ('pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? ' RETURNING id' : ''));
	$stmt->bindValue('titre', $_POST['sujet'], PDO::PARAM_STR);
	$stmt->bindValue('auteur_id_premier_message', $_SESSION['id'], PDO::PARAM_INT);
	$stmt->bindValue('auteur_id_dernier_message', $_SESSION['id'], PDO::PARAM_INT);
	$stmt->execute();
	$sujet_id = 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? $stmt->fetchColumn() : $bdd->lastInsertId();

	$stmt = $bdd->prepare('INSERT INTO mp_meta(sujet_id, membre_id) VALUES(:sujet_id, :membre_id)');
	$stmt->bindValue('sujet_id', $sujet_id, PDO::PARAM_INT);
	$stmt->bindParam('membre_id', $membre_id, PDO::PARAM_INT);
	foreach ([$_SESSION['id'], $destinataire_id] as $membre_id) {
	$stmt->execute();
	}

	$stmt = $bdd->prepare('INSERT INTO mp_messages(sujet_id, auteur_id, message, created_at) VALUES(:sujet_id, :auteur_id, :message, NOW())');
	$stmt->bindValue('sujet_id', $sujet_id, PDO::PARAM_INT);
	$stmt->bindValue('auteur_id', $_SESSION['id'], PDO::PARAM_INT);
	$stmt->bindValue('message', $_POST['message'], PDO::PARAM_STR);
	$stmt->execute();

	$stmt = $bdd->prepare('UPDATE mp_sujets SET premier_message_id = ' . LAST_INSERT_ID_FUNC_NAME . '(), dernier_message_id = ' . LAST_INSERT_ID_FUNC_NAME . '() WHERE id = :sujet_id');
	$stmt->bindValue('sujet_id', $sujet_id, PDO::PARAM_INT);
	$stmt->execute();
	$bdd->commit();
	header('Location: index.php');
	exit;
	}
	}
	?>

	<form method="POST">
	<div>
	<label for="destinataire">À :</label>
	<input autocomplete="off" type="text" id="destinataire" name="destinataire" class="<?= isset($erreurs['destinataire']) ? 'erreur' : '' ?>" value="<?= htmlspecialchars($_POST['destinataire'] ?? '') ?>"/>
	<datalist id="destinataire_list"></datalist>
	</div>
	<div>
	<label for="sujet">Sujet :</label>
	<input type="text" id="sujet" name="sujet" class="<?= isset($erreurs['sujet']) ? 'erreur' : '' ?>" value="<?= htmlspecialchars($_POST['sujet'] ?? '') ?>"/>
	</div>
	<div>
	<label for="message">Message :</label>
	<textarea id="message" name="message" class="<?= isset($erreurs['message']) ? 'erreur' : '' ?>"><?= htmlspecialchars($_POST['message'] ?? '', ENT_NOQUOTES) ?></textarea>
	</div>
	<div>
	<input type="submit" value="Envoyer"/>
	</div>
	</form>

	<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
	<script type="text/javascript">
	$(
	function () {
	var $datalist = $('#destinataire_list');

	$('input[name="destinataire"]').on(
	'keyup',
	function (e) {
	switch (e.key) {
	case 'Enter':
	case 'Backspace':
	case 'ArrowUp':
	case 'ArrowDown':
	case 'ArrowLeft':
	case 'ArrowRight':
	return;
	}
	$.post(
	'users.php',
	{
	term: $(this).val()
	}
	).done(
	function (data) {
	$datalist.empty();
	for (var i = 0; i < data.length; i++) {
	console.log(data[i]);
	$('<option/>').attr('value', data[i]).text(data[i]).appendTo($datalist);
	}
	}
	);
	}
	);
	}
	);
	</script>
	<?php
	require __DIR__ . '/pdo.php';

	const INTERVAL = '7 DAY';

	// MySQL : INTERVAL 7 DAY vs PostgreSQL : INTERVAL '7 DAY'
	$interval = 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? $bdd->quote(INTERVAL) : INTERVAL;

	$bdd->exec(<<<EOS
	SELECT id
	FROM mp_sujets
	WHERE dernier_message_date < INTERVAL {$interval}
	AND NOT EXISTS(
	SELECT 1
	FROM mp_meta
	WHERE sujet_id = mp_sujets.id
	AND NOT m.deleted
	)
	EOS
	);
	<?php
	mb_internal_encoding('UTF-8');

	# MySQL
	$bdd = new PDO('mysql:host=localhost;dbname=base_de_données;charset=utf8', 'login_mysql', 'mot_de_passe');
	# PostgreSQL
	#$bdd = new PDO('pgsql:host=localhost;dbname=base_de_données', 'login', 'mot_de_passe');
	#$bdd->exec("SET NAMES 'UTF-8'");

	$bdd->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
	$bdd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	const NB_SUJETS_PAR_PAGE = 10;
	const NB_MESSAGES_PAR_PAGE = 10;

	define('LAST_INSERT_ID_FUNC_NAME', 'pgsql' == $bdd->getAttribute(PDO::ATTR_DRIVER_NAME) ? 'LASTVAL' : 'LAST_INSERT_ID');

	function pagination($nombre_entrees, $nombre_par_page, $page_courante, $params = [])
	{
	if ($nombre_entrees > $nombre_par_page) { # inutile d'afficher la barre de navigation si tout tient sur une seule "page"
	$qs = $params ? '&' . http_build_query($params, NULL, '&') : '';
	echo '<ul class="pagination">';
	if ($page_courante > 1) {
	printf('<li><a href="?page=%d%s">Page précédente</a></li>', $page_courante - 1, $qs);
	}
	for ($i = 1, $derniere_page = ceil($nombre_entrees / $nombre_par_page); $i <= $derniere_page; $i++) {
	if ($i == $page_courante) { # ne pas afficher la page courante sous forme de lien
	printf('<li class="page-courante">%d</li>', $i);
	} else {
	echo '<li><a href="?page=' . $i . $qs . '">' . $i . '</a></li>';
	}
	}
	if ($page_courante < $derniere_page) {
	printf('<li><a href="?page=%d%s">Page suivante</a></li>', $page_courante + 1, $qs);
	}
	echo '</ul>';
	}
	}
	/*
	-- simule de façon incomplète votre table utilisateurs
	CREATE TABLE membres(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	nom VARCHAR(80) NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY (nom)
	);
	*/

	CREATE TABLE mp_sujets(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	nb_messages INT UNSIGNED NOT NULL DEFAULT 1,
	premier_message_id INT UNSIGNED NULL DEFAULT NULL,
	premier_message_date DATETIME NOT NULL,
	premier_message_auteur_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	dernier_message_id INT UNSIGNED NULL DEFAULT NULL,
	dernier_message_date DATETIME NOT NULL,
	dernier_message_auteur_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	titre VARCHAR(100) NOT NULL,
	PRIMARY KEY (id)
	);

	CREATE TABLE mp_messages(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	message MEDIUMTEXT NOT NULL,
	created_at DATETIME NOT NULL,
	auteur_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	sujet_id INT UNSIGNED NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (id)
	);

	ALTER TABLE mp_sujets ADD FOREIGN KEY (premier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;
	ALTER TABLE mp_sujets ADD FOREIGN KEY (dernier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;

	CREATE TABLE mp_meta(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	read_at DATETIME NULL DEFAULT NULL,
	deleted BOOLEAN NOT NULL DEFAULT FALSE,
	sujet_id INT UNSIGNED NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE,
	membre_id INT UNSIGNED NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	PRIMARY KEY (id),
	UNIQUE KEY (sujet_id, membre_id)
	);
	/*
	-- simule de façon incomplète votre table utilisateurs
	CREATE TABLE membres(
	id SERIAL PRIMARY KEY NOT NULL,
	nom VARCHAR(80) NOT NULL,
	UNIQUE (nom)
	);
	*/

	CREATE TABLE mp_sujets(
	id SERIAL PRIMARY KEY NOT NULL,
	nb_messages INT NOT NULL DEFAULT 1,
	premier_message_id INT NULL DEFAULT NULL,
	premier_message_date TIMESTAMP NOT NULL DEFAULT NOW(),
	premier_message_auteur_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	dernier_message_id INT NULL DEFAULT NULL,
	dernier_message_date TIMESTAMP NOT NULL DEFAULT NOW(),
	dernier_message_auteur_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	titre VARCHAR(100) NOT NULL
	);

	-- CREATE INDEX ON mp_sujets(premier_message_id);
	-- CREATE INDEX ON mp_sujets(premier_message_auteur_id);
	-- CREATE INDEX ON mp_sujets(dernier_message_id);
	-- CREATE INDEX ON mp_sujets(dernier_message_auteur_id);

	CREATE TABLE mp_messages(
	id SERIAL PRIMARY KEY NOT NULL,
	message TEXT NOT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT NOW(),
	auteur_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	sujet_id INT NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE
	);

	-- CREATE INDEX ON mp_messages(sujet_id);
	-- CREATE INDEX ON mp_messages(auteur_id);

	ALTER TABLE mp_sujets ADD FOREIGN KEY (premier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;
	ALTER TABLE mp_sujets ADD FOREIGN KEY (dernier_message_id) REFERENCES mp_messages(id) ON UPDATE CASCADE ON DELETE CASCADE;

	CREATE TABLE mp_meta(
	id SERIAL PRIMARY KEY NOT NULL,
	read_at TIMESTAMP NULL DEFAULT NULL,
	deleted BOOLEAN NOT NULL DEFAULT FALSE,
	sujet_id INT NOT NULL REFERENCES mp_sujets(id) ON UPDATE CASCADE ON DELETE CASCADE,
	membre_id INT NOT NULL REFERENCES membres(id) ON UPDATE CASCADE ON DELETE CASCADE,
	UNIQUE (sujet_id, membre_id)
	);

	-- CREATE INDEX ON mp_meta(sujet_id);
	-- CREATE INDEX ON mp_meta(membre_id);
	DELIMITER //
	CREATE TRIGGER after_insert_mp_message AFTER INSERT ON mp_messages FOR EACH ROW
	BEGIN
	UPDATE mp_sujets SET
	dernier_message_id = NEW.id,
	dernier_message_date = NOW(),
	dernier_message_auteur_id = NEW.auteur_id,
	nb_messages = nb_messages + 1
	WHERE id = NEW.sujet_id
	;
	END//

	CREATE TRIGGER after_delete_comments AFTER DELETE ON comments FOR EACH ROW
	BEGIN
	UPDATE posts SET comments_count = comments_count - 1 WHERE id = OLD.post_id;
	END//
	DELIMITER ;