MySQLi раскладываем все по полочкам

Для кого это статья? Первоочередной целью написания статьи было именно «разложить все по полочкам» для тех, кто уже работал с mysqli, но не вникал глубоко, а быстренько написал свои обертки и забыл про оригинальный синтаксис. Я постарался разъяснить нюансы, с которым столкнулся сам, при переносе данных из большой и очень старой БД, спроектированной человеком, не знающим про нормализации, в новую, с сильно изменившейся структурой.

Можно ли читать эту статью людям, которые все еще используют старое расширение mysql и только думающие об перехода на PDO или MySqli? Думаю даже нужно.

MySqli или PDO

Последние годы я писал сайты исключительно на фреймворках, что избавляло меня от работы с БД напрямую. Некоторое время назад начал работу над сайтом на чистом php и задался вопросом, что использовать вместо устаревшего и нерекомендованного к использованию старого расширения PHP MySQL.

Выбирать нужно было между MySqli и PDO. После не очень длительного изучения решил остановиться на MySqli, так как, как мне тогда казалось, он полностью идентичен PDO, за исключением того, что нет возможности отказаться от MySQL в пользу чего-то другого. Как я напишу ниже это не совсем так, минимум одно заметное отличие есть.

MySqli рекомендован к использованию самими разработчиками PHP.[1 ^[1]]

ООП и процедурный интерфейс

MySqli позволяет писать код как в ООП стиле так и в процедурном. Мне ближе ООП как и большинству из хабр сообщества, поэтому в этом статье будет использован именно он.

Три основные класса

MySqli имеет 3 основные класса, которые будут подробно рассмотрены в этой статье

1. mysqli ^[2] — необходим для установки соединения с БД и будет полезен, если мы хотим выполнить запросы так, как мы это делали в старом расширении MySQL;
2. mysqli_stmt ^[3] — необходим для использования новой возможности MySqli: выполнять запросы по подготовленным выражениям;
3. mysqli_result ^[4] — объединяет функции для получения результатов запросов, сделанных с помощью 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);
}

Преимущества «классического» синтаксиса запросов:

1. Он значительно более компактен, чем подготовленные выражения (класс mysqli_stmt);
2. Он позволяет получить небуферизированный результат в виде mysqli_result. Подготовленные выражения позволяют получить только буферизированный результат mysqli_result, а с небуферизированным придется работать средствами mysqli_stmt, который значительно менее гибкий, чем mysqli_result.

Практическое применение «классического» синтаксиса запросов я вижу:

1. В не буферизированных запросах, если вам хочется использовать преимущества работы с mysqli_result;
2. В запросах 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;

Несколько пояснений к выше написанному коду.

1. В $stmt->bind_param() первым параметром указываем тип привязываемых данных (i – integer, d — double, s – строка). Вторым и последующим указываются переменные, которые будет привязаны к соответствующим плейсхолдорам, объявленным в $stmt->prepare() символами "?" в том порядке, в каком они указаны в $stmt->prepare(). Это заметное отличие от PDO, где плесхолдоры могут быть именованными, а значит не зависят от порядка объявления.
2. $stmt->bind_result по-моему самое слабое место класса mysqli_stmt, так как оно обязывает знать, какие именно данные будут получены из запроса и каком порядке они объявлены в запросе. Для случая “SELECT * FROM …” он вряд ли применим. Гораздо более гибкий подход в работе с результатом предлагает класс mysqli_result. Чтобы быть окончательно объективным, стоит упомянуть, что можно воспользоваться функцией $stmt->result_metadata() получить mysqli_result содержащий только о метаданные без самих данных, и посмотреть что за столбцы получены, но этот подход мне кажется излишне сложным, и о нем я упомянул только, чтобы избежать обвинений в неточной информации.
3. $stmt->store_result(), как я написал в комментарии к строке, необязательный и нужен для того, чтобы можно было использовать такие функции как:
   • $mysqli_stmt->num_rows – количество полученных строк к результате,
   • $stmt->data_seek – перемещение внутреннего указателя на заданную строку результата,
   • $stmt->free_result() – и не забудьте вызвать освобождение памяти если вы использовали $stmt->store_result().

4. $stmt->fetch() возвращает TRUE/FALSE если данные были получены удачно/неудачно, и NULL если данных больше нет.
5. Не все функции, объединенные в блок if, в случае ошибки помещают ее в $stmt->error, некоторые вызывают PHP Warning, но все из них в случае неудачи возвращают FALSE, поэтому мне кажется эта конструкция удачной. Если вам нужно выполнить несколько раз $stmt->execute() или $stmt->fetch(), то так сделать не получится.
6. Не зависимо от того, был ли запрос буферизированный или нет, доступны следующие свойства:
   • $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() – тип массива задается константой,
  1. MYSQLI_ASSOC – ассоциативный,
  2. MYSQLI_NUM – нумерованный,
  3. 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 ^[5]
Больше примеров по MySQLi ^[6]
Список функций MySQLi на русском языке ^[7]

Автор: ChAk