Клас для роботи з базою даних MySQL

Існує дуже велика кількість різних технік створення веб-ресурсів, починаючи від простого структурного та закінчуючи складною об'єктно-орієнтованою системою. Кожен підхід має свої плюси та мінуси. Необхідно знайти правильну структуру для певної програми, ґрунтуючись на її потребах. Наприклад, для маленького сайту візитки підійде простий структурний тип програмування, для великого проекту, звичайно, необхідно продумати вже сильніший підхід, заснований на використанні об'єктів.

У цій темі опишемо клас для роботи з базою даних MySQL, за допомогою якого можна легко керувати збереженими даними, додавати, видаляти чи редагувати інформацію. Розібравшись із роботою даного класу, його можна використовувати практично для всіх веб-застосунків, він зможе служити серцем будь-якого скрипта.

Конфігураційні налаштування класу

Для роботи даного класу необхідно мати конфігураційні установки, що стосуються безпосередньо сервера баз даних:

define("DBHOST", "localhost");
define("DBNAME", "base");
define("DBUSER", "root");
define("DBPASS", "password");
define("COLLATE", "utf8");

Опишемо основні константи:

- DBHOST містить ім'я сервера MySQL;

- DBNAME містить ім'я бази даних;

- DBUSER містить ім'я користувача;

- DBPASS містить пароль користувача бази даних;

- COLLATE кодування за замовчуванням.

Далі опишемо функції класу.

Властивості об'єкту класу MySQLi

Як відомо, об'єкт - це сукупність даних (властивостей) та функцій (методів) для їх обробки. Властивості та методи називаються членами класу. На самому початку опишемо змінні властивості цього класу.

var $db_object = false;
var $query_num = 0;
var $query_list = array();
var $mysql_version = '';
var $MySQL_time_taken = 0;
var $query_object = false;

Опишемо основні змінні:

- Змінна $db_object зберігатиме відкрите з'єднання з базою даних. За замовчуванням значення False.

- У змінній $query_num буде зберігатись кількість виконаних запитів до бази даних. Це корисно для оптимізації скрипту та визначення навантаження. За замовчуванням дорівнює 0.

- Масив $query_list зберігатиме список запитів, їх час виконання і сам запит. Ця змінна служить виключно для цілей налагодження, тому її використання буде виправдано тільки при роботі адміністратора сайту.

- У змінній $mysql_version зберігається версія сервера бази даних MySQL. Ці дані будуть цікаві, наприклад, під час тестування швидкості роботи різних версій.

- Змінна $MySQL_time_taken зберігатиме загальний час, який витрачається на запити до бази даних. За замовчуванням дорівнює 0.

- Змінна $query_object зберігає значення виконання запиту до бази даних.

Далі будуть слідувати необхідні функції, і почнемо з функції визначення реального часу:

function get_real_time() {
    return microtime(true);
}

Підключення до бази даних MySQL

Наступним етапом буде функція підключення до бази даних:

function connect($db_user, $db_pass, $db_name, $db_location) {
    $db_location = explode(":", $db_location);
    if (isset($db_location[1])) {
        $this->db_object = @mysqli_connect($db_location[0], $db_user, $db_pass, $db_name, $db_location[1]);
    } else {
        $this->db_object = @mysqli_connect($db_location[0], $db_user, $db_pass, $db_name);
    }
    if (!$this->db_object) {
        $this->display_error(mysqli_connect_error(), '1');
    }
    $this->mysql_version = mysqli_get_server_info($this->db_object);
    mysqli_query($this->db_object, "SET NAMES '" . COLLATE . "'");
    mysqli_set_charset($this->db_object, "utf8mb4");
    return true;
}

Ця функція приймає як параметри ім'я користувача, пароль, ім'я бази даних та хоста.

Залежно від наявності порту в імені хоста, буде виконано один із двох варіантів підключення.

Якщо з'єднання завершиться помилкою, тоді спрацює функція Display_error, яка буде описана далі.

if (!$this->db_object) {
    $this->display_error(mysqli_connect_error(), '1');
}

У змінну Mysql_version буде записано значення версії бази даних:

$this->mysql_version = mysqli_get_server_info($this->db_object);

І останнє, що робить дана функція, це встановлює необхідне кодування для роботи:

