Основы работы с PHP MySqli

В связи с прекращением поддержки PHP MySQL в 2011 году для работы с базами данных все более широкое применение находят PDO или MySqli. Они обладают лучшей функциональностью (чем MySQL) и предлагают ООП (объектно-ориентированный интерфейс) API. Какой из них лучше, это тема для другой статьи, в этой статье мы попытаемся разобраться с основами работы с MySqli. Поэтому, без дальнейших предисловий, перейдем к рассмотрению соединения (connect), выбора (select), вставки (insert), обновления (update) и удаления (delete) записей (данных/документов/информации) посредством PHP MySqli. Надеюсь, что данная статья будет полезна при решении проблем, которые могут возникнуть при работе с PHP MySqli.

Установка MySqli

При использовании PHP версии 5.3.0 +, MySqli доступен по умолчанию; для более старых версий, чтобы сделать его доступным, надо включить php_mysqli.dll DLL внутри файла php.ini и отредактировать php.ini, раскоментировав строчку extension=php_mysqli.dll. В линуксе MySQLIi будет установлен автоматически при установке пакета PHP5 mysql. Более подробную информацию об установке в системах windows и linux можно найти здесь.

Соединение с базой данных

MySqli предлагает два способа соединения с базой данных: процедурный и объектно-ориентированный. Рекомендуется использовать объектно-ориентированный. Процедурный похож на (старый) MySql, поэтому для новичков его использование, возможно, будет предпочтительней, стоит помнить, что им пользоваться не рекомендуется.

PHP

//процедурный стиль
$mysqli =  mysqli_connect('host','username','password','database_name');

//объектно-ориентированный стиль (рекомендуется)
$mysqli = new mysqli('host','username','password','database_name');

Ниже показано открытие соединения с базой данных объектно-ориентированным способом. Этот способ будет использоваться и во всех приведенных ниже примерах.

PHP

<?php
// Открываем новое соединение с  MySQL сервером
$mysqli = new mysqli('host','username','password','database_name');

//Выводим любую ошибку соединения
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

?>

Выбор (SELECT) результирующего ряда в виде ассоциативного массива

mysqli_fetch_assoc() : в приведенном ниже коде происходит извлечение результирующего ряда в виде ассоциативного массива. Возвращаемый массив содержит строки, полученные из базы данных, где имена столбцов будут являться ключом, используемым для доступа к внутренним данным. Как показана ниже, данные отображаются в виде HTML таблицы.

PHP

<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');

//Output any connection error
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

//MySqli Select Query
$results = $mysqli->query("SELECT id, product_code, product_desc, price FROM products");

print '<table border="1">';
while($row = $results->fetch_assoc()) {
    print '<tr>';
    print '<td>'.$row["id"].'</td>';
    print '<td>'.$row["product_code"].'</td>';
    print '<td>'.$row["product_name"].'</td>';
    print '<td>'.$row["product_desc"].'</td>';
    print '<td>'.$row["price"].'</td>';
    print '</tr>';
}
print '</table>';

// Frees the memory associated with a result
$results->free();

// close connection
$mysqli->close();
?>

Выбор (SELECT) результирующего ряда в виде массива (ассоциативный, обычный, или в оба)

Фукнция fetch_array() : возвращает массив с объединенным функционалом mysqli_fetch_row и mysqli_fetch assoc. Эта функция является расширенной версией функции mysqli_fetch_row(); для доступа к данным можно использовать как строку, так и числа.

PHP

<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');

//Output any connection error
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

//MySqli Select Query
$results = $mysqli->query("SELECT id, product_code, product_desc, price FROM products");

print '<table border="1"';
while($row = $results->fetch_array()) {
    print '<tr>';
    print '<td>'.$row["id"].'</td>';
    print '<td>'.$row["product_code"].'</td>';
    print '<td>'.$row["product_name"].'</td>';
    print '<td>'.$row["product_desc"].'</td>';
    print '<td>'.$row["price"].'</td>';
    print '</tr>';

}
print '</table>';

// Frees the memory associated with a result
$results->free();
// close connection
$mysqli->close();
?>

Выбор (SELECT) результирующего ряда в виде объекта

fetch_object() : чтобы получить результирующий набор в виде объекта, нужно воспользоваться MySqli fetch_object(). Атрибуты объекта будут отображать имена полей, найденных внутри результирующего набора.

