Удобные функции PHP для работы с MySQL

10 октября 2010, 17:53

В статье предлагается несколько простых функций языка PHP для взаимодействия с СУБД MySQL, которые могут значительно облегчить повседневную работу программистов. Код функций можно скачать одним php-файлом.

Работа с MySQL силами встроенных функций PHP (таких как mysql_query(), mysql_fetch_assoc() и др.) не является чем-то сложным. Однако синтаксические конструкции, требуемые для обработки результатов запроса, обычно занимают пару лишних строк кода. Приходится либо повторять такой код каждый раз, либо использовать специальные библиотеки, которые призваны эту задачу автоматизировать. Эти библиотеки, однако, часто берут на себя дополнительные (обычно довольно бесполезные) задачи, в результате чего интерфейс их усложняется и такими библиотеками оказывается неудобно пользоваться.

Предлагаемые в настоящей статье функции позволяют решать подавляющее большинство задач по отправке и обработке результатов запросов к серверу MySQL, используя для этого очень небольшое количество программного кода. Кроме того, легко включается режим использования улучшенного модуля mysqli, что актуально в связи с рекомендациями по переходу с оригинального API MySQL, который с версии PHP 5.5.0 будет считаться устаревшим.

mysql_q() — выполнение запроса и контроль ошибок

Нередко оказывается, что запрос к MySQL содержит ошибку, из-за чего приложение неправильно работает. Функция mysql_query() при этом ведет себя молчаливо, не выводя никаких сообщений. Поэтому поиск места, где ошибка произошла, часто становится проблемой.

Функция mysql_q() помогает быстро найти проблемное место — в случае ошибки в запросе она выводит на экран сообщение с информацией об ошибке и прекращает работу скрипта².

Рассмотрим пример¹:

<?php

$query = "SELECT NOW"; // забыли поставить скобки после NOW()

mysql_query($query); // ничего не происходит

mysql_q($query); // а вот mysql_q() проинформирует об ошибке

?>

После работы mysql_q() на экране появится сообщение:

MySQL error in file /usr/home/www/наш_сайт/test.php at line 7 (function mysql_q):
Unknown column 'NOW' in 'field list'

SELECT NOW

Функция mysql_q() пригодна для любых запросов (не только SELECT):

mysql_q("INSERT VALUES ('что-то') INTO some_table");

mysql_q("SET @a = 1");

mysql_q("USE some_other_database");

mysql_q("TRUNCATE some_table");

mysql_q("DROP some_table");

mysql_q("START TRANSACTION");

// и др.

Подстановка параметров в запрос

Есть возможность подстановки в запрос элементов ассоциативного массива. Для этого запрос нужно составить с использованием специальных меток и в качестве второго аргумента передать функции mysql_q() массив замены:

$sql = "

    INSERT INTO products

    SET 

      id = :id,

      price = :price,

      name = :name

    ";

$params = array(

        'id' => 12, 

        'price' => 10000,

        'name' => 'Телевизор',

    );

mysql_q($sql, $params);

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

    "

    INSERT INTO products

    SET

      id = 12,

      price = 10000,

      name = 'Холодильник'

    "

Если метке соответствует не скалярная величина, а массив, его элементы будут экранированы и подставлены в запрос через запятую. Код вида

$sql = "

    SELECT * FROM products

    WHERE id IN (:ids)

       OR brand = :brand

    ";

$params = array(

        'brand' => 'Полёт',

        'ids' => array(

                5, 

                115,

                220,

                'сюда случайно попала строка',

                NULL

            ),

    );

mysql_q($sql, $params);

В итоге даст запрос

    "

    SELECT * FROM products

    WHERE id IN (5,115,220,'сюда случайно попала строка',NULL)

       OR brand = 'Полёт'

    "

Подстановка параметров в запрос поддерживается всеми функциями библиотеки (см. ниже).

Получить текст запроса после подстановки можно с помощью функции
mysql_substitute($sql, $params); (обычно это бывает полезно при отладке).