mysqli_query($this->db_object, "SET NAMES '" . COLLATE . "'");
mysqli_set_charset($this->db_object, "utf8mb4");

Функція виконання запиту MySQL

Далі слідує функція виконання запиту до бази даних.

function query($query) {
    $time_before = $this->get_real_time();
    if (!$this->db_object) {
        $this->connect(DBUSER, DBPASS, DBNAME, DBHOST);
    }
    if (!($this->query_object = mysqli_query($this->db_object, $query))) {
        $this->display_error(mysqli_error($this->db_object), mysqli_errno($this->db_object), $query);
    }
    $this->MySQL_time_taken += $this->get_real_time() - $time_before;
    $this->query_list[ ] = array('time'     => ($this->get_real_time() - $time_before),
                                 'query'    => $query,
                                 'num'      => (count($this->query_list) + 1));
    $this->query_num++;
    return $this->query_object;
}

Саме ця функція виконуватиме основні дії з базою даних: додавання, видалення, редагування інформації.

Спочатку функція записує в змінну $time_before реальний час початку роботи функції:

$time_before = $this->get_real_time();

Далі виконує підключення до бази даних, якщо воно не встановлено попереднім викликом цієї функції.

if (!$this->db_object) {
    $this->connect(DBUSER, DBPASS, DBNAME, DBHOST);
}

Далі функція виконує запит, і у разі його завершення з помилкою видає повідомлення про це:

if (!($this->query_object = mysqli_query($this->db_object, $query))) {
    $this->display_error(mysqli_error($this->db_object), mysqli_errno($this->db_object), $query);
}

Далі функція розраховує час виконання запиту і додає його до вже існуючого. Змінна MySQL_time_taken зберігатиме загальний час виконання всіх запитів:

$this->MySQL_time_taken += $this->get_real_time() - $time_before;

Далі заповнюємо масив $query_list, який зберігатиме список запитів, їх час виконання і сам запит.

$this->query_list[ ] = array('time'     => ($this->get_real_time() - $time_before),
                             'query'    => $query,
                             'num'      => (count($this->query_list) + 1));

Остання дія функції є підрахунок кількості всіх запитів і повернення результату виконання:

$this->query_num++;
return $this->query_object;

Функція повернення автоматично генерованого ID

Далі слідує функція повернення автоматично генерованого ID, використовуючи останній запит.

function insert_id() {
    return mysqli_insert_id($this->db_object);
}

Ця функція може стати в нагоді записуючи унікальне значення ідентифікатора ID користувача при реєстрації, тобто при додаванні даних до бази даних.

Функція Mysqli_insert_id повертає ID, що генерується запитом до таблиці, що містить колонку з атрибутом AUTO_INCREMENT.

Вилучення результуючого ряду у вигляді асоціативного масиву

Наступною функцією буде функція вилучення результуючого ряду як асоціативного масиву.

function get_row($query_object = '') {
    if ($query_object == '') {
        $query_object = $this->query_object;
    }
    return mysqli_fetch_assoc($query_object);
}

Повертає асоціативний масив, який відповідає результуючій вибірці або NULL, якщо інших рядів не існує.

Якщо у параметрі порожній рядок, тоді буде повернено масив останнього запиту до бази даних, інакше запит, який міститься у параметрі функції.

Звільнення пам'яті зайнятої результатами запиту

Опишемо функцію звільнення пам'яті, зайняту результатами запиту:

function free($query_object = '') {
    if ($query_object == '') {
        $query_object = $this->query_object;
    }
    @mysqli_free_result($query_object);
}

Якщо у параметрі порожній рядок, тоді буде звільнено пам'ять останнього запиту до бази даних, інакше запит, який міститься у параметрі функції.

Функція мультизапиту

Опишемо функцію, яка виконує мультизапит до бази даних:

function super_query($query, $multi = false) {
    if (!$multi) {
        $this->query($query);
        $data = $this->get_row();
        $this->free();
        return $data;
    } else {
        $this->query($query);
        $rows = array();
        while($row = $this->get_row()) {
            $rows[] = $row;
        }
        $this->free();
        return $rows;
    }
}

Ця функція приймає як параметр запит до бази даних. Другий аргумент функції є логічний тип, який за умовчанням набуває значення False.