PHP

<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');

//Output any connection error
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

//MySqli Select Query
$results = $mysqli->query("SELECT id, product_code, product_desc, price FROM products");

print '<table border="1">';
while($row = $results->fetch_object()) {
    print '<tr>';
    print '<td>'.$row->id.'</td>';
    print '<td>'.$row->product_code.'</td>';
    print '<td>'.$row->product_name.'</td>';
    print '<td>'.$row->product_desc.'</td>';
    print '<td>'.$row->price.'</td>';
    print '</tr>';
}

print '</table>';

// close connection
$mysqli->close();
?>

Выбор (SELECT) одиночного значение

Одиночное значение получить из базы данных можно посредством fetch_object (метод Cameron Spear).

PHP

<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');

//Output any connection error
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

//chained PHP functions
$product_name = $mysqli->query("SELECT product_name FROM products WHERE id = 1")->fetch_object()->product_name;
print $product_name; //output value

$mysqli->close();
?>

Извлекаем (SELECT COUNT) количество строк в таблице

Иногда нужно узнать количество строк в таблице, особенно при нумерации страниц.

PHP

<?php
//Open a new connection to the MySQL server
$mysqli = new mysqli('host','username','password','database_name');

//Output any connection error
if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

//get total number of records
$results = $mysqli->query("SELECT COUNT(*) FROM users");
$get_total_rows = $results->fetch_row(); //hold total records in variable

$mysqli->close();
?>

Выбор (SELECT) с помощью шаблонов (prepared statements)

prepared statements — специальный инструмент СУБД, позволяющий ускорить последовательное выполнение повторяющихся запросов, построенных по одному и тому же шаблону.

Одной из особенностей MySqli является возможность использования уже написанных шаблонов: то есть запрос достаточно написать один раз, после чего его можно многократно исполнять с различными параметрами. Использование уже написанных шаблонов улучшает производительность для больших таблицах и сложных запросов. Для предотвращения попадания вредоносного кода анализ каждого запроса производится сервером отдельно.

Код ниже использует шаблон (Prepared statement), чтобы получать данные из базы данных. Заполнитель ? в запросе SQL играет роль маркера и будет замещен параметром, который, в свою очередь, может быть строкой, целым числом, double или blob. В нашем случае это строка $search_product.

PHP

$search_product = "PD1001"; //product id

//create a prepared statement
$query = "SELECT id, product_code, product_desc, price FROM products WHERE product_code=?";
$statement = $mysqli->prepare($query);

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$statement->bind_param('s', $search_product);

//execute query
$statement->execute();

//bind result variables
$statement->bind_result($id, $product_code, $product_desc, $price);

print '<table border="1">';

//fetch records
while($statement->fetch()) {
    print '<tr>';
    print '<td>'.$id.'</td>';
    print '<td>'.$product_code.'</td>';
    print '<td>'.$product_desc.'</td>';
    print '<td>'.$price.'</td>';
    print '</tr>';

}
print '</table>';

//close connection
$statement->close();

Тот же запрос с несколькими параметрами:

PHP

$search_ID = 1;
$search_product = "PD1001";

$query = "SELECT id, product_code, product_desc, price FROM products WHERE ID=? AND product_code=?";
$statement = $mysqli->prepare($query);
$statement->bind_param('is', $search_ID, $search_product);
$statement->execute();
$statement->bind_result($id, $product_code, $product_desc, $price);

print '<table border="1">';
while($statement->fetch()) {
    print '<tr>';
    print '<td>'.$id.'</td>';
    print '<td>'.$product_code.'</td>';
    print '<td>'.$product_desc.'</td>';
    print '<td>'.$price.'</td>';
    print '</tr>';

}
print '</table>';

//close connection
$statement->close();

Вставка (INSERT) записи

Запись ниже вставляет в таблицу новый ряд.

PHP

<?php
//values to be inserted in database table
$product_code = '"'.$mysqli->real_escape_string('P1234').'"';
$product_name = '"'.$mysqli->real_escape_string('42 inch TV').'"';
$product_price = '"'.$mysqli->real_escape_string('600').'"';

//MySqli Insert Query
$insert_row = $mysqli->query("INSERT INTO products (product_code, product_name, price) VALUES($product_code, $product_name, $product_price)");