Далее речь пойдет о функциях для обработки запросов, которые возвращают результат — это, прежде всего, запросы SELECT, а также некоторые другие. Для отправки запросов все эти функции используют mysql_q().

В примерах будет использована вот такая таблица:

mysql> SELECT * FROM products;

+----+-----------+-------+---------------------+

| id | name      | price | created             |

+----+-----------+-------+---------------------+

|  1 | Веник     |   100 | 2010-10-09 22:24:14 |

|  2 | Швабра    |   500 | 2010-10-09 22:24:26 |

|  3 | Чайник    |  1500 | 2010-10-09 22:24:37 |

|  4 | Совок     |   150 | 2010-10-09 22:24:51 |

|  5 | Телевизор |  5000 | 2010-10-09 22:24:59 |

|  6 | Ведро     |   150 | 2010-10-09 22:25:18 |

+----+-----------+-------+---------------------+

6 rows in set (0.00 sec)

mysql_getcell() — получение одной ячейки

Функция mysql_getcell() предназначена для получения результатов запросов, которые состоят из одной строки и одного столбца (такие запросы называются скалярными). Результаты mysql_getcell() возвращает в виде скалярной переменной:

echo mysql_getcell("SELECT 1"); // 1

echo mysql_getcell("SELECT NOW()"); // 2010-10-09 22:30:05

echo mysql_getcell("SELECT COUNT(*) FROM products"); // 6

echo mysql_getcell("SELECT name FROM some_table ORDER BY price LIMIT 1"); // Телевизор

// и др.

Если в запросе встретится ошибка — в сообщении будет указано место, откуда вызывается mysql_getcell()³:

<?php

echo mysql_getcell("SELECT NOW"); 

?>

MySQL error in file /usr/home/www/наш_сайт/html/test.php at line 2 (function mysql_getcell):
Unknown column 'NOW' in 'field list'

SELECT NOW

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

mysql_getrow() — получение одной строки

Функция mysql_getrow() служит для получения результатов запросов, которые состоят из одной строки. mysql_getrow() вернёт результат запроса в виде одномерного ассоциативного массива, ключами которого являются имена колонок, а значениями - то, что хранится в соответствующих ячейках:

<?php

$query = "SELECT * FROM products WHERE id = 5";

$data = mysql_getrow($query);

print_r($data);

?>

Array
(
    [id] => 5
    [name] => Телевизор
    [price] => 5000
    [created] => 2010-10-09 22:24:59
)

Параметры для подстановки в запрос передаются вторым аргументом.

mysql_getcolumn() — получение одного столбца

Функция mysql_getcolumn() служит для получения результатов запросов, которые состоят из нескольких строк и одного столбца. mysql_getcolumn() возвращает результат запроса в виде одномерного массива:

<?php

$query = "SELECT name FROM products ORDER BY price DESC";

$data = mysql_getcolumn($query);

print_r($data);

?>

Array
(
    [0] => Телевизор
    [1] => Чайник
    [2] => Швабра
    [3] => Совок
    [4] => Ведро
    [5] => Веник
)

Можно и запросы типа таких:

<?php

print_r(mysql_getcolumn("SHOW TABLES LIKE 'products'"));

?>

Array
(
    [0] => products
)

Иногда бывает нужно иметь такой массив, ключи которого содержат значения какого-нибудь уникального поля (столбца) соответствующих записей. С помощью mysql_getcolumn() сделать это очень легко — нужно добавить это поле в запрос и указать в качестве второго аргумента TRUE. Ключами становятся значения того поля, которое указано в запросе первым:

<?php

$query = "SELECT id, name FROM products ORDER BY price DESC";

$data = mysql_getcolumn($query, TRUE);

print_r($data);

?>

Array
(
    [5] => Телевизор
    [3] => Чайник
    [2] => Швабра
    [4] => Совок
    [6] => Ведро
    [1] => Веник
)

