Для кого это статья? Первоочередной целью написания статьи было именно «разложить все по полочкам» для тех, кто уже работал с mysqli, но не вникал глубоко, а быстренько написал свои обертки и забыл про оригинальный синтаксис. Я постарался разъяснить нюансы, с которым столкнулся сам, при переносе данных из большой и очень старой БД, спроектированной человеком, не знающим про нормализации, в новую, с сильно изменившейся структурой.
Можно ли читать эту статью людям, которые все еще используют старое расширение mysql и только думающие об перехода на PDO или MySqli? Думаю даже нужно.
MySqli или PDO
Последние годы я писал сайты исключительно на фреймворках, что избавляло меня от работы с БД напрямую. Некоторое время назад начал работу над сайтом на чистом php и задался вопросом, что использовать вместо устаревшего и нерекомендованного к использованию старого расширения PHP MySQL.
Выбирать нужно было между MySqli и PDO. После не очень длительного изучения решил остановиться на MySqli, так как, как мне тогда казалось, он полностью идентичен PDO, за исключением того, что нет возможности отказаться от MySQL в пользу чего-то другого. Как я напишу ниже это не совсем так, минимум одно заметное отличие есть.
MySqli рекомендован к использованию самими разработчиками PHP.[1]
ООП и процедурный интерфейс
MySqli позволяет писать код как в ООП стиле так и в процедурном. Мне ближе ООП как и большинству из хабр сообщества, поэтому в этом статье будет использован именно он.
Три основные класса
MySqli имеет 3 основные класса, которые будут подробно рассмотрены в этой статье
- mysqli — необходим для установки соединения с БД и будет полезен, если мы хотим выполнить запросы так, как мы это делали в старом расширении MySQL;
- mysqli_stmt — необходим для использования новой возможности MySqli: выполнять запросы по подготовленным выражениям;
- mysqli_result — объединяет функции для получения результатов запросов, сделанных с помощью mysqli или mysqli_stmt.
Рассмотрим каждый из них подробнее ниже.
Соединение с БД
Есть два способа.
Способ первый. Если вам нужно просто создать соединение.
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
Способ второй. Если вам нужно использовать опции соединения.
$mysqli = mysqli_init();
if (!$mysqli) {
die('mysqli_init failed');
}
if (!$mysqli->options(MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT = 0')) {
die('Setting MYSQLI_INIT_COMMAND failed');
}
if (!$mysqli->real_connect('localhost', 'my_user', 'my_password', 'my_db')) {
die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
С помощью $mysqli->connect_errno и $mysqli->connect_error мы получаем описание и код ошибки, возникших при соединении. И new mysqli() и $mysqli->real_connect() при ошибках соединений вызывают ошибку PHP Warning. Поэтому вывод ошибок с помощью выше упомянутых функций имеет смысл, если у вас отключено отображение ошибок PHP, например, на рабочем сервере, либо если вам нужно как-то обработать эти данные. Я упомнил здесь об этом, потому что не все функции MySQLi вызывают PHP Warning в случае ошибки, и для того что бы узнать, что произошла ошибка необходимо обязательно обращаться к специальным функциям, об этом ниже.
Полученный при соединении объект мы присвоили переменной $mysqli, для того чтобы использовать его в дальнейшем. Это очевидно для ООП стиля, но и для процедурного стиля этот объект также необходим, в этом отличие от устаревшего расширения MySQL, где ссылку на соединение необязательно было передавать при каждом использовании mysql функций.
Буферизированные и не буферизированные результаты
Прежде чем рассказывать дальше, хотелось бы объяснить разницу между этими двумя типами результатов.
Рассмотрим небуферизированный результат. В этом случае вы можете начинать читать результаты, не дожидаясь пока mysql сервер получит результат полностью.
Преимущества:
- Результат можно начинать читать раньше, сокращается время ожидания;
- Результат не занимает место в оперативной памяти.
Недостатки:
- Невозможно узнать, сколько строк получено;
- Невозможно передвигаться к определенному результату, то есть можно читать данные только с начала и по порядку;
- Нельзя выполнять других запросов, пока не закрыт этот результат.
Буферизированный результат лишен этих недостатков и соответственно лишен перечисленных преимуществ.
«Классические» запросы
В MySqli оставили возможность «классических» запросов: когда пользователю предлагается самостоятельно заниматься безопасностью передаваемых запросов так, как это было в устаревшем расширении MySQL. Для этого предлагается использовать функцию $mysqli->real_escape_string(), с помощью которой необходимо обрабатывать все данные перед помещением их в запрос.
Так же как и с соединением есть два способа сделать такой запрос короткий и длинный.
Короткий.
$result = $mysqli->query(‘текст запроса’, MYSQLI_USE_RESULT);
Возможные константы:
MYSQLI_STORE_RESULT – вернет буферизированный результат, значение по умолчанию
MYSQLI_USE_RESULT – небуферизированный
Длинный.
$mysqli->real_query('текст запроса');
echo($mysqli->field_count); // вернет количество столбцов, в результате,
// можно получить до начала получения результата, что дает дополнительную гибкость
// по сравнению c коротким способом, разумеется, вызывать не обязательно
$result = $mysqli->use_result(); // вернет небуферизированный результат
// или
$result = $mysqli->store_result(); // вернет буферизированный результат
Функции $mysqli->use_result() или $mysqli->store_result() так же используются при мульти запросах (запросах состоящих из нескольких запросов). Мульти запросы в этой статье рассмотрены не будут.
И тот и другой синтаксисы вернут результат в виде объекта mysqli_result, который представляет собой удобный интерфейс для работы с результатом, как с буферизированным так и не с небуферизированным.
Как я писал выше, не все функции MySQLi выбрасывают ошибки PHP, описанные выше функции из их числа. В случае если запрос неверный и сервер вернул ошибку, PHP не даст об этом знать. Для проверки используйте функции:
- $mysqli->error – описание ошибки
- $mysqli->errno – код ошибки
$city = $mysqli->real_escape_string($city);
$mysqli->query(«SELECT * FROM `city` WHERE `id` = '$city'»);
if ($mysqli->errno) {
die('Select Error (' . $mysqli->errno . ') ' . $mysqli->error);
}
Преимущества «классического» синтаксиса запросов:
- Он значительно более компактен, чем подготовленные выражения (класс mysqli_stmt);
- Он позволяет получить небуферизированный результат в виде mysqli_result. Подготовленные выражения позволяют получить только буферизированный результат mysqli_result, а с небуферизированным придется работать средствами mysqli_stmt, который значительно менее гибкий, чем mysqli_result.
Практическое применение «классического» синтаксиса запросов я вижу:
- В не буферизированных запросах, если вам хочется использовать преимущества работы с mysqli_result;
- В запросах INSERT, UPDATE, REPLACE или DELETE, если для вас предпочтителен их более короткий синтаксис.
Для таких запросов будут полезны свойства:
- $mysqli->affected_rows – количество затронутых строк предыдущим запросом не на выборку
- $mysqli->insert_id – автоматически сгенерированный ID для последнего запроса вставки.
Подготовленные запросы
Преимущества подготовленных запросов над «классическими»:
- При серии запросов, в которых меняются лишь данные, запрос передается на сервер только один раз, в дальнейшем посылаются только изменяющиеся данные;
- Защита от SQL-инъекций.
За работу с подготовленными запросами в MySQLi отвечает класс mysqli_stmt.
Два способа создания подготовленного запроса.
// первый способ - используя объект mysqli
$mysqli->prepare(«SELECT * FROM `sk2_articles` WHERE `id` = ?»);
if ($mysqli->errno) {
die('Select Error (' . $mysqli->errno . ') ' . $mysqli->error);
}
// второй способ - используя объект mysqli_stmt
$stmt = $mysqli->stmt_init();
$stmt->prepare(«SELECT * FROM `sk2_articles` WHERE `id` = ?»);
if ($stmt->errno) {
die('Select Error (' . $stmt->errno . ') ' . $stmt->error);
}
Различия в том, для какого объекта вызываются функции получения информации об ошибке. Мне второй способ кажется удобнее, потому что проверки на ошибки можно объединить в один блок if c другими функциями mysqli_stmt. Как это сделать будет видно в примерах ниже.
$id_min = 81;
$id_max = 88;
$stmt = $mysqli->stmt_init();
if(
// подготовливаем запрос, там куда будут вствлятся данные отмечаем символом ? (плейсхолдоры)
($stmt->prepare(«SELECT title FROM sk2_articles WHERE id > ? and id < ?») ===FALSE)
// привязываем переменные к плейсхолдорам
or ($stmt->bind_param('ii', $id_min, $id_max) === FALSE)
// отрправляем даные, которые на данный момент находятся в привязанных переменных
or ($stmt->execute() === FALSE)
// привязывем переменую для получения в нее результата
or ($stmt->bind_result($title) === FALSE)
// делаем запрос буферизированным,
// если бы этой строки не было, запрос был бы небуферезированым
or ($stmt->store_result() === FALSE)
// получение результата в привязанную переменную
or ($stmt->fetch() === FALSE)
// закрываем подготовленный запрос
or ($stmt->close() === FALSE)
) {
die('Select Error (' . $stmt->errno . ') ' . $stmt->error);
}
echo $title;
Несколько пояснений к выше написанному коду.
- В $stmt->bind_param() первым параметром указываем тип привязываемых данных (i – integer, d — double, s – строка). Вторым и последующим указываются переменные, которые будет привязаны к соответствующим плейсхолдорам, объявленным в $stmt->prepare() символами "?" в том порядке, в каком они указаны в $stmt->prepare(). Это заметное отличие от PDO, где плесхолдоры могут быть именованными, а значит не зависят от порядка объявления.
- $stmt->bind_result по-моему самое слабое место класса mysqli_stmt, так как оно обязывает знать, какие именно данные будут получены из запроса и каком порядке они объявлены в запросе. Для случая “SELECT * FROM …” он вряд ли применим. Гораздо более гибкий подход в работе с результатом предлагает класс mysqli_result. Чтобы быть окончательно объективным, стоит упомянуть, что можно воспользоваться функцией $stmt->result_metadata() получить mysqli_result содержащий только о метаданные без самих данных, и посмотреть что за столбцы получены, но этот подход мне кажется излишне сложным, и о нем я упомянул только, чтобы избежать обвинений в неточной информации.
- $stmt->store_result(), как я написал в комментарии к строке, необязательный и нужен для того, чтобы можно было использовать такие функции как:
- $mysqli_stmt->num_rows – количество полученных строк к результате,
- $stmt->data_seek – перемещение внутреннего указателя на заданную строку результата,
- $stmt->free_result() – и не забудьте вызвать освобождение памяти если вы использовали $stmt->store_result().
- $stmt->fetch() возвращает TRUE/FALSE если данные были получены удачно/неудачно, и NULL если данных больше нет.
- Не все функции, объединенные в блок if, в случае ошибки помещают ее в $stmt->error, некоторые вызывают PHP Warning, но все из них в случае неудачи возвращают FALSE, поэтому мне кажется эта конструкция удачной. Если вам нужно выполнить несколько раз $stmt->execute() или $stmt->fetch(), то так сделать не получится.
- Не зависимо от того, был ли запрос буферизированный или нет, доступны следующие свойства:
- $stmt->field_count — количество столбцов в результате,
- $stmt->affected_rows – количество затронутых строк предыдущим запросом не на выборку,
- $stmt->insert_id – автоматически сгенерированный id предыдущей вставки.
Изменим код так, чтобы получить результат в виде экземпляра объекта mysqli_result.
$id_min = 81;
$id_max = 88;
$stmt = $mysqli->stmt_init();
if(
($stmt->prepare(«SELECT title FROM sx2_links WHERE id > ? and id < ?») === FALSE)
or ($stmt->bind_param('ii', $id_min, $id_max) === FALSE)
or ($stmt->execute() === FALSE)
// получение буферизированного результата в виде mysqli_result,
// небуферизированный результат получить нельзя, о чем я писал в недостатках
or (($result = $stmt->get_result()) === FALSE)
or ($stmt->close() === FALSE)
) {
die('Select Error (' . $stmt->errno . ') ' . $stmt->error);
}
$row = $result->fetch_row();
echo $row[0];
Класс mysqli_result и работа с результатом с помощью него
Как было показано выше, объект mysqli_result вы могли получить как с помощью «классического» запроса с помощью класса mysqli, тогда он может быть как буферизированный так и небуферизированный, так и с помощью класса mysqli_stmt, тогда он буферизированный. От того какой результат вы получили, зависит работа функций этого класса, поэтому нужно хорошо понимать, что если ваш запрос небуферизированный вы не располагаете всем результатом и соответственно не можете знать сколько строк в результате, и читать его можно только по-порядку строка за строкой.
Набор функций в этом классе покажется знакомым по-старому расширения:
- $result->fetch_row() – получает текущий ряд результата в виде нумерованного массива,
- $result->fetch_assoc() – в виде ассоциативного массива,
- $result->fetch_array() – тип массива задается константой,
- MYSQLI_ASSOC – ассоциативный,
- MYSQLI_NUM – нумерованный,
- MYSQLI_BOTH – оба,
- $result->fetch_object() – строка результата в виде объекта.
Про $result->fetch_object() хотелось бы поговорить отдельно. У этой функции есть два параметра, оба необязательные:
- class_name – имя класса, на основе которого будет создан объект,
- params – массив параметров, которые будут переданы конструктору при создании объекта.
Как видите, передать конструктору класса предположим ассоциативный массив одной строки результата с помощью этой функции не получится. Она сама за вас присвоит свойствам класса, совпадающие с названиями полей результаты. Если свойства класса не будет найдено, оно создается динамически, с областью видимости public.
class Book
{
private $some1;
public $some2;
protected $id;
function __construct($param1, $param2) {
$this->some1 = $param1;
$this->some2 = $param2;
}
}
$book = $result->fetch_object('Book', array(1, 2));
var_dump( $book);
Выведет, если в результате было только одно поле id
object(Book)[4]
private 'some1' => int 1
public 'some2' => int 2
protected 'id' => int 382
Другие полезные функции и свойства рассматриваемого класса:
- $result->fetch_all() — получение всех рядов в виде массива нумерованного / ассоциативного / обоих типов ,
- $result->data_seek() — установка внутреннего указателя на заданный ряд результата,
- $result->num_rows – количество строк в результате.
Ссылки:
Страница MySQLi в официальной документации на php.net
Больше примеров по MySQLi
Список функций MySQLi на русском языке
