MySQL и PHP: переводим прайс клиента в базу данных

Клиент занимается оптовой торговлей комплектующими. Прайс - это Excel-файл на три сотни строк, который рассылается партнёрам по почте раз в неделю. Задача: сделать каталог на сайте, чтобы партнёры смотрели актуальные цены сами и не дёргали менеджеров. На сервере уже стоит Apache с PHP - поднимали для сайта-визитки в прошлом году. Добавляем MySQL, пишем пару скриптов - и каталог готов. Казалось бы.

Ставим MySQL

Debian Potato, MySQL 3.23. Установка простая:

apt-get install mysql-server php4-mysql

После установки MySQL запускается и принимает соединения. Первым делом меняем пароль root:

mysqladmin -u root password 'нормальный_пароль'

По умолчанию MySQL создаёт пользователя root без пароля и с доступом откуда угодно. Это первое, что надо закрыть - сразу, до того как трогать что-то ещё.

Создаём базу и пользователя для приложения:

CREATE DATABASE catalog CHARACTER SET cp1251;
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'пароль';
GRANT SELECT ON catalog.* TO 'webuser'@'localhost';

Пользователь приложения получает только SELECT. Заливать данные будем отдельно, от другого пользователя. Это не паранойя - если PHP-скрипт когда-нибудь окажется уязвимым, злоумышленник не сможет через него ничего записать в базу.

Структура таблицы

Прайс клиента устроен просто: категория, артикул, название, цена, наличие. Создаём:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category VARCHAR(100),
    sku VARCHAR(50),
    name VARCHAR(255),
    price DECIMAL(10,2),
    in_stock TINYINT(1) DEFAULT 1
) CHARACTER SET cp1251;

Здесь и начинается тема кодировок. Клиент работает в Windows, Excel сохраняет в Windows-1251 (она же cp1251). Сайт отдаётся тоже в 1251 - на Linux это надо явно настраивать, в Apache добавляем AddDefaultCharset windows-1251. Если база создана в другой кодировке - привет, кракозябры. Причём они могут вылезти не сразу: пока данных мало и всё ASCII, всё нормально. Русские символы приходят - и начинается веселье.

Мы это проверили на себе. Первая версия таблицы была создана без явного CHARACTER SET. MySQL по умолчанию поставил latin1. Залили несколько строк для теста - названия в браузере выглядели как мусор. Пришлось дропать таблицу, создавать заново с правильной кодировкой и заливать снова. Хорошо что это был тест, а не продакшн со всем прайсом.

Правило простое: кодировка базы, таблицы, соединения и заголовков страницы должна быть одной и той же с первого дня. Выбираешь cp1251 - везде cp1251. Переделывать потом дороже.

Импорт из Excel

Клиент готовит прайс в Excel, менеджер обновляет раз в неделю. Импорт делаем через CSV: из Excel «Сохранить как» - CSV с разделителем точка с запятой, кодировка Windows. Заливаем в MySQL:

LOAD DATA INFILE '/tmp/price.csv'
INTO TABLE products
CHARACTER SET cp1251
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(category, sku, name, price, in_stock);

IGNORE 1 LINES - пропускаем шапку таблицы. \r\n - потому что файл из Windows и строки там заканчиваются по-виндовому. Если этого не указать, MySQL заглатывает \r как часть последнего поля, и в базе у всех записей в конце поля in_stock будет лишний символ. Тоже нашли опытным путём.

PHP-скрипт каталога

Скрипт несложный - выборка из базы с группировкой по категориям:

<?php
$db = mysql_connect('localhost', 'webuser', 'пароль');
mysql_select_db('catalog', $db);
mysql_query("SET NAMES cp1251");

$result = mysql_query(
    "SELECT category, sku, name, price, in_stock
     FROM products
     ORDER BY category, name"
);

$current_cat = '';
while ($row = mysql_fetch_assoc($result)) {
    if ($row['category'] !== $current_cat) {
        if ($current_cat !== '') echo '</table>';
        echo '<h2>' . htmlspecialchars($row['category']) . '</h2>';
        echo '<table border="1"><tr><th>Артикул</th><th>Название</th><th>Цена</th><th>Наличие</th></tr>';
        $current_cat = $row['category'];
    }
    echo '<tr>';
    echo '<td>' . htmlspecialchars($row['sku']) . '</td>';
    echo '<td>' . htmlspecialchars($row['name']) . '</td>';
    echo '<td>' . number_format($row['price'], 2, ',', ' ') . ' руб.</td>';
    echo '<td>' . ($row['in_stock'] ? 'есть' : 'нет') . '</td>';
    echo '</tr>';
}
if ($current_cat !== '') echo '</table>';
?>

SET NAMES cp1251 - это то самое соединение с базой. Если его не выставить, MySQL будет отдавать данные в latin1, и кодировка снова поедет. Три места, где кодировка должна совпадать: база/таблица, соединение (SET NAMES), заголовок страницы.

htmlspecialchars на каждом выводе - не для красоты. В прайсе попадаются названия с кавычками и амперсандами, без экранирования HTML ломается.

Что получилось

Каталог работает. Партнёры смотрят цены на сайте вместо того чтобы ждать письма. Менеджер раз в неделю заливает новый CSV - процедура на пять минут. Интеграция простая, но рабочая - именно такой подход мы обычно и предлагаем, когда речь идёт о связке внутренних данных с веб-интерфейсом.

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