Параметры для подстановки передаются третьим аргументом.

mysql_gettable() — для табличных запросов

Табличным называется запрос, результат которого содержит более одной строки и более одного столбца. Функция mysql_gettable() возвращает его в виде двумерного массива:

<?php

$query = "SELECT * FROM products ORDER BY price DESC LIMIT 3";

$data = mysql_gettable($query);

print_r($data);

?>

Array
(
    [0] => Array
        (
            [id] => 5
            [name] => Телевизор
            [price] => 5000
            [created] => 2010-10-09 22:24:59
        )

    [1] => Array
        (
            [id] => 3
            [name] => Чайник
            [price] => 1500
            [created] => 2010-10-09 22:24:37
        )

    [2] => Array
        (
            [id] => 2
            [name] => Швабра
            [price] => 500
            [created] => 2010-10-09 22:24:26
        )
)

<?php

print_r(mysql_gettable("SHOW VARIABLES LIKE 'query_cache%'"));

?>

Array
(
    [0] => Array
        (
            [Variable_name] => query_cache_limit
            [Value] => 1048576
        )

    [1] => Array
        (
            [Variable_name] => query_cache_min_res_unit
            [Value] => 4096
        )

    [2] => Array
        (
            [Variable_name] => query_cache_size
            [Value] => 134217728
        )

    [3] => Array
        (
            [Variable_name] => query_cache_type
            [Value] => ON
        )

    [4] => Array
        (
            [Variable_name] => query_cache_wlock_invalidate
            [Value] => OFF
        )
)

Есть возможность записать в ключи массива значения уникального поля. Для этого поле нужно добавить в запрос (если оно уже не присутствует в неявном виде как часть *) и указать имя поля вторым аргументом⁴:

<?php

$query = "SELECT * FROM products ORDER BY price DESC LIMIT 3";

// вместо * можно было написать id, name, price, created

$data = mysql_gettable($query, 'id');

print_r($data);

?>

Array
(
    [5] => Array
        (
            [id] => 5
            [name] => Телевизор
            [price] => 5000
            [created] => 2010-10-09 22:24:59
        )

    [3] => Array
        (
            [id] => 3
            [name] => Чайник
            [price] => 1500
            [created] => 2010-10-09 22:24:37
        )

    [2] => Array
        (
            [id] => 2
            [name] => Швабра
            [price] => 500
            [created] => 2010-10-09 22:24:26
        )

)

Параметры для подстановки передаются третьим аргументом.

mysql_write_row()

Одна из самых распространенных задач — это вставка в таблицу новой записи или обновление уже существующей по уникальному ключу с предварительным экранированием передаваемых данных. Эту задачу позволяет решать функция mysql_write_row().

Простой INSERT

Для вставки записи в таблицу качестве аргументов функции требуются имя таблицы и ассоциативный массив данных для вставки, в котором ключи соответствуют именам колонок. Функция вернёт id вставленной записи (см. mysql_insert_id).

<?php

// Допустим, есть пустая таблица вида

// CREATE TABLE goods (

//  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

//    name VARCHAR(255),

//    price DECIMAL(5),

//    description TEXT

// );

// Хотим вставить вот такую строку:

$data = [

    "name" => "Чайник",

    "price" => 3000,

    "description" => "Хороший и дорогой чайник. ' - кавычку экранировать не требуется"

];

mysql_write_row('goods', $data); // вернет 1 - id новой записи

Простой UPDATE

mysql_write_row() можно использовать и для обновления записей таблицы. Для этого ей нужно в качестве третьего аргумента передать значение уникального ключа. Результат работы функции в этом случае — количество изменившихся записей в таблице (см. mysql_affected_rows):

$data = [

    "description" => "Самый лучший чайник",

    "price" => 3500

];

// Следующие две записи идентичны:

mysql_write_row('goods', $data, array('id' => 3) );  // вернет 1, т.к. изменилась одна строка

