Pull to refresh

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

Reading time 11 min
Views 211K

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

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


MySqli или PDO


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

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

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

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


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

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


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


Преимущества «классического» синтаксиса запросов:
  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
Больше примеров по MySQLi
Список функций MySQLi на русском языке
Tags:
Hubs:
+26
Comments 63
Comments Comments 63

Articles