if($insert_row){
    print 'Success! ID of last inserted record is : ' .$mysqli->insert_id .'<br />';
}else{
    die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}

?>

Отрывок ниже вставляет те же значения посредством шаблонов (Prepared Statement). Как мы уже говорили, шаблоны чрезвычайно эффективны против SQL инъекция. Для приведенного примера их использование является оптимальным вариантом.

PHP

//values to be inserted in database table
$product_code = 'P1234';
$product_name = '42 inch TV';
$product_price = '600';

$query = "INSERT INTO products (product_code, product_name, price) VALUES(?, ?, ?)";
$statement = $mysqli->prepare($query);

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$statement->bind_param('sss', $product_code, $product_name, $product_price);

if($statement->execute()){
    print 'Success! ID of last inserted record is : ' .$statement->insert_id .'<br />';
}else{
    die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}
$statement->close();

Вставка (INSERT) нескольких записей

Вставка нескольких рядов одновременно осуществляется путем включения ряда значений столбцов, где каждый ряд значений должен быть обнесен скобками и отделен от других запятой. Иногда нужно узнать, сколько записей было вставлено, обновлено или удалено, для этого можно воспользоваться mysqli_affected_rows.

PHP

//product 1
$product_code1 = '"'.$mysqli->real_escape_string('P1').'"';
$product_name1 = '"'.$mysqli->real_escape_string('Google Nexus').'"';
$product_price1 = '"'.$mysqli->real_escape_string('149').'"';

//product 2
$product_code2 = '"'.$mysqli->real_escape_string('P2').'"';
$product_name2 = '"'.$mysqli->real_escape_string('Apple iPad 2').'"';
$product_price2 = '"'.$mysqli->real_escape_string('217').'"';

//product 3
$product_code3 = '"'.$mysqli->real_escape_string('P3').'"';
$product_name3 = '"'.$mysqli->real_escape_string('Samsung Galaxy Note').'"';
$product_price3 = '"'.$mysqli->real_escape_string('259').'"';

//Insert multiple rows
$insert = $mysqli->query("INSERT INTO products(product_code, product_name, price) VALUES
($product_code1, $product_name1, $product_price1),
($product_code2, $product_name2, $product_price2),
($product_code3, $product_name3, $product_price3)");

if($insert){
    //return total inserted records using mysqli_affected_rows
    print 'Success! Total ' .$mysqli->affected_rows .' rows added.<br />';
}else{
    die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}

Обновление (Update)/удаление (Delete) записей

Принцип обновление и удаление записей тот же. Достаточно заменить строку запроса на MySql update или delete (не понял, сам смотри).

PHP

//MySqli Update Query
$results = $mysqli->query("UPDATE products SET product_name='52 inch TV', product_code='323343' WHERE ID=24");

//MySqli Delete Query
//$results = $mysqli->query("DELETE FROM products WHERE ID=24");

if($results){
    print 'Success! record updated / deleted';
}else{
    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
}

Обновление с помощью шаблонов (prepared statements)

Пример обновления записи с помощью шаблонов (prepared statements) приведен ниже.

PHP

$product_name = '52 inch TV';
$product_code = '9879798';
$find_id = 24;

$query = "UPDATE products SET product_name=?, product_code=? WHERE ID=?";
$statement = $mysqli->prepare($query);

//bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
$results =  $statement->bind_param('ssi', $product_name, $product_code, $find_id);

if($results){
    print 'Success! record updated';
}else{
    print 'Error : ('. $mysqli->errno .') '. $mysqli->error;
}

Удаление старых записей

Удалению подвергаются все записи, находящиеся на сервере больше 1 дня; количество дней можно задать самому.

PHP

//MySqli Delete Query
$results = $mysqli-<query("DELETE FROM products WHERE added_timestamp > (NOW() - INTERVAL 1 DAY)");

if($results){
    print 'Success! deleted one day old records';
}else{
    print 'Error : ('. $mysqli-<errno .') '. $mysqli-<error;
}

Заключение

Вне сомнения, MySqli существенно лучше стандартного MySql расширения PHP, хотя принципы их работы довольно схожи. Надеюсь, приведенная выше информация окажется полезной при создании и переносе проектов в будущем. Для удобства ниже была реализована возможность скачать файлы примеров. Это можно сделать, нажав на кнопку download.

источник