mysql_write_row('goods', $data, 3); // скалярная величина считается значением столбца с именем 'id'

Обновлять можно также по условию на несколько колонок — в этом случае массив с ключами будет содержать несколько элементов. При этом одна и та же колонка может фигурировать и в части SET, и в части WHERE: код

$set_keys = array(

    'f1'=>11,

    'f2'=>22

);

$where_keys = array(

    'f2'=>2,

    'f3'=>3,

    'f4'=>NULL,

);

mysql_write_row('tablename', $set_keys, $where_keys);

даст запрос

UPDATE tablename SET f1=11, f2=22 WHERE f2=2 AND f3=3 AND f4 IS NULL

INSERT ... ON DUPLICATE KEY UPDATE

При вставке записей можно включать режим ON DUPLICATE KEY UPDATE. Для этого четвертым аргументом нужно передать 'DUPLICATE', а второй и третий должны быть составлены как для случая обычного UPDATE:

$data_columns = [

    "description" => "Самый лучший чайник",

    "price" => 3500,

];

$unique_key_columns = [

    "id" => 3

];

mysql_write_row('goods', $data_columns, $unique_key_columns, 'DUPLICATE');

Такой код приведёт к запросу

INSERT INTO goods SET

    # здесь поля и из $data_columns, и из $unique_key_columns

    id = 3,

    description = 'Самый лучший чайник',

    price = 3500

ON DUPLICATE KEY UPDATE

    # здесь - только поля из $data

    description = 'Самый лучший чайник',

    price = 3500

Бывают ситуации, когда удобнее передать все необходимые данные в $data, а в $unique_keys - указать, какие ключи из них относятся к уникальным полям:

$all_data = [

    "id" => 3,

    "description" => "Самый лучший чайник",

    "price" => 3500

];

$unique_keys = ['id']; // будет исключен из части ON DUPLICATE KEY UPDATE

mysql_write_row('goods', $all_data, $unique_keys, 'DUPLICATE');

// Приведёт к такому же запросу, как в предыдущем примере

INSERT IGNORE и REPLACE

Вставку записи можно проводить также в режимах INSERT IGNORE и REPLACE — их в таком случае нужно указать четвертым аргументом⁵:

mysql_write_row('goods', $data, FALSE, 'IGNORE'); 

mysql_write_row('goods', $data, FALSE, 'REPLACE');

Вставка DateTime

Объекты DateTime автоматически преобразуются в строку в формате ДДДД-ММ-ГГ чч:мм:сс. Следующий код:

mysql_write_row(

    'sometable',

    [

        'when' => new DateTime()

    ]

);

Вызовет запрос

INSERT INTO sometable SET when = '2022-09-02 18:43:00'

Вставка JSON

Если элемент является массивом, mysql_write_row() попытается записать его в качестве JSON. При этом возможны два режима работы: первый - запись всего поля целиком, второй - обновление определенного ключа в нём.

Предположим, имеется вот такая таблица:

CREATE TABLE `t` (

  i int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,

  j json DEFAULT NULL

)

Вставляем в таблицу данные:

$data = [

    'j' => [

        'a' => 10,

        'b' => 'строка'

    ]    

];

mysql_write_row('t', $data);

Результат:

mysql> SELECT * FROM t;

+---+--------------------------------+

| i | j                              |

+---+--------------------------------+

| 1 | {"a": 10, "b": "строка"}       |

+---+--------------------------------+

Преобразование массива в JSON-строку происходит в режиме JSON_UNESCAPED_UNICODE.

Теперь представим, что нам нужно обновить значение элемента a. Если мы передадим функции массив вида [ 'j' => [ 'a' => 11 ] ], поле перепишется полностью:

$id = 1;

$data = [

    'j' => [

        'a' => 11

    ]    

];

mysql_write_row('t', $data, [ 'i' => $id ] );

Результат:

mysql> SELECT * FROM t;