Якщо значення другого аргументу False, то функція повертає один рядок запиту. Якщо значення True – тоді повертається масив результатів. Також ця функція звільняє пам'ять, зайняту результатами запиту.

Отримуємо кількість рядків, отриманих попередньою операцією

Опишемо процедуру отримання числа рядків, порушених попередньою операцією запиту до бази даних:

function get_affected_rows() {
    return mysqli_affected_rows($this->db_object);
}

Ця функція повертає кількість рядків, отриманих в останніх INSERT, UPDATE, REPLACE або DELETE запитах.

Результатом роботи функції є ціле число. Якщо вона більша за нуль, значить одна з вищеописаних операція закінчилася успішно. Нуль означає, що запиту виду UPDATE не оновлено жодного запису, або жоден рядок не відповідає умові WHERE у запиті, або запит ще не було виконано.

Отримуємо число рядів у результативній вибірці

Розглянемо функцію отримання числа рядів у результуючій вибірці:

function num_rows($query_object = '') {
    if ($query_object == '') {
        $query_object = $this->query_object;
    }
    return mysqli_num_rows($query_object);
}

Якщо параметр порожній рядок, тоді буде повернено масив останнього запиту до бази даних, інакше запит, який міститься в параметрі функції.

Вибір одного рядка з результуючого набору

Далі слідує функція вибірки одного рядка з результуючого набору та додавання її в асоціативний масив.

function get_array($query_object = '') {
    if ($query_object == '') {
        $query_object = $this->query_object;
    }
    return mysqli_fetch_array($query_object);
}

Якщо параметр порожній рядок, тоді буде повернено масив останнього запиту до бази даних, інакше запит, що міститься в параметрі функції.

Функція екранування спецсимволів у рядку

Ще одна функція даного класу є функція екранування спецсимволів у рядку:

function safesql($source) {
    if (!$this->db_object) {
        $this->connect(DBUSER, DBPASS, DBNAME, DBHOST);
    }
    if ($this->db_object) {
        return mysqli_real_escape_string($this->db_object, $source);
    } else {
        return addslashes($source);
    }
}

Ця функція повертає рядок, в якому перед кожним спецсимволом доданий зворотний слеш (\), для подальшого використання цього рядка у запиті до бази даних. Екрануються одиночні лапки ('), подвійні лапки ("), зворотний слеш (\) та NUL (байт NULL).

Закриття раніше відкритого з'єднання з базою даних

Функція класу Close для роботи з базою даних закриває раніше відкрите з'єднання з базою даних.

function close() {
    @mysqli_close($this->db_object);
}

Функція виведення помилок

Далі слідує функція виведення помилок, завдяки якій можна чітко виявити місцезнаходження помилки:

function display_error($error, $error_num, $query = '') {
    $query = htmlspecialchars($query, ENT_QUOTES, 'UTF-8');
    $error = htmlspecialchars($error, ENT_QUOTES, 'UTF-8');
    $trace = debug_backtrace();
    $level = 0;
    if (@$trace[1]['function'] == "query") $level = 1;
    if (@$trace[2]['function'] == "super_query") $level = 2;
    $trace[$level]['file'] = str_replace(ROOT_PATH, "", $trace[$level]['file']);
    echo '
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <title>MySQL Fatal Error</title>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    </head>
    <body>
    <table border="1">
        <tr>
            <td colspan="2" align="center">MySQL Error!</td>
        </tr>
        <tr>
            <td>MySQL error in file:</td>
            <td>'.$trace[$level]['file'].' at line '.$trace[$level]['line'].'</td>
        </tr>
        <tr>
            <td>Error Number:</td>
            <td>'.$error_num.'</td>
        </tr>
        <tr>
            <td>The Error returned was:</td>
            <td>'.$error.'</td>
        </tr>
        <tr>
            <td>SQL query:</td>
            <td>'.$query.'</td>
        </tr>
    </table>
    </body>
    </html>';
    exit();
}

Спочатку обробляємо виведення помилок, а саме перетворюємо спеціальні символи в HTML-сутності:

$query = htmlspecialchars($query, ENT_QUOTES, 'UTF-8');
$error = htmlspecialchars($error, ENT_QUOTES, 'UTF-8');

Далі запускаємо функцію Debug_backtrace, яка повертає масив вкладених асоціативних масивів:

$trace = debug_backtrace();

Оскільки виклик функції Query міститься у функції Super_query, зміст масивів завжди буде строго по порядку:

Array
(
    [0] => Array
        (
        [file] => classes\mysqli.php
        [line] => 43
        [function] => display_error
        ...
        )
    [1] => Array
        (
        [file] => classes\mysqli.php
        [line] => 76
        [function] => query
        ...
        )
    [2] => Array
        (
        [file] => file.php
        [line] => 17
        [function] => super_query
        )
)

Завдяки даній функції ми точно дізнаємося ім'я файлу, номер рядка з помилкою, номер самої помилки з текстовим поданням і запит, який містить помилку.

Ну ось і добіг кінця клас для роботи з базою даних MySQL.

Основні можливості класу

Опишемо основні можливості класу та їх реалізацію.

Спочатку необхідно створити екземпляр класу:

$db = new db;

Додавання записів до таблиці:

$email = 'first@mail.ru';
$password = 'abc';
$db->query("INSERT INTO `test` (`email`, `password`) VALUES ('{$email}', '{$password}')");

Видалення запису:

$db->query("DELETE FROM `test` WHERE password = 'mypassword'");

Автоматично генерований ID останній запит:

$id = $db->insert_id();

Оновлення записів:

$email = 'myname@gmail.com';
$password = 'abc';
$id = '3';
$db->query("UPDATE `test` SET `email` = '{$email}', `password` = '{$password}' WHERE (id = '{$id}')");

Вибірка даних:

$password = 'abc';
$row = $db->query("SELECT email, password FROM `test` WHERE password = '{$password}'");
echo '<pre>';
print_r($row);
echo '</pre>';
echo $db->num_rows();

Результат виконання:

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 2
    [lengths] => 
    [num_rows] => 1
    [type] => 0
)
1