+---+-----------+

| i | j         |

+---+-----------+

| 1 | {"a": 11} |

+---+-----------+

Чтобы затронуть только значение a, нужно применить другую форму записи, переместив название JSON-ключа в имя поля:

$id = 1;

$data = [

    'j.a' => 11

];

mysql_write_row('t', $data, [ 'i' => $id ] );

В этом случае значения соседних элементов сохранятся:

mysql> SELECT * FROM t;

+---+--------------------------------+

| i | j                              |

+---+--------------------------------+

| 1 | {"a": 11, "b": "строка"}       |

+---+--------------------------------+

mysql_escape()

Функция mysql_escape() экранирует переменную, учитывая её тип:

строка будет экранирована⁶ и заключена в одинарные кавычки
число будет оставлено без изменений
вместо NULL в строку запроса будет вставлено выражение NULL
во всех остальных случаях применяется функция intval()

Это позволяет экранировать все переменные единообразно:

$id = 5;

$name = "O'Reilly";

$sql = "

    SELECT ...

    WHERE id = " . mysql_escape($id) . "

      AND name = " . mysql_escape($name) . ";

    ";

// В результате получится запрос

// SELECT ...

// WHERE id = 5

//   AND name = 'O\'Reilly'

Если функции передан массив, то она вернет список его экранированных элементов, перечисленных через запятую. Это весьма удобно при составлении запросов с IN:

$values = array(10, NULL, "строка", "Д'Артаньян");

$sql = "SELECT ... WHERE col IN (" . mysql_escape($values) . ")";

// Получится запрос

// SELECT ... WHERE col IN (10, NULL, 'строка', 'Д\'Артаньян')

Поддержка mysqli

Изначально функции были написаны на основе оригинального API MySQL, но они могут работать и в режиме совместимости с «улучшенным» модулем — mysqli. Добиться этого очень просто: нужно, чтобы к моменту вызова функций в глобальной области видимости существовала переменная $mysqli, содержащая соответствующий объект:

$mysqli = mysqli_connect(...); // можно и объектный синтаксис - не важно

// дальше всё как обычно

mysql_q("SELECT 1");

Никаких других дополнительных действий не требуется.

1. ▲ В примерах предполагается, что уже установлено соединение с сервером MySQL и выбрана база данных для работы.

2. ▲ А также отправляет веб-клиенту HTTP-заголовок с кодом 500, который сигнализирует о том, что на сервере произошел сбой.

3. ▲ Вид сообщений об ошибках таков и для других функций; речь о них пойдет в статье далее.

4. ▲ Столбец, указываемый во втором аргументе, обязательно должен присутствовать в результате запроса. Если для этого столбца используется псевдоним, то вторым аргументом нужно указывать именно псевдоним:

mysql_getcolumn("SELECT id AS alias, name FROM products", "alias")

5. ▲ Следует отметить, что успешный REPLACE по уникальному ключу, отличному от автоинкрементного поля, приведёт к обновлению последнего так же, как если бы была добавлена новая запись.

6. ▲ Экранирование проводится с помощью встроенных функций библиотек MySQL — mysql_real_escape_string или mysqli_real_escape_string() (в зависимости от того, включен ли режим совместимости с модулем mysqli).

© Все права на данную статью принадлежат порталу webew.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в печатных изданиях допускается только с разрешения редакции.

Другие статьи сходной тематики:

Настройка и установка WAMP
Использование INFORMATION_SCHEMA в хранимых процедурах
Отправка e-mail в русской кодировке средствами PHP

Статьи, часто просматриваемые вместе с этой:

Легкий шаблонизатор на PHP
PHP: инструмент для проверки правильности заполнения веб-форм
Организация пространства адресов страниц веб-сайта средствами PHP и MySQL/MariaDB

Добавить комментарий
Отображение комментариев: Древовидное | Плоское

NO USERPIC

rgbeast

Про debug_backtrace() не знал. Интересно.