Другий варіант:

$password = 'abc';
$row = $db->super_query("SELECT email, password  FROM `test` WHERE password = '{$password}'", true);
echo '<pre>';
print_r($row);
echo '</pre>';
echo $db->get_affected_rows();

Результат виконання:

Array
(
    [0] => Array
         (
          [email] => myname@mail.ru
          [password] => vasya
         )
)
1

Третій варіант:

$password = 'abc';
$db->query("SELECT email, password  FROM `test` WHERE password = '{$password}'");
$row = $db->get_row();
echo '<pre>';
print_r($row);
echo '</pre>';

Результат:

Array
(
    [email] => myname@mail.ru
    [password] => vasya
)

Перевірка існування унікального запису:

$db->query("SELECT `id` FROM `table` WHERE `id` = '999'");
if ($db->num_rows()) {
    ...
}

Перевірка існування кількох відповідних записів:

$row = $db->super_query("SELECT COUNT(*) as count FROM `table` WHERE `name` = 'php'");
if ( $row['count'] > 2 ) {
   ...
}

Обновити комірку в таблиці, якщо значення поля збігається з умовою. Цей вираз дуже часто використовується в програмуванні:

$db->query( "UPDATE `table` 
SET
    `status` = CASE
         WHEN `status` = '0' THEN 1
         WHEN `status` = '1' THEN 0
    END
WHERE (`id` = '{$id}')");

Можна створити функцію для виведення інформації для адміністратора:

function view_info_admin() {
    global $db;
    echo '<div style="border: 1px solid red; background: red;">';
    echo '<b>Version MySQL server:</b> '.$db->mysql_version.'<br />';
    $all_query = $db->query_list;
    echo '<pre>';
    print_r( $all_query );
    echo '</pre>';
    echo '<b>ALL Times:</b> '.$db->MySQL_time_taken.'<br />';
    echo '<b>ALL Query:</b> '.$db->query_num.'<br />';
    echo '<b>ALL Memory:</b> '.get_size(memory_get_usage()).'<br />';
    echo '</div>';
}
// Визначення розміру
function get_size($bytes) {
    if ($bytes < 1000 * 1024) {
        return number_format($bytes / 1024, 2) . " KB";
    } elseif ($bytes < 1000 * 1048576) {
        return number_format($bytes / 1048576, 2) . " MB";
    } elseif ($bytes < 1000 * 1073741824) {
        return number_format($bytes / 1073741824, 2) . " GB";
    } else {
        return number_format($bytes / 1099511627776, 2) . " TB";
    }
}